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.