How to open excel spreadsheet called from SAP during Excel VBA Macro

Question

long time reader, first-time poster

I have a macro that exports production reports from SAP in xlsx format. The thing is, I need four of these reports for our four production lines (A, B, C & D), but when the macro runs its course only two or three print out (Normally just B, C, & D print out but sometimes just C & D).

Basically, I'm looking for a way so that as the excel spreadsheets are exported from SAP in the macro, they pop up as soon as they are generated instead of waiting until the end and hoping for the four to pop up. Is there some sort of loop or delay that will allow this to occur? I know that SAP saves them to a certain folder so I know they're exporting from SAP, but they're just not popping up when the macro finishes.

Here's an example of the code I'm running that will open one of the four line's production numbers. I'm taking dates from an Excel spreadsheet to filter the posting date of when a box or truck of plastic is made. I basically just repeat the part from 'A line data gathering' another three times and that completes my macro.

Sub Get_SAP()
Dim App, Connection, session As Object

 Sheets("Sheet1").Select
 Range("B8").Select
Selection.Copy
 a_value = Range("B4").Text
 b_value = Range("B5").Text

'Open Up SAP on Screen'

 Set SapGuiAuto = GetObject("SAPGUI")
 Set App = SapGuiAuto.GetScriptingEngine
 Set Connection = App.Children(0)
 Set session = Connection.Children(0)

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

'A Line Data Gathering'

'Type in /ncooispi'

session.findById("wnd[0]/tbar[0]/okcd").Text = "/ncooispi"
session.findById("wnd[0]").sendVKey 0

'Type in search parameters in Cooispi'

session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/cmbPPIO_ENTRY_SC1100-PPIO_LISTTYP").Key = "PPIOD000"
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_SC1100-ALV_VARIANT").SetFocus
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_SC1100-ALV_VARIANT").Text = "BRUCE-4"
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_WERKS-LOW").Text = "AC55"
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_PARBPL-LOW").Text = "A-Extruder"
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_PARBPL-LOW").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_PARBPL-LOW").caretPosition = 8
session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&NAVIGATION_PROFILE_TOOLBAR_EXPAND"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_VARIANT"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&COL0"
session.findById("wnd[1]/usr/tabsG_TS_ALV/tabpALV_M_R3").Select
session.findById("wnd[1]/usr/tabsG_TS_ALV/tabpALV_M_R3/ssubSUB_DYN0510:SAPLSKBH:0600/cntlCONTAINER2_FILT/shellcont/shell").currentCellRow = 2
session.findById("wnd[1]/usr/tabsG_TS_ALV/tabpALV_M_R3/ssubSUB_DYN0510:SAPLSKBH:0600/cntlCONTAINER2_FILT/shellcont/shell").selectedRows = "2"
session.findById("wnd[1]/usr/tabsG_TS_ALV/tabpALV_M_R3/ssubSUB_DYN0510:SAPLSKBH:0600/btn600_BUTTON").press
session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/btn%_%%DYN003_%_APP_%-VALU_PUSH").press
session.findById("wnd[3]/usr/tabsTAB_STRIP/tabpINTL").Select
session.findById("wnd[3]/usr/tabsTAB_STRIP/tabpINTL/ssubSCREEN_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL_255-ILOW_I[1,0]").Text = ""
session.findById("wnd[3]").sendVKey 14
session.findById("wnd[3]/usr/tabsTAB_STRIP/tabpINTL/ssubSCREEN_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL_255-IHIGH_I[2,0]").Text = ""
session.findById("wnd[3]").sendVKey 14

'Plug numbers from Excel into Posting Date Filters'
session.findById("wnd[3]/usr/tabsTAB_STRIP/tabpINTL/ssubSCREEN_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL_255-ILOW_I[1,0]").Text = a_value
session.findById("wnd[3]/usr/tabsTAB_STRIP/tabpINTL/ssubSCREEN_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL_255-IHIGH_I[2,0]").Text = b_value

'Get A Line spreadsheet'

session.findById("wnd[3]/tbar[0]/btn[8]").press
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&XXL"    session.findById("wnd[1]/tbar[0]/btn[0]").press
 session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "ALINEPRODUCTION.XLSX"
 session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 16
 session.findById("wnd[1]/tbar[0]/btn[11]").press

 Sub End

ScriptMan ScriptMan · Accepted Answer · 2017-03-03T09:44:11

I would test if the file already exists at the destination.

for example:

'Get A Line spreadsheet'

session.findById("wnd[3]/tbar[0]/btn[8]").press
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd [0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&XXL"    session.findById("wnd[1]/tbar[0]/btn[0]").press

'-------------- new -----------------------------------------------------
Set fso = CreateObject("Scripting.FileSystemObject")
File_Name = "c:\tmp\ALINEPRODUCTION.XLSX"
If fso.fileExists(File_Name) Then
  Set myfile = fso.GetFile(File_Name) 
  myfile.Delete
end if
'--------------- new -----------------------------------------------------

session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "ALINEPRODUCTION.XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 16
session.findById("wnd[1]/tbar[0]/btn[11]").press
'---------------- new ----------------------------------------------------
do    
  if fso.fileExists(File_Name) Then Exit do
  Application.Wait (Now + TimeValue("0:00:01"))
loop

How to open excel spreadsheet called from SAP during Excel VBA Macro

1 Answers