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

nayr1482

Board Regular
Joined
Feb 2, 2009
Messages
75
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.


Code:
'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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Maybe you could loop through all workbooks until it opens?

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

Code:
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
 
Last edited:
Upvote 0
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.


Code:
'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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top