如何使用Excel VBA将SAP树视图中的值复制到excel工作表中

Sub CopySAPTreeViewValues()
    Dim SAPApp As Object
    Dim SAPGUI As Object
    Dim SAPConnection As Object
    Dim SAPSession As Object
    Dim SAPTree As Object
    Dim SAPNode As Object
    Dim ExcelSheet As Worksheet
    Dim RowIndex As Integer
    
    ' 创建SAP的COM对象
    Set SAPApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set SAPGUI = SAPApp.GetScriptingEngine
    Set SAPConnection = SAPGUI.OpenConnection("SAP Logon")
    Set SAPSession = SAPConnection.Children(0)
    
    ' 进入SAP树视图
    SAPSession.findById("wnd[0]").maximize
    SAPSession.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
    SAPSession.findById("wnd[0]").sendVKey 0
    
    ' 获取树视图对象
    Set SAPTree = SAPSession.findById("wnd[0]/usr/cntlTREE_CONTAINER/shellcont/shell")
    
    ' 创建新的Excel工作表
    Set ExcelSheet = ThisWorkbook.Sheets.Add
    
    ' 复制树视图中的值到Excel工作表
    RowIndex = 1
    CopySAPTreeViewNodeValues SAPTree, ExcelSheet, RowIndex
    
    ' 关闭SAP连接
    SAPSession.findById("wnd[0]").CloseConnection
    
    ' 释放对象
    Set SAPApp = Nothing
    Set SAPGUI = Nothing
    Set SAPConnection = Nothing
    Set SAPSession = Nothing
    Set SAPTree = Nothing
    Set ExcelSheet = Nothing
End Sub

Sub CopySAPTreeViewNodeValues(SAPNode As Object, ExcelSheet As Worksheet, ByRef RowIndex As Integer)
    Dim ChildNode As Object
    
    ' 复制当前节点的值到Excel工作表
    ExcelSheet.Cells(RowIndex, 1).Value = SAPNode.Text
    RowIndex = RowIndex + 1
    
    ' 复制子节点的值到Excel工作表
    For Each ChildNode In SAPNode.Children
        CopySAPTreeViewNodeValues ChildNode, ExcelSheet, RowIndex
    Next ChildNode
End Sub