使用Excel宏从SAP提取数据 [英] Pulling data from SAP using Excel Macros

问题描述

因此，我尝试使用excel宏从SAP提取数据.我是VBA的新手，请多多包涵.我在这里找到了一个名为VBA的主题，该主题从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:

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

这是链接 VBA从SAP获取虚拟变量的数据

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

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

我们非常感谢您的帮助.我这样做的原因是因为SAP不会导出长文本，并且修复它需要采取上帝的行动.

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.

推荐答案

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

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.

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

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

这篇关于使用Excel宏从SAP提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！