Using Excel VBA to automate reporting

[Former Member]

Hi,

I am trying to automate some reporting. I need to change the prompt variables and then refresh.

I have tried the code below, and a LOT of variations around it.

If some one could point out my newbie error, or perhaps even post some simple examples, that would be great.

tia,

Peter

Sub RefreshPrompts() 
    DoSAPRefreshPrompts InputBox("Enter in format mm.yyyy", "Run Month/Year", "09.2012")
End Sub

Sub DoSAPRefreshPrompts(CalMonth As String)
    Dim iRet

    ' Logon to the application, returns no errors
    iRet = Application.Run("SAPLogon", "DS_1", "100", UserId, password)
    If val(iRet) <> 1 Then MsgBox "Logon failure. iRet = " & iRet, vbOKOnly, "Error"
      
    iRet = Application.Run("SAPSetRefreshBehaviour", "Off") ' Usually returns no error
    If val(iRet) <> 1 Then MsgBox "Refresh behaviou off. iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On") ' Usually returns no error
    If val(iRet) <> 1 Then MsgBox "Pause Variable. iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPSetVariable", "CAS_SM_CALMONTH", CalMonth) ' Returns error
    If val(iRet) <> 1 Then MsgBox "Set calender Month to " & CalMonth & ". iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPSetVariable", "FIM_COMP_CODE_AU", "AU10") ' Returns error
    If val(iRet) <> 1 Then MsgBox "Set AU10 variable. iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPSetVariable", "FIMPBDGV", "1") ' Returns error
    If val(iRet) <> 1 Then MsgBox "set '1' variable. iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off") ' Usually returns no error
    If val(iRet) <> 1 Then MsgBox "Pause Variable submit off. iRet = " & iRet, vbOKOnly, "Error"
    
    iRet = Application.Run("SAPSetRefreshBehaviour", "On") ' Usually returns no error
    If val(iRet) <> 1 Then MsgBox "Refresh behaviour On. iRet = " & iRet, vbOKOnly, "Error"
End Sub

[sebastian_keim · Accepted solution]

Peter,

Have you tried the "SAPSetVariable" with the "Value Format" parameters, such as Text, String, INTERNAL_KEY or INPUT_STRING?

Worked for me..especially the INPUT_STRING.And be sure to include your "DS_1" as parameter.

What you could also try to do: select a cell within your data grid before calling "SAPSetVariable".

Have you considered "SAPSetFilter"?

BR

Sebastian

[Former Member · Accepted solution]

Hi Sebastian,

Thanks for you help, it is much appreciated.

I had tried the various Value Formats, without success.

In the SAPSetVariable, I can't see a parameter to set the Datasource, my "DS_1" value. Is there another command I should be issueing?

I'll try your other suggestions and see how I go.

Cheers,

Peter

[Former Member · Accepted solution]

I have exactly the same problem. This code doesn't work either:

lResult = Application.Run("SAPSetVariable", "0COMP_CODE", "9999", "Key", "DS_1")

And even Application.Run("SAPGetProperty", "LastError", "Text") results just in an empty string. If I omit the "DS_1" parameter in the above statement then Application.Run("SAPGetProperty", "LastError", "Text") returns the correct error message: 'Variables are unmerged, data source alias needs to be specified'

BUT:

Using the SAPSetFilter function with exactly the same parameters works flawless:

lResult = Application.Run("SAPSetFilter", "DS_1", "0COMP_CODE", "9999", "Key")

Unfortunately I need to change the variables not only the filter values

[james_batchelor2 · Accepted solution]

All,

Any progress here?  We are on 1.3 Sp4...SetVariable as described in the help does not work.  SetFitler does.   I have tried many configurations of the syntax with no luck.

jim

[Former Member · Accepted solution]

Hi Jim,

Did you check the whole Syntax

Call Application.Run("SAPSetVariable", "0S_CUST", lList, "INPUT_STRING", "DS_1")

DS_1 is the Data Source name

You can find all Parameters in the function bar in Excel.

Also I am wondering that the technical name of your Variable starts with F. Name Range?

Switch on technical names in the settings. Call the prompt Screen and you See the technical Name of your Variable.

Regards,

Ralf

[james_batchelor2 · Accepted solution]

couple of things.

Thanks for the syntax.  That worked...+ a little editing in building the string.

value - value  (note spaces)  Not obvious in help.

So complete value syntax for me was  "value1 - value2; value3 - value4"

note space behind ;

thanks!

jim

[Former Member · Accepted solution]

For me the problem is solved.

It was due to the fact, that you need to get the variable name (if one is used in the query definition) from the query designer.

That's why this code has not worked:

lResult = Application.Run("SAPSetVariable", "0COMP_CODE", "9999", "Key", "DS_1")

For the characteristic 0COMP_CODE a variable was used in the query definition:

So using:

lResult = Application.Run("SAPSetVariable", "YRGE0130", "9999", "Key", "DS_1")

works flawless.

I hope this helps...

[former_member331343]

Hi

I'm using the SAPSETVARIABLE to set a value in the prompt - so far so good.

But how can I clear a variable?

If I use Spplication.run("SAPSETVARIABLE", "ZSA_COC1", "", "KEY", "DS_1") then the variable is set to ="#" - not assigned.

Thank you for a Feedback.

Roger

[reyemsaibot]

Hi Roger,

when I use SAPSETVARIABLE like this

lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

lResult = Application.Run("SAPSetVariable", "0I_CUSTO", "255", "INPUT_STRING", "DS_1")

MsgBox lResult

lResult = Application.Run("SAPSetVariable", "0I_CUSTO", "", "INPUT_STRING", "DS_1")

it shows first the customer 255 and then all customers. But instead of KEY I use INPUT_STRING.

Tobias

[Former Member]

I am quite new to this concept of pulling data from SAP to EXCEL using VBA. I copied the above code and tried to execute, and naturally it gives error.

I have used the method of calling a function module / BAPI from excel vba with success, but alas no success with T Codes or other standard reports of SAP.

It would be nice if some more guidance and on this concept can be shared, with sample files and script if possible

[TammyPowlas]

If you are new to this, I don't recommend copying the code above - I recommend copying sample code from the SAP Help - please see this recording to show you how:

http://scn.sap.com/docs/DOC-20676

Regards,

Tammy

[david_stocker]

Have you tried setting a breakpoint in the code in the VBA editor and then triggering your macro?  Set a breakpoint near the top (but not on a Dim declaration line) of the macro and then step through it, line by line, with Shift-F8.  Use the locals and watch windows to track the various parameters inside the macro to isolate it to the line in question.  Then we can look at it further.

Cheers,

David

[Former Member]

Tammy Powlas

Thnx for the input, saw ur posted video, it was interesting.

It would be nice if you can let me know how to activate "Analysis" Tab in Excel. I am using Excel 2007

Also is there similar tutorials for accessing Std. Reports from Core SAP ECC from Excel VBA.

Regards

Debabrata

[TammyPowlas]

Do you have a license for Analysis Office?  Because once you install then and then launch it, the ribbon is active.