Search code examples
excelvbavbscriptsap-gui

repeating script that goes from SAP GUI to Excel and back again

I am trying to build a script that takes the data out of a huge report in SAP application, runs some cleanup in Excel, and then load it into Access. At least, that is my eventual goal. The data in SAP is a bit polluted, which is why we are resorting to Access as the place where cleansed data specific to our department lives.

I'm new to scripting (after 20 years, I don't remember much of what I used to know) but I have been using the built in macro generators in both SAP and Excel and have gotten part of the way to what I want. What I am running into right now is this error:

Microsoft Excel is waiting for another application to complete an OLE action

This happens presumable because my report takes a very long time to run. It stops the process and this is supposed to be something I can set and forget and have the data in the morning.

The script starts as a macro in Excel, connects to SAP application via SAP GUI and triggers the report there. The report runs and then uses SAP to export it back to Excel. Given the size of report I can only run it in one or two month chunks, but I need to get data for an entire year. I figure I can have the script repeat itself 6 times, 2 months at a time. Anything more than 2 months and the report will time out before it produces anything. I have tried to use "Run in Background" on SAP to simplify this, but the output generated is so garbled as to be useless.

Below is the code that I have so far.

This does what I want, except I have to babysit through the Excel error message and I don't know how to get the focus back to SAP.

Thanks for any help or articles you can direct me to.

Sub Experimental1()

' start the SAP portion

Dim SapGuiAuto, application, connection, session, WScript
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
'
' ***the above is key to opening a script in SAP.  SAP must be running for  this to work***
' begin ZSPWAR launch
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "zspwar"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 17
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]").sendVKey 8         session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellRow = 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "8"
session.findById("wnd[1]").sendVKey 2
' adjust the date
session.findById("wnd[0]/usr/ctxtS_DATE-LOW").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_DATE-HIGH").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
'begins save portion of script
'ignore warning from excel and keep going through long task
'application.IgnoreRemoteRequests = True
' end caffeine like behavior
session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
 session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press

'Ends Save portion

'back into Excel

  ChDir _
         "G:\Warranty\Strategy's\Special Projects\001 Process and Procedure      Documentation\Databases\Core Return Database"
     ActiveWorkbook.SaveAs Filename:= _
         "G:\Warranty\Strategy's\Special Projects\001 Process and Procedure      Documentation\Databases\Core Return Database\ZSPWAR test1.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Solution

  • I offer you a way to solve your problem.

    For example:

    Sub Experimental1()

' start the SAP portion

Dim SapGuiAuto, application, connection, session, WScript
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPapplication = SapGuiAuto.GetScriptingEngine
Set connection = SAPapplication.Children(0)
Set session = connection.Children(0)
'
' ***the above is key to opening a script in SAP.  SAP must be running for  this to work***
' begin ZSPWAR launch
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "zspwar"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 17
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellRow = 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "8"
session.findById("wnd[1]").sendVKey 2
' adjust the date
session.findById("wnd[0]/usr/ctxtS_DATE-LOW").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_DATE-HIGH").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
'begins save portion of script
'ignore warning from excel and keep going through long task
'application.IgnoreRemoteRequests = True
' end caffeine like behavior
session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
 session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press

'Ends Save Portion

'for the SAP German version
'SAP_Workbook = "Tabelle von Basis (1)"

'for the SAP English version (?)
SAP_Workbook = "Worksheet in ALVXXL01 (1)"
EXCEL_Path = "G:\Warranty\Strategy's\Special Projects\001 Process and Procedure Documentation\Databases\Core Return Database"
myWorkbook = "ZSPWAR test1.xlsx"


On Error Resume Next
Do
Err.Clear
Set xclapp = GetObject(, "Excel.Application")
If Err.Number = 0 Then Exit Do
'msgbox "Wait for Excel session"
wscript.sleep 2000
Loop


Do
Err.Clear
Set xclwbk = xclapp.Workbooks.Item(SAP_Workbook)
If Err.Number = 0 Then Exit Do
'msgbox "Wait for SAP workbook"
wscript.sleep 2000
Loop


On Error GoTo 0


Set xclsheet = xclwbk.Worksheets(1)

xclapp.Visible = True
xclapp.DisplayAlerts = False

xclapp.ActiveWorkbook.SaveAs EXCEL_Path & "\" & myWorkbook
xclapp.ActiveWorkbook.Close


Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
Set xclapp = Nothing

'only if an information on the display
session.findById("wnd[1]/tbar[0]/btn[0]").press

    Regards, ScriptMan