VBA - Controlling SAP...not waiting for excel sheet to open

[nayr1482]

I am using vba to interact with SAP and get data out, etc...
Generally it all works fine.

In this particular section, when I run the whole program it always errors out - it seems to not be allowing the Excel Sheet to actually open before moving on.
I have tried adding DoEvents and a wait as you can see - but get the same result.

When I run this section independent of the whole program or if I step through here it works fine.

Any ideas?


What is supposed to happen, and what works in all other segments I have coded this way...on the last session.findById("wnd[1]").sendVKey 0 it should open the data from SAP in Excel, then save it. But like I said in this section for some reason the Excel doc isnt opening.

'Export the file
    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_EXPORT"
    session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&XXL"


    'Select Radio button "Select from All Available Formats"
    session.findById("wnd[1]/usr/radRB_OTHERS").Select
    'Select "Excel (In Existing XXL Format)"
    session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
    session.findById("wnd[1]").sendVKey 0
    'Acknowledge "Filter criteria..." dialogue box
    On Error Resume Next
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
    If Err.Number <> 0 Then
        session.findById("wnd[1]").sendVKey 0
    End If
    On Error GoTo 0
    'Select "Table" radio button
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
    session.findById("wnd[1]").sendVKey 0
    session.findById("wnd[1]").sendVKey 0
    DoEvents
    Application.Wait Now + #12:00:05 AM#
    
    
    'Save CS12
    objExcel.DisplayAlerts = False
    objExcel.ActiveWorkbook.SaveAs "C:\temp\COOIS.xlsx"
    objExcel.DisplayAlerts = True

 

[mrshl9898]

Maybe you could loop through all workbooks until it opens?

Something like the below, but maybe with a wait or a counter.

Sub LoopEachOpenWorkbook()


Dim wb As Workbook
Dim success As Long


success = 0


Do Until success = 1
For Each wb In Application.Workbooks
  If wb.Name <> "PERSONAL.xlsb" And wb.Name <> ThisWorkbook.Name Then
    objExcel.DisplayAlerts = False
    objExcel.ActiveWorkbook.SaveAs "C:\temp\COOIS.xlsx"
    objExcel.DisplayAlerts = True
    success = 1
  End If
Next wb
Loop


End Sub

 

[nayr1482]

I tried this and variants of it without any luck.

Here is what I have ended up with as a band-aid for the moment....
Basically if I get the error I just have it retry. Seems to always work on the 2nd try - weird.

'Export the file
    session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&NAVIGATION_PROFILE_TOOLBAR_EXPAND"
TryAgain:
    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"


    'Select Radio button "Select from All Available Formats"
    session.findById("wnd[1]/usr/radRB_OTHERS").Select
    'Select "Excel (In Existing XXL Format)"
    session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
    session.findById("wnd[1]").sendVKey 0
    'Acknowledge "Filter criteria..." dialogue box
    On Error Resume Next
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
    If Err.Number <> 0 Then
        session.findById("wnd[1]").sendVKey 0
    End If
    On Error GoTo 0
    'Select "Table" radio button
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
    session.findById("wnd[1]").sendVKey 0
'    Application.Wait Now + #12:00:01 AM#
    session.findById("wnd[1]").sendVKey 0
    
    
'    Do Until ActiveWorkbook.Name <> ThisFile
'        Application.Wait Now + #12:00:01 AM#
'    Loop
    
    
    
    
    
    'Save CS12
    objExcel.DisplayAlerts = False
    On Error Resume Next
    objExcel.ActiveWorkbook.SaveAs "C:\temp\COOIS.xlsx"
    objExcel.DisplayAlerts = True
    If Err.Number <> 0 Then
        On Error GoTo 0
        session.findById("wnd[1]").sendVKey 0
        GoTo TryAgain
    End If
    On Error GoTo 0

 

[mirelmo67]

Please refer to this link - it helped me a lot to sort out this issue.