excel 使用调用方法运行两个VBA子程序

wj8zmpe1  于 2023-05-08  发布在  其他
关注(0)|答案(1)|浏览(253)

我已经编写/录制了两个单独的宏,一个进入SAP下载一些信息,另一个然后将下载的信息拉入/格式化到我的原始电子表格中。
这两个宏都可以自己完美地运行。我遇到的问题是,当我试图在pulldemand宏中调用ConnectToSAPGUI宏时,当它试图读取export.XLSX并指出下标超出范围时,代码中断。
我不明白这中间的差距在哪里。一开始我认为宏可能运行得太快,无法保存从SAP下载的文件,所以我在代码中暂停了5秒,但这并没有解决问题。
任何建议,我去错了,我可以做些什么来解决它是赞赏。
PS:这是我第一次尝试使用宏与SAP接口。从我在网上找到的其他教程中获得了我用来这样做的代码。

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
lnvxswe2

lnvxswe21#

如果您的问题是等待文件下载...我用这个:

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

相关问题