Skip to Content
avatar image
Former Member

Usage of SAPBEX setVariables method?

Dear all,

I have a question regarding the usage of the SAPBEXsetVariables method which is part of the SAPBEX VBA-API.

I want to invoke a BEx Workbook from a VBA-Macro. The query, embedded in this workbook, is

using variables. (Some of the variables are further being processed by user exit, once a value for these variables

has been set.)

When opening the Workbook from my VBA-Macro, I don't want the variable screen to pop up. The variables should be set automatically during runtime, e.g. read from a spreadsheet. No user interaction should be necessary (reason: I need to batch process some 100 variables, that means, my workbook is being invoked a 100 times).

The method SAPBEXSetFilterValue does not work in this scenario, because of the above mentioned variable processing by user exit.

I thought, the SAPBEXsetVariables(varValues As Range) method might be the solution. Unfortunately, I couldn't find out, how to handle this method.

I understand, that the method is reading the variables and their respective values from an Excel Range object.

But I have no idea how variables and values need to be arranged within the range object.

Any help on this topic? Am I on the right track at all?

Thanks in advance.

Kind Regards,

Yogen Weinreich

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Nov 18, 2004 at 12:02 AM

    Hi Yogen,

    Sorry I did not see your posting until just today.

    I have not been able to use SAPBEXsetVariables to my satisfaction. But, I think I can tell you what to do.

    1. set the local query property to "Save and reuse variable values;

    2. use VB to set the variable value directly in the array that starts in cell FY2 on the worksheet named "SAPBEXqueries";

    3. refresh the query.

    Step 2 is the issue, right?

    To make this worksheet visible, the VBA command is:

    Sheets("SAPBEXqueries").visible = True

    to return it to its original condition, the VBA command is:

    Sheets("SAPBEXqueries").visible = xlSheetVeryHidden

    You do not have to make it visible to change the values on it; but you probably will need to in order to find exactly what cells you want to change and the format you will need.

    If you have more than one query embedded in your workbook, it can get confusing. If you need more, write back.

    Regards, Pete

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Pete,

      This is really helpful information. However I wanted to pass these parameters by my sheet only.

      1.) If you have any document which can help me to identity the different parameters in ("SAPBEXqueries") sheeet. So that I can fill them automatically in VBA macro and pass that range in Run "SAPBEX.xla!SAPBEXsetVariables", varRng.. function. to run query.

      2.) How can we hide the query designer window while refreshing the query through VBA Macro and passing these parameters from VBA Macro only.

      If any one can help me in these two issue, that will really greatful.

      Thanks

      Sharad Singh