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.