sapgui scripting - export report to Excel then return to sap

[Former Member]

I am new to SAP and my company has just updated to 4.7. I can export a report to MS Excel as spreadsheet, run a excel macro on the spreadsheet in excel, save the excel workbook in a local directory, then close & exit excel. However, I can not get the script to continue after excel is closed. The script does not respond. the following is an example of the code:

If Not IsObject(application) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

Set connection = application.Children(0)

End If

If Not IsObject(session) Then

Set session = connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject application, "on"

End If

session.findById("wnd[0]/usr/ctxtS_POSID-LOW").text = "me24810110"

session.findById("wnd[0]/usr/ctxtS_POSID-HIGH").text = "me24810111"

session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/tbar[1]/btn[43]").press

session.findById("wnd[1]/tbar[0]/btn[0]").press

session.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").select

session.findById("wnd[1]/tbar[0]/btn[0]").press

session.findById("wnd[1]/tbar[0]/btn[0]").press

set ns1=createobject("WScript.shell")

ns1.AppActivate "Microsoft Excel"

ns1.SendKeys "%TMM", True

ns1.SendKeys "PERSONAL.XLS!EOM_Status", True

ns1.SendKeys "%R", True

ns1.SendKeys "%FA H:\2005_Monthly_Status\EOM_CIP_Status.xls ", True

ns1.SendKeys "%Y", True

ns1.SendKeys "%Fx", True

Now... from here, the SAP report that was exported is showing on the screen, but I can't get the script to recognize any of the controls on the screen.

Any help would be greatly appreciated.

[Christian_Cohrs]

Hi Alan,

I made a very simple test, without any problem:

...

MsgBox session.Id

set ns1=createobject("WScript.shell")

ns1.AppActivate "Microsoft Excel"

MsgBox "Excel"

ns1.SendKeys "%Fx", True

MsgBox session.Id

Here the final message box is displayed. Could you explain the problem a bit more? Does the script not respond at all, or is it just a problem of finding controls on the screen?

Best regards,

Christian

[Former Member]

First, I want to thank you for your response. It's greatly appreciated.

The message box example you sent me does and did work before. I am attaching example code (I've numbered some of the lines of code so we can reference the same code) followed by specific events. Example code follows:

If Not IsObject(application) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

Set connection = application.Children(0)

End If

If Not IsObject(session) Then

Set session = connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject application, "on"

End If

session.findById("wnd[0]/usr/ctxtS_POSID-LOW").text = "me24730110"

session.findById("wnd[0]/usr/ctxtS_POSID-HIGH").text = "me24730111"

session.findById("wnd[0]/usr/ctxtS_POSID-HIGH").setFocus

session.findById("wnd[0]/usr/ctxtS_POSID-HIGH").caretPosition = 10

session.findById("wnd[0]").sendVKey 0

1. session.findById("wnd[0]/tbar[1]/btn[8]").press

2. session.findById("wnd[0]/tbar[1]/btn[43]").press

3. session.findById("wnd[1]/tbar[0]/btn[0]").press

4. session.findById("wnd1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").select

5. session.findById("wnd[1]/tbar[0]/btn[0]").press

6. session.findById("wnd[1]/tbar[0]/btn[0]").press

7. set ns1=createobject("WScript.shell")

8. ns1.AppActivate "Microsoft Excel"

9. ns1.SendKeys "%TMM", True

10. ns1.SendKeys "PERSONAL.XLS!EOM_Status", True

11. ns1.SendKeys "%R", True

12. ns1.SendKeys "%FA H:\SAP_Scripts\EOM-Status\t1Data.xls ", True

13. ns1.SendKeys "%Y", True

14. ns1.SendKeys "%Fx", True

15. 'MsgBox session.Id

16. session.findById("wnd[0]/tbar[0]/btn[3]").press

Specific events:

First scenario ... With line 15 (MsgBox) commented out. The script runs great until line 16 . Excel is activated, the report data exported to a spreadsheet, the excel macro executed properly, excel file saved, and excel is exited properly. When the excel app is closed out, the script is left in the same condition as after execution of line 1 (the SAP report data has been selected and is presented in report format) and the excel app is no longer resident. Line 16 depresses the green back arrow and would return SAP to the previous screen. But nothing happens. It's like the script does not see line 16 and ends after excel is closed out in line 14.

Second scenario ... With line 15 (MsgBox) NOT commented out. Excel is activated, the report data exported to a spreadsheet, and the excel macro begins execution. During the excel macro execution, the MsgBox is displayed (/app/con[0]/ses[0]) and everything halts after the ecxel macro completes. When I hit the OK button in the MsgBox, an SAP information modal box is displayed with the caption "Save data in spread sheet". When I hit the green check mark (enter) in the information modal box, I am returned to same condition as after execution of line 1 (the SAP report data has been selected and is presented in report format) however, the excel app is still showing the result of the executed excel macro, the excel file is not saved, and the excel app is still resident. It appears that the script halted when the MsgBox was executed.

Please... any suggestions.???

[Christian_Cohrs]

Hi Alan,

how do you start the script? Do you double-click the VBS file in explorer, or do you run it from the built-in SAP GUI player?

Best regards,

Christian

[Former Member]

Hi Christian...

I run the SAP script from the built-in SAP GUI player. Does this make a difference.???

Thanks... Al

[Christian_Cohrs]

Hi Alan,

I'm afraid it does. The internal playback mechanism is significantly less stable when it comes to these synchronisation dependant scripts. Please try to run the script from wscript.exe by double-clicking it. Does that solve the problem?

Best regards,

Christian

[Former Member]

Thanks for the response Christian...

I've logged on to SAP, gone to the page required for the script to start from, brought up explorer, then double clicked on the .vbs script file. The outcome was the same as before.

Thanks again Christian.

[Former Member]

Hi Christian...

Thanks to your response concerning the differences in launching the script using the SAP GUI player and just double clicking the vbs script in explorer, I've come up with a solution. It apears that the SAP GUI player loses the fact that the script is a WScript after the Excel app is closed out. But if I execute the script via explorer it still recognizes the script as a WScript. So I put the Script to sleep for 10 seconds which evidently gave Excel enough time to finish and close allowing the remainder of the script to execute. If you have any other ideas or pointers, please, let me know. Thanks again for your help.

Al