Skip to Content
0
Sep 27, 2019 at 08:00 AM

Switching between SAP and e.g. Excel - Macro/Script

392 Views

Hi. I have succesfully created a macro, that (1) runs in the active session of SAP. When done, it (2) open an excel file (in background - not visible), does a lot of things in here with the SAP extracts, then in the end, copies a list from the excel file, saves and closes the file, (3) switches back to the open SAP session and continued to pull data from the copied list from the excel file. After done, it (4) opens the same excel file again and put it the last details from SAP.


My issues/questions are:

1: I am dependent on keeping the active SAP session "selected" in Windows. If I change to another windows (like browser, excel, etc) the script will fail (cant find session) when trying to reconnect to the active session (part 3 above). Is there any way I can optimize the way the script switches between SAP and other programs or the way it finds the needed SAP session without this window having to be "selected" in Windows?

2: Is there a way of doing the SAP work in background like for Excel (visible = False)

Thank you

Current 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


set session = application.ActiveSession


'Sap Extract 1 - Taken out - Not relevant for example

'Sap Extract 2 - Taken out - Not relevant for example

'''''In excel
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("  "File Path"  , 0, FALSE)
objExcel.Application.Visible = False
'objExcel.UserControl = true
objExcel.Application.Run "Excel Macro 1"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing


''''''' Back to SAP for 2nd part of extracts (this is where is fails if I change active window)

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

set session = application.ActiveSession

'Sap Extract 3 - Taken out - Not relevant for example

'''''Back to Excel
Set objExcel = CreateObject("Excel.Application")
'
Set objWorkbook = objExcel.Workbooks.Open("  "  File Path ", 0, FALSE)
objExcel.Application.Visible = True
'objExcel.UserControl = true
objExcel.Application.Run "Macro 2"
objExcel.Application.Run "Macro 3"