0

I have a VBA code that mixes SAP GUI Scripting and VBA.

Basically I use a SAP transaction and I export it as a Excel sheet, after I click the export button it asks for a place to save.

I have recorded the script using SAP and inserted it into VBA.

The steps are:

  1. Click the Button to Export (SAP)

(apparently there is no code that involves the "Save As..." window, user must interact)

  1. Click the Button to Go Back to Main Page (SAP)

  2. Set Open Workbook a Name so I can work with it

The problem is that if I run the code it doesn't work. It is like the code doesn't wait until the workbook to open to go further.

I have tried:

  1. Adding time for the VBA code to wait
  2. Adding lines (e.g. MsgBox) between the events
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select '(Step 1)
session.findById("wnd[0]/tbar[0]/btn[3]").press '(Step 2)
session.findById("wnd[0]/tbar[0]/btn[3]").press '(Step 2)

Set Book = ActiveWorkbook '(Step 3)
Set Sheet = ZMMBBook.Worksheets("Sheet1") '(Step 3)

It is expected that the Workbook is set but instead the code runs before it opens. Even trying to add time in between it doesn't work, it is like it only opens after it progresses but when it progresses the workbook is not open so it results in a "Subscript Out of Range" error.

If I insert a breakpoint in the "Set" line the workbook opens and if I proceed the code works normally.

I would want as a solution:

  1. A Way To Insert Lines of Code that make the Code "break" as if in a Breakpoint (but smoothly so end-user can use it)

  2. A way in SAP GUI Scripting to interact with the Save Window (because I'd say it seems that it is what causes the bug)

  3. Any other solution

Thank you, all!

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
GBeltramini
  • 13
  • 6

1 Answers1

0

Try testing for a successful download of the file after pressing the last SAP GUI button before continuing with the VBA. Something like this...

session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select '(Step 1)
session.findById("wnd[0]/tbar[0]/btn[3]").press '(Step 2)
session.findById("wnd[0]/tbar[0]/btn[3]").press '(Step 2)

' Check the file downloaded successfully
If session.findById("wnd[0]/sbar").Text <> "" Then
    msgbox "There was an error downloading the file"
end if

Set Book = ActiveWorkbook '(Step 3)
Set Sheet = ZMMBBook.Worksheets("Sheet1") '(Step 3)

GUI Scripting is good at waiting for itself to finish processing something before continuing with the next command, but I used to have issues when stepping back and forth between SAP GUI Scripting and VBA.

Alternatively, do you need to export the file to get the data? My first preference was to extract the data straight out of the GUI from the Table, Grid or series of individual Fields where I could, into an array that I would then paste into the Excel Range. It means some more GUI Scripting but often ran faster and gave us more control over exactly what data we got and where the data went (Description fields often export with the same header but can be easily identified within the SAP GUI).

Josh
  • 236
  • 1
  • 2
  • 12