如何循环遍历Excel现有行直至最后一行，优化SAP GUI取数更新表格的VBA代码

核心优化逻辑为统计Excel工作表A列有效行数作为循环边界，将重复的行级操作封装到循环结构中，即可适配任意行数的提取需求，无需逐行编写执行逻辑。

优化后完整VBA代码 #

Sub SAP_OpenSessionFromLogon()
    Dim SapGui
    Dim Applic
    Dim connection
    Dim session
    Dim WSHShell
    Dim maxRow As Long
    Dim i As Long
    Dim myGrid As Object
    
    ' 启动并登录SAP
    Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe"
    Set WSHShell = CreateObject("WScript.Shell")
    Do Until WSHShell.AppActivate("SAP Logon ")
        Application.Wait Now + TimeValue("0:00:01")
    Loop
    Set WSHShell = Nothing
    Set SapGui = GetObject("SAPGUI")
    Set Applic = SapGui.GetScriptingEngine
    Set connection = Applic.OpenConnection("System", True)
    Set session = connection.Children(0)
    session.findById("wnd[0]").Maximize
    session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
    session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "Name"
    session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PW"
    session.findById("wnd[0]").sendVKey 0
    
    ' 进入目标事务码
    session.SendCommand ("/nZPS_RE008K_NEW")
    session.findById("wnd[0]").Maximize
    session.findById("wnd[0]/usr/ctxtI_VBUKR").Text = "1200"
    
    ' 获取FORMAT表A列最大有效行号作为循环边界
    maxRow = Sheets("FORMAT").Cells(Rows.Count, 1).End(xlUp).Row
    
    ' 循环处理每行数据
    For i = 6 To maxRow
        If Sheets("FORMAT").Cells(i, 1) Like "*IE-12-2007984*" Then
            ' 可在此处补充对应匹配逻辑
        End If
        ' 填入查询参数执行查询
        session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(i, 1)
        session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        
        ' 提取SAP Grid数据回写Excel
        Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
        ' SAP Grid行索引从0开始，若需返回多行可在此处嵌套内层循环遍历myGrid.RowCount
        Sheets("Format").Cells(i, 3).Value = myGrid.getcellvalue(0, "ZZ_PTREL")
        Sheets("Format").Cells(i, 30).Value = myGrid.getcellvalue(0, "CASTKA_ROZPOCTU")
        
        ' 返回查询页面处理下一行
        session.findById("wnd[0]/tbar[0]/btn[3]").press
    Next i
    
    ' 释放对象
    Set myGrid = Nothing
    Set session = Nothing
    Set connection = Nothing
    Set Applic = Nothing
    Set SapGui = Nothing
End Sub

补充说明 #

如果单条查询返回的SAP GUI表格存在多行数据，可在内层新增循环遍历SAP Grid所有行，通过myGrid.RowCount获取SAP表格的总行数即可适配任意长度的SAP返回结果。

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