vba.

It has been a while since I wrote the last blog post. But there happened a lot in the last two months. We had our Deep Dives about Self Service with SAP Data Analytics Cloud Architecture and I had also some weeks of vacation. Now I am back from my vacation, and now I want to share some ideas I had in the last months.

This post is about Microsoft PowerPoint and how I use it to create a master PowerPoint file for different purposes. The idea was to have one place for all my SAP Data Warehouse Cloud slides and use them in different customer scenarios.

Therefore, I search a little what I can do. If you have Microsoft Office 365, PowerPoint has the option of Custom Slide Shows under the tab Slide Show.

A few weeks ago, a colleague of mine, ask me how he can get the calendar week to use it in the Analysis Office API SAPSetFilter. I developed a short macro which defines the calendar week to use it in 0CALWEEK. Maybe someone has another solution for this.

In the comments of this blogpost on blogs.sap.com, Stephen Hobbs showed his idea of insert a product Image besides the crosstab in combination with the new Customize User Interface function. So I make my own thoughts how I can realize a thing like this.

The API command SAPInsertLine is available since version 2.2 SP3 of Analysis for Office. With this function you can insert a new line into a crosstab. There are five input-parameters available:

• RuleID
• Data Source Alias
• Position
• PositionBy
• PositionBy parameters

You can define the RuleID, if you do not define an ID, the system generates one automatically. As Data Source Alias, you have to enter the data source alias, e.g. DS_1. As Position you can choose between Before, After, BelowHeader or BesideHeader. For PositionBy you can enter one of the following elements:

• Dimension
• DimensionResult
• DimensionGroup
• DimensionMember
• HierarchyNode
• Tuple

At the moment I am working very hard to get my book done, so this is only a short blog post how to use SAPGetVariable with VBA. The Analysis Office help provides no example so here it is:

Since Analysis for Office 2.0 you have the option Table Design. You can now create new empty rows and columns.

A new feature of Analysis for Office 2.3 is a macro called SAPOpenWorkbook. Some of you maybe know the old sapbex.xla!SAPBExReadWorkbook or the BExAnalyzer.xla!runWorkbook command.

The user guide explanation for this macro is:

You can use this API method to open an Analysis workbook. The workbook that should be opened can be stored on a SAP NetWeaver server or on a BI platform.

The workbook is opened in the same Microsoft Excel instance. After the command execution, the opened workbook is active.

As you know the sapbex.xla!SAPBExReadWorkbook respectively BExAnalyzer.xla!runWorkbook command needed either an established connection from the addin or you created your own connection with the following code:

After SAP still doesn't provide a native button in Analysis for Office 2.2 SP2 to log off from a system, I decided to make a short instruction from my article "Analysis for Office 2.1 Logoff via VBA". Have fun.

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

Before you can use Analysis for Office with Visual Basic for Application, you have to load the Analysis Addin. The code for this is in Analysis for Office 1.4:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Private Sub EnableAnalysisOffice()
Dim addin As COMAddIn

For Each addin In Application.COMAddIns
 If addin.progID= "SBOP.AdvancedAnalysis.Addin.1" Then
  If addin.Connect= False Then addin.Connect= True
 End If
Next

End Sub

In Analysis for Office 2.0, SAP has changed the name of the addin. The command is now:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Private Sub EnableAnalysisOffice()
Dim addin As COMAddIn

For Each addin In Application.COMAddIns
 If addin.progID= "SapExcelAddIn" Then
  If addin.Connect= False Then addin.Connect= True
 End If
Next

End Sub

Now you can continue working with the SAP Analysis commands.

When you get a date from BEx Analyzer or Analysis Office the Excel internal format is Text and not Date. This means that the sorting doesn't work correctly. Use the following VBA source code to convert easily text to date.

In Excel you can quickly create very complex formulas. If you want to simplify your formula, you can also write your own function in VBA. Here is an example for calculating the percentage variance from the previous year.

1
2
3
4
5

Option Explicit

Public Function prozent(source As Double, target As Double)
  prozent = Application.WorksheetFunction.IfError(IIf(target < 0,(source - target) / -target, (source - target) / target), 0)
End Function

As you can see, a very simple formula. For this purpose the sample as an Excel formula.

=IFERROR(IF(targetCell < 0,(sourceCell - targetCell) / - targetCell,(sourceCell - targetCell / targetCell), 0)

= (ACT) 215 - (PY) 204 / (PY) 204 = 0,055 = 5,5 %

As you can see the own formula is more readable and can be easily combined with another formula.

There is an easy way to delete the contents of an array. Is possible by the command Erase

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Option Explicit

Sub FeldBeispiel()
Dim fFeld(5, 5) As Integer

fFeld(3, 3) = 3
MsgBox fFeld(3, 3)
Erase fFeld
MsgBox fFeld(3, 3)

End Sub

The following source code allows you to connect your DataSource to a BW system. The workbook must contain a DataSource (DS_1). At this moment you can't open a workbook like BEx 3.5 or 7.0. In Analysis for Office 2.3 you have now the option to open a workbook with the function SAPOpenWorkbook.

In 7.0 and 3.5 you can open workbooks via VBA. This is useful if you want to automate reporting. The command for BEx Analyzer is:

If you want to import source code from a text file, you can use the following code.

If you want to start a Process Chain and you maybe haven't got the rights in the BW, you can start a Process Chain by VBA. Use the following source code:

If you have created your own SAP function module, you can use this with the following VBA code.

Sub FunctionModule()

'Variables Definition

Dim MyFunc As Object

Dim E_INSERTED As Object

Dim E_MODIFIED As Object

Dim DATA As Object

Set MyFunc = R3.Add("Z_RSDRI_UPDATE_LCP") 'FunctionModule Name in SAP BW

Set E_INSERTED = MyFunc.imports("E_INSERTED") 'InsertFunction in SAP BW

Set E_MODIFIED = MyFunc.imports("E_MODIFIED") 'ModifyFunction in SAP BW

Set DATA = MyFunc.tables("I_T_DATA") 'Table to store data and write to BW.

'Add data

DATA.Rows.Add 'add new data

rowDATA.Value(1, 1) = Sheet1.Cells(1, 10).Value 'First Cell of the data table is filled with the value from Sheet1.Cells(1,10)

'Call Insert or Modify

Result = MyFunc.CALL

'Message to the User

If Result = True Then

MsgBox "Insert Rows: " & E_INSERTED.Value & " Modify Rows: " & E_MODIFIED.Value, vbInformation

Else

MsgBox MyFunc.EXCEPTION 'Exception

End If

End Sub

If you want to create automated reports with data from an SAP ERP or Business Warehouse system, you first need to connect to a SAP system. The connection can be used later to access system tables in the ERP or Business Warehouse or to trigger various other action.