Roll Your own Analysis Office Features (Part II) - Using the AutoRefresh toggle to improve performance

[david_stocker]

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.