如何在VBA中将SAP选定单元格的值赋值给变量或Excel单元格

问题场景 #

我需要通过VBA将SAP中指定单元格的值赋值给变量，或直接写入Excel。当前用以下代码选中SAP表格中的单元格：

session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell 6, "DEBIT"

目标是从SAP事务码FAGLB03（总账科目余额显示）提取数据，不想用可靠性差的SendKeys。之前尝试了以下代码但失败：

session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").copy
session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(6, "DEBIT") = amt
session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(6, 1) = amt

解决方案 #

核心问题是赋值方向错误，getItemText是用于获取单元格值的方法，需将方法返回值赋值给变量，而非反过来。另外注意SAP表格控件的行/列索引规则：部分控件行号从0开始，部分从1开始，可根据实际测试调整。

正确取值语法 #

' 通过列名获取值
amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(行号, "列名")

' 或通过列索引获取值（索引通常从0开始）
amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(行号, 列索引)

修正后的完整代码 #

Sub SAPAuto()

Dim sh As Worksheet, wb As Workbook, tbl As ListObject, numrows As Integer, numcolmns As Integer, FiscalYear As Integer, _
    FiscalMonth As Integer, GLAccounts As Range, acct As Integer, co As String, CoCode As Range, amt As Variant
Set wb = ThisWorkbook
FiscalYear = wb.Worksheets("Driver").Range("B15").Value
FiscalMonth = wb.Worksheets("Driver").Range("B16").Value
Set GLAccounts = wb.Worksheets("Reference Tables").[GL_Accounts]
Set CoCode = wb.Worksheets("Reference Tables").[Company_Code]

' 初始化SAP连接
If Not IsObject(SAPGuiApp) Then
    Set SapGuiAuto = GetObject("SAPGUI")
    Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
    Set Connection = SAPGuiApp.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 SAPGuiApp, "on"
End If
    
' 遍历工作表和表格
For Each sh In wb.Worksheets
    co = sh.Range("A1")
    For Each tbl In sh.ListObjects
        sh.Activate
        numrows = tbl.DataBodyRange.Rows.Count
        ' 打开FAGLB03事务码
        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/nFAGLB03"
        session.findById("wnd[0]").sendvkey 0
        ' 输入查询参数
        session.findById("wnd[0]/usr/ctxtRACCT-LOW").Text = Right(tbl.Name, 5)
        session.findById("wnd[0]/usr/ctxtRBUKRS-LOW").Text = co
        session.findById("wnd[0]/usr/txtRYEAR").Text = FiscalYear
        session.findById("wnd[0]").sendvkey 8
        
        ' 提取DEBIT值并写入Excel
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "DEBIT"
        amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(FiscalMonth, "DEBIT")
        tbl.DataBodyRange.Cells(numrows, 4).Value = amt
        
        ' 提取CREDIT值并写入Excel
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "CREDIT"
        amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(FiscalMonth, "CREDIT")
        tbl.DataBodyRange.Cells(numrows, 5).Value = amt
        
        ' 提取BALANCE值并写入Excel
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "BALANCE"
        amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(FiscalMonth, "BALANCE")
        tbl.DataBodyRange.Cells(numrows, 6).Value = amt
        
        ' 提取CUMULATIVE BALANCE值并写入Excel
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").setCurrentCell FiscalMonth, "CUMULATIVE BALANCE"
        amt = session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").getItemText(FiscalMonth, "CUMULATIVE BALANCE")
        tbl.DataBodyRange.Cells(numrows, 7).Value = amt
    Next tbl
Next sh

MsgBox "Success"
End Sub

关键修改说明 #

1. 将变量amt的类型从Long改为Variant，避免数值格式不兼容的问题
2. 修正getItemText的赋值方向：amt = 控件.getItemText(...)
3. 删除原代码中错误的setCurrentCell ... = amt语句，改为先定位单元格，再取值赋值
4. 每一步提取值后直接写入对应Excel单元格

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