How to set variables values via VBA.

[Former Member]

Anybody please help.

How to set variables values via VBA in workbook. SAP Netweaver 2004s.

[Former Member · Accepted solution]

I do not know solution with VBA. But you can try following 2 alternatives.

1) You can set the default value in the variable itself.

2) You can set the variant (at the selection screen) and can reuse it.

[Former Member · Accepted solution]

Thanks for your reply

I had this idea, it is not a very nice realization

[former_member213877 · Accepted solution]

Hi,

Variables can be set using

Run "sapbex.xla!SAPBEXsetVariables", varRng

where varRng is a range that contains the variable values you want to set.

Hope this helps you..!!

-Pradnya

[Former Member · Accepted solution]

Sap Bex Analyzer use BExAnalyzer.xla in Bi 7.0.

Function SAPBEXsetVariables is empty.

Function SAPBEXsetVariables(varValues As Range) As Integer

End Function

[Former Member · Accepted solution]

After we add macros to workbook using buttons "Add Macro" in the properties of a workbook. Added module BExInterfaceModule, which contains the function BEx ():

Public Function BEx () As BExApplication

Set BEx = Application.Run ( "BExAnalyzer.xla! GetBEx", ThisWorkbook)

End Function

After that, we can invoke a method of object SetVariables.

For example: Bex.SetVariables.

But we do not know the types of parameters that need to refer to this method.

Can anybody help identify these parameters?

[Former Member · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

Pass variable values with VBA and BI 7.0 funtions to Query

At first a remark u2013 Iu2019ve read a lot of threads saying that passing values to a query can be done by using VBA code only. Iu2019ve tested it but Iu2019m not sufficient with the new BEX 7.0 API and therefore I use a mixture of BEX 7.0 funtionality and VBA. I create a BEX 7.0 design item button passing the values to a query u2013 I hide this button somewhere on the sheet or on a hidden sheet and I then raise the event to click the button from VBA code. Works fine and the maintenance is easier if something changes in the API in the future again.

How to start:

Switch to design mode in BEX Analyzer:

Implement a BEX 7.0 design item u201Cbuttonu201D

Click on the button to implement the properties

Make the input for the commands

data_provider = dataprovider_1

cmd = process_variables

subcmd = var_submit

No comes the part with the variables u2013 Letu2019s assume a query has 4 variables but you only want to change 1 with the button u2013 an organizational unit for instance.

Make a range somewhere in the excel with the following structure:

Name Index Value

VAR_NAME_1 1 Variablename

VAR_VALUE_EXT_1 1 variablevalue

Value should contain the name of your variable of course and u201Cvalueu201D the value of your variable

Set a name for this range with EXCEL functionality but without the header:

Back to the properties of the button: Insert the name of the range with the variables in the field Command Range:

If you have more variables to process you can of course enhance your Filterrange!

In the left upper Corner you have a name for your button:

Now you can raise the button-click in vba like this:

Application.Run "'" & ThisWorkbook.Name & "'!Sheet2.BUTTON_35_Click"

regards, Lars

[former_member341062 · Accepted solution]

Hi Lars,

Do you know of a whitepaper on what commands/subcommands can be called with the BEx 7.0 command button? Is it only those few available through the menu or are there more? (I mean, there must be more because the whole analyzer seems crippled compared to 3.x.. )

[former_member185837 · Accepted solution]

quote:

Now you can raise the button-click in vba like this:

Application.Run "'" & ThisWorkbook.Name & "'!Sheet2.BUTTON_35_Click"

end quote.

I tried this line of code in the sub workbook_open() of ThisWorkbook object.

On my PC it works perfectly. But on a user's PC we get this error:

runtime error 91 object variable or With block variable not set

I also tried the simple instruction:

Sheet2.BUTTON_35_Click

but it won't run anyway..

Have you got any idea why this happens?

Edited by: Davide Cavallari on Jul 11, 2011 6:47 PM

[Former Member · Accepted solution]

There are three radio buttons available:

"Workbook-Specific Command",

"Planning-Specific Command", and

"Data Provider-Specific Command"

In the "Select Command Type" dialog upon configuring the button, none have the input for commands as detailed in the Jun 10, 2009 9:12 AM post. Please help with more specific instructions.

[Former Member · Accepted solution]

Scott, did you ever get an answer to your question?

I'm finding that the post isn't exactly what I'm seeing as I create the command button.

For instance, there is no Sub-Command, also what is there about the static Parameters in the properties of the button? Should these be set up?

I believe I followed the instructions correctly. But, still not working..

[Former Member]

Hi all,

I am looking for the same thing as Lohith. I am not sure if we have a ready made command that we can include under the static parameters in the button properties or have to include macro functionality.

Regards

Kanchana

[Former Member]

Hi all,

I refresh this issue because helped me to find out a solution how to automatically set the variables and refresh a workbook. But now I have a problem. I have a workbook with two queries and I need to to refresh them separately (I'm using the same time variable, for the first query it shows actual information and for the second one it shows data till Dec. current year), any ideas how to do this? Also I'm using two differente multi providers.

thanks a lot,

Martin

[former_member215195]

Hi All,

     Request you to share the details on how to refresh individual queries manually?

Command Run("BExAnalyzer.xla!MenuRefreshVariables") seem to be running all the queries together. Does this command accept any parameter with which we can mention the query to be executed?

Pls suggest.

Lohith

[Former Member]

Thank you very much!

We tried to do it early, but it did not work.

Maybe because the patch level was lower.