SAP WScript download to Excel

[MF.Cummings]

I am attempting to automate some routine data pulls from SAP. I have a WScript that mostly works but is missing some important steps. When initiating a Transaction in SAP, the resulting dataset can be downloaded locally in the form of an Excel spreadsheet. When the sheet populates, the user is expected to Save the document (the default workbook name is "Spreadsheet in Basis(1)" )

I have a basic knowledge of how to create an Excel object, I use macros frequently so I also have some knowledge of VBA. What I would like to do is take control of the open workbook, save it in a specified location with a specific name prior to returning control back to the master script, and back to the SAP navigation routine.

Here's my WScript so far:

Dim iHsh
Dim sFile
Dim i, s

sFile = "D:\Documents and Settings\Desktop\SAP Login\ARQ General.sap"
iHsh = Array(144, 155, 155, 115, 64, 176, 150, 114)

If Not isObject(application) Then
   set shell = CreateObject("Wscript.Shell")
   Shell.run chr(34) & sFile & Chr(34), 1, false
   Set Shell = nothing
End If

      Set WshShell = WScript.CreateObject("WScript.Shell")
      
      WshShell.AppActivate "ARP General"
      WScript.Sleep 2000
		'~ For i = 0 to UBound(iHsh)
			'~ s = Array(s & Chr(AscB(Mid(iHsh, i, 1))))
		'~ Next
      WshShell.SendKeys ("******") 'still working on the Hash function to remove the open password
      WScript.Sleep 1000
      WshShell.SendKeys "{TAB} {ENTER}"
	  WScript.Sleep 3000
	  
'******* SAP Takes over here *********************************************************************************

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]").maximize
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00007"
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00007"
session.findById("wnd[0]/tbar[1]/btn[17]").press
'Here we select the variant
session.findById("wnd[1]/usr/txtV-LOW").text = "MFC_IW59"
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[8]").press
'kick it off
session.findById("wnd[0]/tbar[1]/btn[8]").press
'now we wait for our data to populate


'********** Here is where the Excel download parameter selection process begins ************
'spreadsheet request button (Shift+F4)
session.findById("wnd[0]/tbar[1]/btn[16]").press
'"excel: Number of Key Columns" popup window and button
session.findById("wnd[1]/tbar[0]/btn[0]").press
'"Export list object to XXL" window - pivot table selection popup window and button
session.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").select
'selects Table over Pivot-table
session.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").setFocus
'click check box and move to next option
session.findById("wnd[1]/tbar[0]/btn[0]").press
'Export list object to XXL -- click check box and Excel pops up
session.findById("wnd[1]/tbar[0]/btn[0]").press

'*********waiting for excel to populate, takes approx 30 seconds, may want to sleep here or look for a triggering event***

set objXL = Wscript.createobject("Excel.Application")
objXl.AppActivate "Microsoft Excel"
objXl.Visible = True



'**** Now we return to SAP and navigate back to initial screen for next Tcode
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
'now we are back to the initial SAP Home screen

My question is "What is the correct syntax to take control of an active process (Excel) and execute code to Save the workbook prior to closing the workbook and returning control to SAP?"

Thank you for your reply,

MFC