Step by step guide: How to Set BEx workbook variables using VBA

[Former Member]

      Before starting keep in mind that if you write some incorrect VBA code in BEx workbook and save it in the server, there is a chance that FATAL error will occur and excel will be closed after you click OK or CANCEL in error message window. This error can only be fixed by deleting the workbook and recreating it with new one. So I suggest trying this solution on locally saved workbook first. 

Step 1: 

Open workbook with SAP BEx Analyzer and save it locally.

Step 2:

Create variable list like this in different sheet :

In this example we use only 2 variables but you can define more simply by creating

VAR_NAME_2                        0              Name of variable
VAR_VALUE_EXT_2             0              Value of variable
VAR_NAME_3                        0              Name of variable
VAR_VALUE_EXT_3             0              Value of variable
…..

and so on.

Step 3:

Insert button using BEx Analyzer Add-in:

Step 4:

In Design Mode simply click on inserted button:

Select command type “Workbook-Specific Command”:

Leave as it is and press “Finish”

and set static parameters and click OK:

DATA_PROVIDER – value will show which data provider should be used than variables is processed.
CMD – processed values from cells before submitting them.
SUBCMD – submits variables for data provider.
Command Range – into this text field insert range with your variable list.

Tip: Setting static parameters can get tricky sometimes so double check it.

Step 5: Insert variables and click your created button. Query should now run with your inserted values and Change Variable screen will not appear.

Step 6: To change values you can write macro like this:

When using external source (like C# project):

Sub changeVariables( compCode As String, period As String)
      Sheets("Sheet1").Visible = True 
      Sheets("Sheet1").Select  
      Range("C3").Value = compCode
      Range("C5").Value = period
       Sheet5.BUTTON_39_Click

      Sheets("Sheet1").Visible = False
      Sheets("Table").Select
End Sub

When using in workbook coded values:

Sub changeVariables()
      Sheets("Sheet1").Visible = True 
      Sheets("Sheet1").Select  
      Range("C3").Value = “9999”
      Range("C5").Value = “2013”
       Sheet5.BUTTON_39_Click

      Sheets("Sheet1").Visible = False
      Sheets("Table").Select
End Sub

Button_click_39 sub will look like this (this Sub will be generated automatically):

Public Sub BUTTON_39_Click()
      Dim BEx1 As Object
      Set BEx1 = Application.Run("BExAnalyzer.xla!GetBEx")
      Call BEx1.RaiseButtonClick(Parent.Name, "BUTTON_39")
End Sub

Conclusions:

This method is not very elegant but does the job. Locally saved workbooks can get variable values from external sources or use coded ones. This solution proves to be stable when running it periodically. This can be used when writing simple report generator which generates physical reports with several languages, currencies and etc.

Hope this helps.

Additional information:
SAP note 1085218