Roll Your own Analysis Office Features (Part II) – Using the AutoRefresh toggle to improve performance
In this installment of “Roll Your own Analysis Office Features”, we’ll look at a new trick that Analysis Office users can user to improve their performance. Analysis Office 1.4, SP2 added a new VBA command for toggling AutoRefresh of data sources. You can use this to control which data sources auto-refresh, instead of simply toggling the whole document on or off. For example, if you have a large workbook with many data sources, you could use this command to enable auto refresh on the active page and disable it for all other pages.
In the code below, we’ll define a simple (31 lines, including comments and pretty print whitespace) macro script to toggle off auto refresh for all data sources, except for the currently selected data source. It is quite simple. It loops over all data sources in the document (presuming that the data sources follow the standard naming convention) and toggles off their auto-refresh. Then, it uses SAPGetCellInfo to find the currently selected data source and turns it back on.
Sub FocusAutoRefresh()
Dim ds As Integer
Dim lResult As Long
Dim currentDataSource As String
Dim dataSourceAlias As String
Dim foundAllDataSources As Boolean
ds = 1
dataSources = ""
foundAllDataSources = False
'Determine all data sources in the document, presuming that they follow the default naming convention
Do While foundAllDataSources = False
currentDataSource = "DS_" & CStr(ds)
'The next line of code does two things simultaniously
' 1-Assert that DS_X exists. If it does not exist, we'll get an exception here
' 2-If DS_X exists, toggle its auto refresh off
lResult = Application.Run("SAPExecuteCommand", "AutoRefresh", "Off", currentDataSource)
If lResult < 1 Then
foundAllDataSources = True
End If
ds = ds + 1 'increment ds
Loop
dataSourceAlias = Application.Run("SAPGetCellInfo", Selection, "DATASOURCE")
lResult = Application.Run("SAPExecuteCommand", "AutoRefresh", "On", dataSourceAlias)
End Sub
Now, lets take it for a spin. In the video below, I’ve created a workbook to demonstrate FocusAutoRefresh(). It includes two data sources, DS_1 and DS_2. First, we’ll select one of them and execute FocusAutoRefresh(). Then we’ll change the navigation state of both to show that only one of them now auto-refreshes. Afterwards, we’ll switch the “auto-refresh focus” to the other data source and change the navigation state of both again to demonstrate the new regime.