如何用FOR循环简化SAP GUI脚本，选中指定列前25项

问题描述 #

我尝试在SAP GUI中选中已知列名的前25行数据，录制了如下VBScript脚本，脚本可以正常运行，但希望通过FOR循环（行范围1至25、固定目标列）简化代码，请问有什么优化方案？

原录制脚本：

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"  ' 修正原脚本笔误：app改为application
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell 10,"ST_RESERVA"
set coll1 = application.createGuiCollection
coll1.add "10,ST_RESERVA"
coll1.add "10,FEVOR"
coll1.add "10,AUFNR"
coll1.add "10,MATNR"
coll1.add "10,ZAGRLOGISTICO"
coll1.add "10,MAKTX"
coll1.add "11,ST_RESERVA"
coll1.add "11,FEVOR"
coll1.add "11,AUFNR"
coll1.add "11,MATNR"
coll1.add "11,ZAGRLOGISTICO"
coll1.add "11,MAKTX"
coll1.add "12,ST_RESERVA"
coll1.add "12,FEVOR"
coll1.add "12,AUFNR"
coll1.add "12,MATNR"
coll1.add "12,ZAGRLOGISTICO"
coll1.add "12,MAKTX"
coll1.add "13,ST_RESERVA"
coll1.add "13,FEVOR"
coll1.add "13,AUFNR"
coll1.add "13,MATNR"
' ... 以此类推直到完成25行的列选择
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectedCells = coll1
set coll1 = nothing

优化方案 #

通过数组存储列名+双层For循环的方式简化代码，核心思路是把重复的列名和行号遍历逻辑交给循环处理，避免手动重复添加每一项：

优化后的完整脚本 #

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If

session.findById("wnd[0]").maximize
Dim gridShell
Set gridShell = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
gridShell.setCurrentCell 10,"ST_RESERVA"

' 1. 定义需要选择的列名数组
Dim targetColumns
targetColumns = Array("ST_RESERVA", "FEVOR", "AUFNR", "MATNR", "ZAGRLOGISTICO", "MAKTX")

' 2. 创建GuiCollection并通过循环添加选中项
Set coll1 = application.createGuiCollection
Dim rowNum, colName
' 遍历25行：从10开始到34（10+24=34），对应原脚本的起始行号，可根据实际需求调整
For rowNum = 10 To 34
    ' 遍历所有目标列
    For Each colName In targetColumns
        coll1.Add rowNum & "," & colName
    Next
Next

' 应用选中的单元格集合
gridShell.selectedCells = coll1
Set coll1 = nothing
Set gridShell = nothing

关键优化点说明 #

• 复用列名数组：把需要选择的列名统一存到数组targetColumns中，后续修改列名只需调整数组内容，无需逐个修改add语句。
• 双层循环简化重复操作：外层循环遍历行号（这里从10到34共25行，可根据实际起始行号调整），内层循环遍历列名数组，自动生成每行每列的选中项。
• 对象变量复用：将网格控件对象赋值给gridShell变量，避免重复调用findById，提升脚本执行效率。
• 修正原脚本笔误：将原脚本中未定义的app对象改为application，避免运行时错误。

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