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 :
/wp-content/uploads/2014/06/step2_472891.jpg

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:

/wp-content/uploads/2014/06/step3_472898.jpg

Step 4:

In Design Mode simply click on inserted button:

/wp-content/uploads/2014/06/step4_1_472899.jpg

Select command type “Workbook-Specific Command”:
/wp-content/uploads/2014/06/step4_2_472900.jpg

Leave as it is and press “Finish”
/wp-content/uploads/2014/06/step4_3_472901.png

and set static parameters and click OK:

/wp-content/uploads/2014/06/step4_4_472903.jpg

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Rasmus Josefsen

    This is exactly what I have been looking for but when I create the button and set the static parameters, and then click the button, the variable screen pops up, without my values.

    When I then go into the static parameters again a new cmd with index 3 and SHOW_VARIABLE SCREEN value has been added by itself. The index for DATA_PROVIDER is also changed.

    Can you advise me on how to get past this issue?

     

    (0) 

Leave a Reply