使用Excel宏从SAP中提取数据

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. 从excel中的列表中复制通知编号。
3. Go to the appropriate screen in SAP and paste this number in the search box.
4. 转到SAP中的相应屏幕并将此数字粘贴到搜索框中。
5. Open the long text box.
6. 打开长文本框。
7. Copy the long text.
8. 复制长文本。
9. Paste into excel.
10. 粘贴到excel中。

Here is the link VBA pulling data from SAP for dummies

以下是VBA从SAP中获取数据的链接

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不会导出longtext并且需要上帝的行为来修复它。

2 个解决方案

#1

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

#2

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