1

I have written the below code to export data from SAP GUI.

If I go step by step everything works fine. Excel is exported and data is copied to the proper place. But if I try to run it NOT step by step it goes on error (subscript out of range).

The problem is at the 'close SAP session part of the code. from that point the code does not run forward if I try to run it at once. I tried to use a timer which stops the code until the exported file opens but it does not help but I am sure that the problem is that the exported file opens too slowly and the code can't run forward.

Do you have any idea how to solve this? Thanks

Sub SapExport_Returns()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim Wbname As String
    
    'SapExport file is open with Cover sheet filled and Data sheet empty
    
    Set wb = Workbooks("SAP export")
    Set ws = wb.Worksheets("Cover")
    Set ws2 = wb.Worksheets("Data")
    
    
    'turn off screen update
    
    Application.ScreenUpdating = False
    If Not IsObject(SAPApplication) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set SAPApplication = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(SAPConnection) Then
       Set SAPConnection = SAPApplication.Children(0)
    End If
    If Not IsObject(SAPsession) Then
       Set SAPsession = SAPConnection.Children(0)
    End If
    If IsObject(WScript) Then
       WScript.ConnectObject SAPsession, "on"
       WScript.ConnectObject Application, "on"
    End If
    
    'Disable the Application Alert before saving the file
    
    Application.DisplayAlerts = False
    
    SAPsession.findById("wnd[0]").maximize
    'transaction code
    
    SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00006"
    
    'company code
    
    SAPsession.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").Text = CStr(ws.Range("C3").Value)
    
    'open items on date
    
    SAPsession.findById("wnd[0]/usr/ctxtPA_STIDA").Text = ws.Range("H5").Value
    
    'layout
    
    SAPsession.findById("wnd[0]/usr/ctxtPA_VARI").Text = CStr(ws.Range("H8").Value)
    SAPsession.findById("wnd[0]/usr/ctxtPA_VARI").SetFocus
    SAPsession.findById("wnd[0]/usr/ctxtPA_VARI").caretPosition = 11
    SAPsession.findById("wnd[0]/tbar[1]/btn[8]").press
    SAPsession.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
    
    'workbook naming
    
    Wbname = CStr(ws.Range("C15").Value)
    
    SAPsession.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Wbname
    SAPsession.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
    SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
    
    'close SAP session
    
    'session.findById("wnd[0]").maximize
    'session.findById("wnd[0]/tbar[0]/btn[15]").press
    'session.findById("wnd[0]/tbar[0]/btn[15]").press
    
    'Enabling the Application Alerts after saving the file
    
    Application.DisplayAlerts = True
    
    'wait until SAP exported file opens
    
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 50
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    
    'copying formatted data to file and close SAP extract
    
    Workbooks(Wbname).Worksheets("Sheet1").Range("A1").CurrentRegion.Copy ws2.Range("A2")
    ws2.Range("A2").CurrentRegion.Columns.AutoFit
    ActiveWindow.DisplayGridlines = False
    ws2.Name = "FBL5N"
    wb.Sheets.Add.Name = "Data"
    
    'Turn on screen update
    
    Application.ScreenUpdating = True
    
End Sub
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Fábián Varga
  • 11
  • 1
  • No experience with SAP and you did not show your timer attempt but maybe try a `Do While` loop to wait for the `wnd[0]` element is available? Something like `Do While session.findById("wnd[0]") Is Nothing: DoEvents: Loop`. Note that it can potentially goes into infinite loop. – Raymond Wu Mar 20 '22 at 04:21
  • I've no experience with SAP either. You control your SAP app kind of remotly. Isn't there a better way to get the data from SAP ? A query to get the data directly into Excel without controling the SAP Controlwindow ? I assume there is something out of sync while running the macro. – MaMe Mar 20 '22 at 06:52
  • 1
    Where excatly do you get the error message? You need to be aware that the SAPGUI will export the data into an Excel file asynchonously and often also in another instance , i.e. the file is not reachable with the line `Workbooks(Wbname).Worksheets("Sheet1").Range("A1").CurrentRegion.Copy ws2.Range("A2").` This [Code](https://www.ms-office-forum.net/forum/showpost.php?p=2062065&postcount=12) might help you to do what you want. – Storax Mar 20 '22 at 07:57

0 Answers0