Macro can't recognize exported workbook from SAP via VBA

Question

I have been working on SAP GUI scripting to set up automatic reports. In my macro I'm running SAP transactions, exporting them to excel (which opens a "Workbook in basis(1)") and then copy some info from that file into my "Macro workbook". However, sometimes it works and sometimes it doesn't. The problem is that the excel workbook that is created from SAP is in a different instance, so my macro is unable to use it.

Is there any way in which I can ensure that the excel generated by SAP is always in the same instance? or get my macro to recognize workbooks in other instances?

This is the code I'm using (WB_Macro is the workbook that has the macro itself and WB_SAP is the worbook generated by SAP):

Dim WB_SAP As Workbook

'Save input in variables
countryCode = Sheets("FBL5N").Cells(2, 10).Value
salesOrganization = Sheets("FBL5N").Cells(2, 11)


'Run SAP transaction
objSess.findById("wnd[0]/tbar[0]/okcd").Text = "fbl5n"
objSess.findById("wnd[0]/tbar[0]/btn[0]").press
objSess.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").Text = countryCode
objSess.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").SetFocus
objSess.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").caretPosition = 4
objSess.findById("wnd[0]/usr/btn%_DD_KUNNR_%_APP_%-VALU_PUSH").press
For i = 1 To size
    objSess.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1," & i - 1 & "]").Text = customerCodes(i)
    Next i
objSess.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").caretPosition = 6
objSess.findById("wnd[1]/tbar[0]/btn[8]").press

objSess.findById("wnd[0]/usr/ctxtPA_VARI").Text = "/liber auto"
objSess.findById("wnd[0]/usr/ctxtPA_VARI").SetFocus
objSess.findById("wnd[0]/usr/ctxtPA_VARI").caretPosition = 12
objSess.findById("wnd[0]/tbar[1]/btn[8]").press
objSess.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
objSess.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
objSess.findById("wnd[1]/tbar[0]/btn[0]").press
objSess.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
objSess.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
objSess.findById("wnd[1]/tbar[0]/btn[0]").press
objSess.findById("wnd[1]/tbar[0]/btn[0]").press

'Activate and Capture SAP generated workbook
Workbooks("Worksheet in Basis (1)").Activate
Set WB_SAP = ActiveWorkbook
Debug.Print WB_SAP.Name

WB_SAP.Sheets(1).Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
objSess.findById("wnd[1]/tbar[0]/btn[0]").press


WB_Macro.Activate
Sheets("FBL5N").Select
Range("Table11[[   CoCd]]").Select
ActiveSheet.Paste

Please read the following link: stackoverflow.com/questions/40724085/… — ScriptMan

Storax Storax · Accepted Answer · 2018-02-14T15:19:38

This has probably nothing to do with SAP. You probably activated the option to ignore remote requests.

Just run this in the immediate window

Application.IgnoreRemoteRequests = False

BTW, you could improve your code by using

With objSess
   .FindById("wnd[0]/tbar[0]/okcd").Text = "/nfbl5n"


End With

Macro can't recognize exported workbook from SAP via VBA

1 Answers