november 2015.

After the article "Connecting error in Analysis for Office via VBA" is often read, I would like to publish a few tips and tricks about working with Analysis for Office.

The most important thing when you are working with VBA in Analysis for Office, is the refresh of the DataSource(s). Otherwise nothing works. Once a connection to the Business Warehouse is established, each DataSource needs to be refreshed.

If you have only one DataSource, that is the source code:

1

Call Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

If you have more than one DataSource, this is the source code:

1

Call Application.Run("SAPExecuteCommand", "Refresh")

So that you know if the refresh was successful, you should use the following source code:

1
2

Dim lResult As Long
lResult= Application.Run("SAPExecuteCommand", "Refresh")

or

1
2

Dim lResult As Long
lResult= Application.Run("SAPExecuteCommand", "Refresh", "DS_1") 

Therefore you can check lResult to 1 or 0. If you refresh the DataSource(s) each time, a lot of time is wasted. To counteract this, there is the following source code:

1

Call Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")

This command checks if the DataSource is already active. Here an example source code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

Sub AnalysisOfficeStart()
  Dim lResult As Long
  'Connection is running?
  If Application.Run("SAPGetProperty", "IsConnected", "DS_1") Then
    'Active DataSource?
    If Not Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1") Then
      'Refresh DataSource
      lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
    Else
      'Show Prompts
      lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
    End If
  Else
    lResult = Application.Run("SAPLogon", "DS_1", "Client", "User", "Password")
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
  End If
End Sub

After this you can excute your own code.

Since version 2.1 Analysis for Office is now finally a VBA command to log out of the current system.

So far is nothing in the help file, but it is called in the SCN Forum under "Whats new in Analysis for Office 2.1".

The command is:

1
2
3

Public Sub Logoff()
  Call Application.Run("SAPLogoff",Parameter)
End Sub

Parameter:

• True: With this value, the connection is restarted the system
• False: With this value the connection will not restart the system

Result:

• 0: faulty execution
• 1: Successful execution