SAPBEXsetVariables range

[Former Member]

Hello BExperts,

What format/structure does the SAPBEXsetVariables API function expect for its input range? I have been struggling for several days now to figure this out. The description of the API function is below.

Function SAPBEXsetVariables(varValues As Range) As Integer

Set variable Values for User to provide Pop-Up Filter selections (Filter dialogs will be disabled if properly filled)

The code I have written attempts to open a workbook, set the values of two variables that it normally would prompt the user for, and refreshes the workbook.

Sub TestMacro()
    Dim varValues As Range
    Set varValues = Worksheets("Test").Range("A6:F7")
    Run "SAPBEXreadWorkbook", "W4FSAZJXZ341W13I569KJ28RX"
    Run "SAPBEXsetVariables", varValues
    Run "SAPBEXrefresh", True
End Sub 

The workbook opens and refreshes just fine, but always prompts me for the variable values. The workbook only contains one query. Searching the Internet reveals that several others have had this problem but I cannot find a solution.

It appears that with BI 3.x one could simply overwrite variables values in the hidden "SABBEXqueries" sheet. But it seems that sheet no longer exists for BI 7. Function SAPBEXdebugPrint does not seem to be available for BI 7 either so I cannot see the C_T_VARIABLES structure that some recommend looking at.

The last post in the following thread appears to reveal the structure of the range that SAPBEXsetVariables expects, but did not work for me. BEx still asks me for the values of the two variables.

http://forums.sdn.sap.com/thread.jspa?threadID=276850&tstart=0

Any help would be appreciated!

Edited by: alfredough on Dec 29, 2011 5:48 AM

[Former Member · Accepted solution]

Hi Alfredough, I'm afraid I'm in pretty much the same situation as yourself (although possibly slightly further down the line). The reason I add that possibility is because I'm pretty certain that SAPBEXsetVariables has been removed in 7.x.

It looks like there are some cludges which can be done to work through this (possibly), but the entire comparison of bexanalyzer.xla to sapbex.xla seems to involve features being removed, rather than added.

Here's the thread which seems to give a cludge, which I'm having trouble getting working: http://forums.sdn.sap.com/thread.jspa?threadID=1306490

There's also some work which someone's done here around script objects: http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/25185

[Former Member · Accepted solution]

Thanks for your response. I ended up figuring out a work-around, just been meaning to come back here to post it.

I created a new sheet in my workbook named Button. In this sheet I created a range that was four rows by three columns in size. An example is below. I only needed to set two variables.

VAR_NAME 0 TECHNICAL_NAME

VAR_VALUE_EXT 0 CHARACTERISTIC_VARIABLE_VALUE

VAR_NAME_1 0 TECHNICAL_NAME2

VAR_VALUE_EXT_1 0 CHARACTERISTIC_VARIABLE_VALUE2

The text in the first column is used by BEx, so leave that as is. I'm assuming for additional variables and values you continue to increment the extensions, for example _2, _3, etc. I'm not sure what the second column is used by BEx for, but 0 worked for me so I stuck with it. The third column contains the variables' technical names and the characteristic values I want to set them to.

Next I created a button. For its command type I selected: workbook-specific command and selected the data provider the variables pertained to. For workbook-specific command I selected Process Variables.

I set the button's command range to my Excel range above. In the static parameters for the button:

SUBCMD 0 VAR_SUBMIT

CMD 0 PROCESS_VARIABLES

DATA_PROVIDER 0 DP_#

Where DP_# is the data provider I selected when specifying the button's command type. For some reason it is incredibly difficult to set these static parameters. They will keep getting overwritten. You have to keep going back and setting them again. Stick with it and eventually it'll let you get all of this in there.

I then wrote a macro in the workbook.

Sub ClickMacro()
   Sheets("Button").BUTTON_#_Click
End Sub

BUTTON_# is the name of the button. You can view the button's name by clicking on it in design mode.

I then wrote VBA code in another workbook, which copies values into the range I gave an example for above.

Dim workbookName As String
Run "SAPBEXreadWorkbook", "WORKBOOKID"
workbookName = ActiveWorkbook.Name
Run "'" & workbookName & "'!ClickMacro"
Run "SAPBEXrefresh", True
Workbooks(workbookName).SaveCopyAs fileName:=(fileName)

WORKBOOKID is the workbook ID of the workbook that contains the sheet and ClickMacro that I defined above.

fileName is the path and file where I save the workbook

Hope this helps someone. It took me a long time to figure out how to do this.

[Former Member · Accepted solution]

Why not just use this simple code:

    Dim sSheet As Object

    Set sSheet = ThisWorkbook.Worksheets("SAPDATA")

    sSheet.BUTTON_2_Click

instead of

Run "SAPBEXreadWorkbook", "WORKBOOKID"

workbookName = ActiveWorkbook.Name

Run "'" & workbookName & "'!ClickMacro"

Run "SAPBEXrefresh", True

[former_member215195 · Accepted solution]

Hi Alfred,

Thank you for providing the details.

I could create a button following the below procedure.

1. Open Bex Analyzer

2. Navigate to Bex Analyzer (menu) -> Design Toolbar -> Insert Button. This will switch on the Design Mode.

3. Click on the button, select "Workbook-Specific Command" and click on next (Data provider need not be worried about at this point as it can be changed later also).

4. Select "Process Variables" and click on Finish.

5. One more window with an heading "Properties of Button" will be shown with fields like "Name of Button", "Range", "Button Text", "Command Range" and few other.

6. On the right side of this window, list of Static Parameters can be created.

7. As per your example, i created below parameters

CMD                         0          PROCESS_VARIABLES

DATA_PROVIDER      0          DP_1

and created a range having below details and provided this range in "Command Range" field (in button properties)

VAR_NAME                     0         #NAME1#             

VAR_VALUE_EXT            0          #VALUE#

VAR_NAME_1                 0          #NAME2#

VAR_VALUE_EXT_1         0          #VALUE2#

8. This DID NOT WORK for me. Actually nothing was happening. After some investigation, i figured out that i need to have one command like below

CMD

1

SHOW_VARIABLE_SCREEN

Without this neither the variable screen was shown nor it read the parameter values from the excel range provided. Once this was done, the input screen was shown and with the values that i provided in my excel range.

Here are my questions. Pls provide your valuable inputs.

1. Why am i forced to have a command for "SHOW_VARIABLE_SCREEN"? Is this mandatory? problem with this is that i will always be shown a input screen which i don't want (in fact the very reason for this exercise is not to show the parameter screen but to supply values to it from the back end). Is there some setting that i need to do to get rid of this?

2. The setup can read values for only  2 parameters. i mean when i have below entries, the program was not reading the 3rd parameter values (of course i updated the "command range").

VAR_NAME_2                 0          #NAME3#

VAR_VALUE_EXT_2         0          #VALUE3#

Thank you. Looking for a sooner response.

Lohith

[former_member215195 · Accepted solution]

Hi Alfred,

I figured out the mistake that i have done. Looks like the order of the commands is very important.

In my case, i had something like below

CMD                         0          PROCESS_VARIABLES

DATA_PROVIDER      0          DP_1

SUBCMD                   0          VAR_SUBMIT

I should not have the PROCESS_VARIABLES as my first command. Instead should have DP_1 or the SUBCMD as first argument. So below order worked for me.

DATA_PROVIDER      0          DP_1

SUBCMD                   0          VAR_SUBMIT

CMD

0

PROCESS_VARIABLES

But my second question is still unanswered. Can you through some light on it?

Lohith

[former_member215195 · Accepted solution]

Hi All,

The values for 3rd variable is read after using the below command along with the other 2.

VAR_OPERATOR_3

0

EQ

Hope this helps.

Lohith

[Former Member · Accepted solution]

Hi Lohith and all interested in,

I definitely resolved the problem with preparing the range for SAPBEXSetVariables function.

My BW spreadsheeds are now perfectly automated. If someone wants to know the know-how, just pls e-mail me on tomasz.szymacha.66@gmail.com

Cheers

Tomek

[former_member812821 · Accepted solution]

Hi, i have 7 sheets with 7 queries and 7 dp (data provider)..

Id like to refresh all queries with the new variables values setted calling the "process variables" button only one time, ¿it is posible?.

Actually i have to set the dp variable (example dp_1) and when i click the button it refresh all workbook but only uses the new values for the first sheet that contains dp_1.

The rest of sheets are refreshed with values of initial window prompt variables.

If i call 7 times the button for every dp it goes correct, but the time of execution is very long.

Id like simulate the execution when i fill the window prompt variables.

Could you help me please?.