vba 使用 Excel 宏从 SAP 中提取数据

Pulling data from SAP using Excel Macros

提问by adastra

So I am trying to pull data from SAP using excel macros. I am new to VBA so please bear with me. I found a topic on here called VBA pulling data from SAP for dummies and I am confused. What I am trying to do is as follows:

所以我正在尝试使用 excel 宏从 SAP 中提取数据。我是 VBA 新手，所以请耐心等待。我在这里找到了一个名为 VBA 从 SAP 为傻瓜提取数据的主题，我很困惑。我想要做的是如下：

1. Copy a notification number from a list in excel.
2. Go to the appropriate screen in SAP and paste this number in the search box.
3. Open the long text box.
4. Copy the long text.
5. Paste into excel.

1. 从 excel 列表中复制通知编号。
2. 转到 SAP 中的相应屏幕并将此数字粘贴到搜索框中。
3. 打开长文本框。
4. 复制长文本。
5. 粘贴到excel中。

Here is the link VBA pulling data from SAP for dummies

这是从 SAP 为傻瓜提取数据的链接VBA

I can't seem to get by Set session = connection.Children(0) 'Get the first session (window) on that connection.

我似乎无法通过 Set session = connection.Children(0) '获取该连接上的第一个会话（窗口）。

Any help is much appreciated. The reason I am doing this is because SAP wont export longtext and it takes an act of God to get it fixed.

任何帮助深表感谢。我这样做的原因是因为 SAP 不会导出长文本，而且它需要上帝的作为来修复它。

回答by DeerSpotter

This is what i use for all my connections for SAP:

这是我用于 SAP 的所有连接的内容：

'Connect to SAP to run automation.
        If Not IsObject(SAP_applic) Then
            Set SapGuiAuto = GetObject("SAPGUI")
            Set SAP_applic = SapGuiAuto.GetScriptingEngine
        End If
            Set connection = SAP_applic.Children(0)
        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

回答by Brian

If I'm reading this correctly, you're wanting to extract the long text information from a notification. If that is the case, I have a text file that you can import into the visual basic editor and then run that macro in your spreadsheet. The only thing you need to have is the first column containing the list of your notification number in your spreadsheet (be sure the first notification number starts in cell A2). In cell A1, input Notification number or something like that. For cell B2 input Description therefore you'll know what each column represents. I don't know if you're using transaction IQS3 to access your notification, but this is where I go to view all of our notifications that are created. If not, then hopefully this template my still be of some use to you as a go by or something.

如果我没看错，您可能想从通知中提取长文本信息。如果是这种情况，我有一个文本文件，您可以将其导入 Visual Basic 编辑器，然后在电子表格中运行该宏。您唯一需要的是电子表格中包含通知编号列表的第一列（确保第一个通知编号以单元格 A2 开头）。在单元格 A1 中，输入通知编号或类似内容。因此，对于单元格 B2 输入说明，您将知道每列代表什么。我不知道您是否使用事务 IQS3 来访问您的通知，但这是我查看我们创建的所有通知的地方。如果没有，那么希望这个模板我仍然对你有用。

Simply copy and paste the following code below into notepad and save it somewhere that you can access when importing into your spreadsheet.

只需将以下代码复制并粘贴到记事本中，然后将其保存在导入电子表格时可以访问的位置。

Dim i As Integer
Sub Main()

    Call MsgBox("Excel will minimize during this task to allow you to do some other work while it runs. " _
                & vbCrLf & "" _
                & vbCrLf & "It takes approximately 9 seconds per EWR number to retrieve the data from SAP." _
                & vbCrLf & "" _
                & vbCrLf & "Thanks for your patience and understanding, while the code runs. :)" _
                , vbInformation, "See you soon!")

    With Application
        .ScreenUpdating = False
        .Cursor = xlWait
        .Visible = False
    End With
    On Error GoTo Main_Error

    If Not IsObject(sapApplication) Then
        Set SapGuiAuto = GetObject("SAPGUI")
        Set sapApplication = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
        Set Connection = sapApplication.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 sapApplication, "on"
    End If
    i = 2
    'For i = 2 To LastRow(Sheet1)
    Do Until Cells(i, 1).Value = ""
        If Cells(i, 1).Value = "" Then GoTo errReturn
        Application.StatusBar = "Row: " & i & ": Retrieving details for EWR: " & Cells(i, 1).Value
        Cells(i, 2) = Populate(Session, Cells(i, 1).Value, i)
        Cells(i, 1).VerticalAlignment = xlCenter
        Cells(i, 2).VerticalAlignment = xlCenter
        Cells(i, 2).HorizontalAlignment = xlCenter
        If Not Cells(i, 2).MergeCells = True Then Rows.AutoFit
        i = i + 1
        DoEvents
        'Next i
    Loop

    Columns("A:B").AutoFit

    On Error GoTo 0
errReturn:
    With Application
        .ScreenUpdating = True
        .Cursor = xlNormal
        .StatusBar = False
        .Visible = True
    End With
    Exit Sub

Main_Error:
    MsgBox "You need to connect to the SAP GUI to use this spreadsheet", vbCritical, "Error"
    GoTo errReturn
End Sub

Function Populate(Session, EWRNumber As String, j As Integer) As String
    On Error GoTo continue
    Dim strpopulate As String
    'Dim j As Integer
    strpopulate = ""
    'j = 1
    With Session
        '.findById("wnd[0]").maximize
        .findById("wnd[0]/tbar[0]/okcd").Text = "/nIQS3"
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]/usr/ctxtRIWO00-QMNUM").Text = EWRNumber
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB01/ssubSUB_GROUP_10:SAPLIQS0:7235/subCUSTOM_SCREEN:SAPLIQS0:7212/subSUBSCREEN_1:SAPLIQS0:7715/btnQMICON-LTMELD").press
        .findById("wnd[0]/mbar/menu[2]/menu[2]").Select
        n = 1
        Do Until .findById("wnd[0]/usr/tblSAPLSTXXEDITAREA/txtRSTXT-TXLINE[2," & n & "]").Text = "________________________________________________________________________"
            strpopulate = strpopulate & .findById("wnd[0]/usr/tblSAPLSTXXEDITAREA/txtRSTXT-TXLINE[2," & n & "]").Text
            strpopulate = strpopulate & vbCrLf
            n = n + 1
            'MsgBox (CDbl(n / 29) = CInt(n / 29))
            If CDbl(n / 29) = CInt(n / 29) Then
                Call MergeCells(j)    '= 29
                i = i + 1
                'j = j + 1
            End If
        Loop
        .findById("wnd[0]/tbar[0]/btn[15]").press
        .findById("wnd[0]/tbar[0]/btn[15]").press
    End With
    'MsgBox strpopulate
continue:
    Debug.Print strpopulate
    Populate = strpopulate
End Function

Sub MergeCells(j As Integer)
    Cells(j, 1).Select
    'Insert row below active cell
    ActiveCell.Offset(1).EntireRow.Insert

    'Merge Selected Cells and Newly inserted Cells
    Cells(j, 1).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).Merge
    Cells(j, 2).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).Merge

    ActiveCell.Select
    Cells(j, 1).VerticalAlignment = xlCenter
    Cells(j, 2).VerticalAlignment = xlCenter
    Cells(j, 2).HorizontalAlignment = xlCenter
    Cells(j, 2).WrapText = True
    Rows(j).RowHeight = 409
    Rows(j + 1).RowHeight = 409
End Sub