How i can change the Query Parameter in SAP BusinessObjects Analysis for Excel via VBA Macro ?

[Former Member]

Hello,

regarding the SAP BusinessObjects Analysis for Excel i want to change the Query Paramenter via VBA Macro but i don't know how to start the aplication and how to set the filter.

Can somebody help me to realize that?

With kind regards
Andreas

[Former Member]

OK, i found a solution.

Sub SetVar ()

Dim lResult As Long
Dim allVar As String
Dim CWInterv As String

allVar = Sheets(10).Cells(8, 2).Value & "; " & Sheets(10).Cells(9, 2).Value
CWInterv = Sheets(10).Cells(2, 8).Value & " - " & Sheets(10).Cells(2, 9).Value

lResult = Application.Run("SAPSetVariable", "MSN List", allVar, "INPUT_STRING", "DS_1")
lResult = Application.Run("SAPSetVariable", "Week Interval", CWInterv, "INPUT_STRING", "DS_1")

End Sub

thanks a lot for your professional help

With kind regards

Andreas

[Former Member]

Right. this is what I wanted to write right now, as well...

I tested this for Calendarmonth/Year variable and it worked:

lResult = Application.Run("SAPSetVariable", "ACALMONTH", "07.2013 - 08.2013", "INPUT_STRING", "DS_1")

So this is the way how to fill your variable...

Regards, Martin

[Former Member]

Matin, thanks a lot for your help.

Now the script works very well

With kind regards

Andreas

[Former Member]

Perfect, thanks a lot for your Help.

Now this part is working. 🙂

The only thing needs to be clarified is, how to put "from - to" (CW) into this script.

The same script is not working,  maybe becasue of "Input_String"

Do you know hoe to fill it?

Best regards

Andreas

[Former Member]

This is correct in a normal script i cannot use variable names between"" but in this script i can use only one variable name between "" an it is working, but with two or three variable names not. 😞

I tryed a lot of version, without "", or with "" and without ;, nothing is working 😞

Best regards

Andreas

[Former Member]

This is what I said?!

Try the following:

Dim all_vars as String

all_vars = A1 & ";" & A2 & ";" & A3

lResult = Application.Run("SAPSetVariable", "MSN List", all_vars, "INPUT_STRING", "DS_1")

[Former Member]

I know the debugger 🙂 🙂 but in this line is an "Listenzeichen" Error??? 🙂

My parmaeters are an multi select (for example; part 1 ; part 2; part 3) and the second one is from CW to CW.

If i put the parts directly in this code it is working without problems but in my case i need the parts as a Variable and with this the cose is not working. Maybe there is a problem wit the semicolon !!

Best regards

Andreas

[Former Member]

Hi Andreas,

I think the problem is that semicolon here is not allowed, except in " A1; A2, A3 " ... since you are using variable names, you cannot put it between " ".

I suggest to use only one variable, which has the whole content of A1, A2, and A3 separated by semicolon.

Best regards,

Martin

[Former Member]

Hi

If you check the help for SAPSetVariable you will find the following;

Call Application.Run("SAPSetRefreshBehaviour", "Off")

Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")

Call Application.Run("SAPSetVariable", "0BWVC_COUNTRY", "US")

Call Application.Run("SAPSetVariable", "0BWVC_MATERIAL", "Hardware")

Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")

Call Application.Run("SAPSetRefreshBehaviour", "On")

Not sure of your requirements but I needed to use the login too

lResult = Workbooks(lstrBook).Application.Run("SAPLogon", lstrQryID, "600", strUserName, strPassword)

[Former Member]

Hello,

thanks a lot and now i understand better what the mean in the help function but how i can put more than one variable.

Only one is working but more than one seperated wit a semicolon is not working

For Example:

Sub SetVar()

Dim lResult As Long
Dim A1 As String
Dim A2 As String
Dim A3 As String

A1 = Cells(9, 1).Value
A2 = Cells(10, 1).Value
A2 = Cells(11, 1).Value

lResult = Application.Run("SAPSetVariable", "MSN List", A1; A2; A3, "INPUT_STRING", "DS_1")

End Sub

I dont know what the problem is.

Best regards

Andreas

[Former Member]

Hi Andreas,

Is your parameter a multi select?

What I aslo found in my case is that the workbook needs to be refreshed before the parameters can be set. In my VBA I refresh and then set the parameters.

[Former Member]

Hi Andreas,

If you don't "know" what the problem is, you can make the Debugger tell you 🙂

Just include new variable:

Dim VlastError As Variant

And use this "SAPGetProperty" always as second line after any "lResult =" functionality.

lResult = Application.Run("SAPSetVariable", "MSN List", A1; A2; A3, "INPUT_STRING", "DS_1")
VlastError = Application.Run("SAPGetProperty", "LastError")

When you check VlastError it tells you an error number & message.

The help document of AO gives a hint in the chapter "LastError-Informationen verwenden"

Best regards,

Martin