SAP GUI与VBA连接：循环中用变量替代[1,0]的技术求助

我需要修改一个连接SAP GUI的VBA宏，这个宏能完成一系列手动操作要花好几天的重复任务。我完全不懂SAP GUI Scripting语言，现在希望在循环运行时用递增变量替代代码里的[1,0]这类固定索引，操作界面是SAP ECC的IW32维护订单界面，我的SAP GUI Scripting知识为零。以下是我的VBA代码：

' Sua rotina continua aqui.
Sub CONEXÃO()
    Dim SAP, SAPGUI, Connections, cntConnection, i, Connection, RetVal
    'Parametros
    On Error GoTo erro
    Set SAP = GetObject("SAPGUI")
    Set SAPGUI = SAP.GetScriptingEngine()
    Set Connections = SAPGUI.Connections()
    cntConnection = Connections.Count()
    Set Connection = SAPGUI.Connections(CLng(i))

    'Check Conexão
    If IsObject(Connection) Then
        ConnectionExists = vbTrue
    Else
        ConnectionExists = vbFalse
erro:
        RetVal = Shell("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")   ' Execute o Programa.
        Set xl = CreateObject("excel.application")
        While xl.Ready = False
        Wend

        Set SapGuiAuto = GetObject("SAPGUI")
        Set SapApp = SapGuiAuto.GetScriptingEngine
        Set Connection = SapApp.OpenConnection("01 [BRF] - SAP ECC Produção -->Logon Automático<---", True)
        Set SapCon = SapApp.Children(0)
        Set session = SapCon.Children(0)


        'session.findById("wnd[0]").maximize
        'session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "mandante"
        'session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "usuário"
        'session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "senha"
        'session.findById("wnd[0]").sendVKey 0

        'Set session = Nothing
        'connection.CloseSession ("ses[0]")
        'Set connection = Nothing
        'Set sap = Nothing
    End If
End Sub

Sub Macro1()
    Application.Run "CONEXÃO"

    Dim App, Connection, session As Object
    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)

    Dim i, x As Long

    i = 2
    x = 0
    While Planilha1.Cells(i, "B") <> ""

        ORDEM = Planilha1.Cells(i, 2)
        CPP = Planilha1.Cells(i, 3)
        QUANTIDADE = Planilha1.Cells(i, 4)
        OPERACAO = Planilha1.Cells(i, 5)
        APROVADOR = Planilha1.Cells(i, 6)
        CONTA = Planilha1.Cells(i, 7)
        GESTOR = Planilha1.Cells(i, 8)
        TIPO = Planilha1.Cells(i, 9)
        CONTRATO = Planilha1.Cells(i, 10)
        ITEMC = Planilha1.Cells(i, 11)
        
        Do While i <= 2
            session.findById("wnd[0]").maximize
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw32"
            session.findById("wnd[0]").sendVKey 0
            session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = ORDEM
            session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").caretPosition = 12
            session.findById("wnd[0]").sendVKey 0
            session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1100/tabsTS_1100/tabpMUEB").Select
            i = i + 1
        Loop
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/ctxtRESBD-MATNR[1,0]").Text = CPP
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[4,0]").Text = "1"
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[4,0]").SetFocus
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[4,0]").caretPosition = 18
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[1]/usr/txtRCM01-VORNR").Text = OPERACAO
        session.findById("wnd[1]/usr/txtRCM01-VORNR").caretPosition = 2
        session.findById("wnd[1]").sendVKey 0
        session.findById("wnd[1]/usr/ctxtVG_SAKNR").Text = CONTA
        session.findById("wnd[1]/usr/ctxtVG_SAKNR").caretPosition = 6
        session.findById("wnd[1]/tbar[0]/btn[13]").press
        session.findById("wnd[0]/usr/chkEBAN-WEUNB").Selected = False
        session.findById("wnd[0]/usr/txtEBAN-AFNAM").Text = "433004"
        session.findById("wnd[0]/usr/ctxtEBAN-BEDNR").Text = APROVADOR
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").Text = "0001"
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").SetFocus
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").caretPosition = 4
        session.findById("wnd[0]/tbar[0]/btn[0]").press
    Wend

解决方案：用递增变量替换固定索引 #

1. 修复循环逻辑问题 #

原代码中Do While i <=2只会执行一次（i初始为2，执行后i变为3直接退出），需要将打开IW32、加载订单的逻辑移到外层循环，保证每行Excel数据对应处理一个SAP订单。

2. 新增递增索引变量 #

在Macro1中新增用于SAP表格行的索引变量，和Excel行循环同步递增。

3. 动态拼接控件ID #

将固定的[1,0]替换为变量拼接形式，SAP表格控件的索引格式为[行,列]，行/列通常从0开始计数（部分场景可能从1开始，需自行测试确认）。

修改后的完整代码 #

' Sua rotina continua aqui.
Sub CONEXÃO()
    Dim SAP, SAPGUI, Connections, cntConnection, i, Connection, RetVal
    'Parametros
    On Error GoTo erro
    Set SAP = GetObject("SAPGUI")
    Set SAPGUI = SAP.GetScriptingEngine()
    Set Connections = SAPGUI.Connections()
    cntConnection = Connections.Count()
    Set Connection = SAPGUI.Connections(CLng(i))

    'Check Conexão
    If IsObject(Connection) Then
        ConnectionExists = vbTrue
    Else
        ConnectionExists = vbFalse
erro:
        RetVal = Shell("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")   ' Execute o Programa.
        Set xl = CreateObject("excel.application")
        While xl.Ready = False
        Wend

        Set SapGuiAuto = GetObject("SAPGUI")
        Set SapApp = SapGuiAuto.GetScriptingEngine
        Set Connection = SapApp.OpenConnection("01 [BRF] - SAP ECC Produção -->Logon Automático<---", True)
        Set SapCon = SapApp.Children(0)
        Set session = SapCon.Children(0)


        'session.findById("wnd[0]").maximize
        'session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "mandante"
        'session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "usuário"
        'session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "senha"
        'session.findById("wnd[0]").sendVKey 0

        'Set session = Nothing
        'connection.CloseSession ("ses[0]")
        'Set connection = Nothing
        'Set sap = Nothing
    End If
End Sub

Sub Macro1()
    Application.Run "CONEXÃO"

    Dim App, Connection, session As Object
    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)

    Dim i, rowIndex As Long

    i = 2
    'rowIndex初始值根据SAP表格起始行调整，这里假设从0开始对应第一行
    rowIndex = 0
    While Planilha1.Cells(i, "B") <> ""

        ORDEM = Planilha1.Cells(i, 2)
        CPP = Planilha1.Cells(i, 3)
        QUANTIDADE = Planilha1.Cells(i, 4)
        OPERACAO = Planilha1.Cells(i, 5)
        APROVADOR = Planilha1.Cells(i, 6)
        CONTA = Planilha1.Cells(i, 7)
        GESTOR = Planilha1.Cells(i, 8)
        TIPO = Planilha1.Cells(i, 9)
        CONTRATO = Planilha1.Cells(i, 10)
        ITEMC = Planilha1.Cells(i, 11)
        
        ' 每次循环加载当前订单
        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw32"
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = ORDEM
        session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").caretPosition = 12
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1100/tabsTS_1100/tabpMUEB").Select

        ' 用递增变量替换固定索引[1,0]
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/ctxtRESBD-MATNR[" & rowIndex & ",0]").Text = CPP
        ' 数量字段索引同理替换，这里假设比物料字段晚3行，需根据实际界面调整
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[" & rowIndex + 3 & ",0]").Text = QUANTIDADE
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[" & rowIndex + 3 & ",0]").SetFocus
        session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1101/tabsTS_1100/tabpMUEB/ssubSUB_AUFTRAG:SAPLCOMK:3020/tblSAPLCOMKTCTRL_3020/txtRESBD-MENGE[" & rowIndex + 3 & ",0]").caretPosition = 18
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[1]/usr/txtRCM01-VORNR").Text = OPERACAO
        session.findById("wnd[1]/usr/txtRCM01-VORNR").caretPosition = 2
        session.findById("wnd[1]").sendVKey 0
        session.findById("wnd[1]/usr/ctxtVG_SAKNR").Text = CONTA
        session.findById("wnd[1]/usr/ctxtVG_SAKNR").caretPosition = 6
        session.findById("wnd[1]/tbar[0]/btn[13]").press
        session.findById("wnd[0]/usr/chkEBAN-WEUNB").Selected = False
        session.findById("wnd[0]/usr/txtEBAN-AFNAM").Text = "433004"
        session.findById("wnd[0]/usr/ctxtEBAN-BEDNR").Text = APROVADOR
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").Text = "0001"
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").SetFocus
        session.findById("wnd[0]/usr/ctxtEBAN-EKORG").caretPosition = 4
        session.findById("wnd[0]/tbar[0]/btn[0]").press

        ' 递增循环变量
        i = i + 1
        rowIndex = rowIndex + 1 ' 根据SAP表格行数量调整步长
    Wend
End Sub

关键注意事项 #

• 测试时先单步执行，确认rowIndex对应的SAP表格行是否正确
• 如果SAP表格的行索引从1开始，将rowIndex初始值设为1即可
• 数量字段的索引偏移量（rowIndex + 3）需根据实际界面布局调整

内容的提问来源于stack exchange，提问作者marlos isganzella