Running two VBA subs using call method

Question

I have written/recorded two separate macros, one that goes into SAP to download some information, and another that then takes the downloaded information and pulls/formats it into my original spreadsheet.

Both macros run perfectly by themselves. The problem I run into is when I try to call the ConnectToSAPGUI macro within the pulldemand macro, the code breaks when it tries to read export.XLSX and says that the subscript is out of range.

I don't understand where the disconnect is. I figured at first that the macro might be running too fast for the downloaded file from SAP to save, so I put a 5 second pause in the code, but this didn't solve the problem.

Any suggestions on where I'm going wrong and what I can do to fix it is appreciated.

PS: this is my first time trying to use a macro to interface with SAP. Got the code I used to do so from other tutorials I found online.

Sub ConnectToSAPGUI()

    'Catch and redirect errors in case SAP GUI is not
    'open or not accessible
    On Error GoTo NotConnected
    'Get the SAP GUI Scripting object
    Set SapGuiAuto = GetObject("SAPGUI")
    'Get the currently running SAP GUI
    Set SAPApp = SapGuiAuto.GetScriptingEngine
    'Get the first system that is currently connected
    Set SAPCon = SAPApp.Children(0)
    'Get the first session (window) on that connection
    Set session = SAPCon.Children(0)
    'Return to regular error handling
    On Error GoTo 0

    'Your code here
    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nyscmd04"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtS_MATNR-LOW").Text = Range("B1")
    session.findById("wnd[0]/usr/ctxtS_WERKS-LOW").Text = Range("B2")
    session.findById("wnd[0]/usr/ctxtS_DAT00-LOW").Text = Range("B3")
    session.findById("wnd[0]/usr/ctxtS_DAT00-HIGH").Text = Range("B5")
    session.findById("wnd[0]/usr/ctxtS_DAT00-HIGH").SetFocus
    session.findById("wnd[0]/usr/ctxtS_DAT00-HIGH").caretPosition = 8
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell 9, "MAKTX"
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectedRows = "9"
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
    session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/tbar[0]/btn[11]").press

    Set session = Nothing

    'Exception handling in case SAP GUI is not
    'open or not accessible
    Exit Sub

NotConnected:
MsgBox "Please log into SAP first.", vbCritical

End Sub





Sub pulldemand()

'Call ConnectToSAPGUI

Application.Wait (Now + TimeValue("0:00:05"))

    Windows("export.XLSX").Activate
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H2").FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1]),1)"
    Range("H2").AutoFill Destination:=Range("H2:H1642")

    Windows("Deamand Breakdown Report.xlsm").Activate
    Sheets("Sheet2").Select
    Range("B2:ZZ1000000").ClearContents
    Range("B2").FormulaR1C1 = _
        "=SUMIFS([export.XLSX]Sheet1!C13,[export.XLSX]Sheet1!C22,RC1,[export.XLSX]Sheet1!C8,R1C)"
    Range("B2").AutoFill Destination:=Range("B2:S2"), Type:=xlFillDefault
    Range("B2:S2").AutoFill Destination:=Range("B2:S17"), Type:=xlFillDefault
    Range("B2:S17").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("export.XLSX").Close (False)
    Sheets("Sheet2").Activate
    Range("A1").Select
    MsgBox ("Report Generated")

End Sub

If you get an error, it's useful to mention on which line the error occurs. — Tim Williams, Mar 05 '19 at 18:55
`Call`, as a VBA command, is deprecated and should not be used. And remember to use `Option Explicit`, this will help with debugging. — AJD, Mar 05 '19 at 19:04

score 0 · Answer 1 · answered Mar 05 '19 at 19:16

If your problem is waiting for the file to download... I use this:

Dim FilePath As String, FileName As String, FindIt As String

FilePath = wb.Path & "\"
FileName = FilePath  & "yourfilename"
FindIt = Dir(FileName ) 
While Len(FindIt) = 0  
  FindIt = Dir(FileName )
Wend

Running two VBA subs using call method

1 Answers1