title: repeating script that goes from SAP to Excel and back again
body: <p>I am trying to build a script that takes the data out of a huge report in SAP, 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.<br>
  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 the 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 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. Here is what I have so far. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>This does what I want, except I have to babysit through the excel error message and I dont know how to get the focus back to SAP</p>
  
  <p>Thanks for any help or articles you can direct me to.</p>