vba.

Analysis Office Get Calendar Week for SAPSetFilter

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.

Read More 0 Comments

Analysis Office Insert Product Image

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.

Read More 0 Comments

Analysis Office SAPInsertLine

The API command SAPInsertLine is available since version 2.2 SP3 of Analysis 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
Read More 0 Comments

Using SAPGetVariable with VBA

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:

Read More 0 Comments

Analysis Office Table Design, API and Design Rules

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

Read More 0 Comments

Analysis Office SAPOpenWorkbook

A new feature of Analysis 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:

Read More 0 Comments

Analysis Office Logoff

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

Read More 0 Comments

Analysis Office: Best Practice Connection

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 Office.

 

The most important thing when you are working with VBA in Analysis 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:

 

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

 

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

 

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

 

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

 

Dim lResult As Long

lResult= Application.Run("SAPExecuteCommand", "Refresh")

 

or

 

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:

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

 

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

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.

Read More 0 Comments

Analysis Office 2.1 Logoff via VBA

Since version 2.1 Analysis 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 Office 2.1".

 

The command is:

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
Read More 0 Comments

Enable Analysis Office Addin

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

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 Office 2.0, SAP has changed the name of the addin. The command is now:

 

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.

Read More 0 Comments

How to convert a text field into a date field with VBA

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.

 

Read More 0 Comments

Create own Excel formula

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.

 

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.

Read More 0 Comments

Delete VBA array content

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

 

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

Read More 0 Comments

Connecting error in Analysis Office

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 Office 2.3 you have now the option to open a workbook with the function SAPOpenWorkbook.

Read More 0 Comments

Open a BEx workbook via VBA

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:

Read More 0 Comments

Load VBA code from a text file

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

Read More 0 Comments

Start a Process Chain by VBA

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:

Read More 0 Comments

How to use a remote enabled function module in SAP BW with VBA

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

Read More 0 Comments

Connecting via VBA to a SAP system

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.

Read More 0 Comments