时间戳

首页 > 解决方案 > 如何确保 Excel 读取数据并将其放入 SAP？

vba - 如何确保 Excel 读取数据并将其放入 SAP？

问题描述

嗨，我正在编写代码，但我似乎无法弄清楚如何让 Excel 读取数据以在 SAP 中发布。例如，我想在 fb02 中输入一个文档编号，让 Excel 从第一个数据单元格工作到最后一个数据单元格。但我似乎无法正确设置功能。

在 Excel 中，我在 A 列（Documentnr）和 B 列（Inkooporder）中有数据，我希望它从单元格 A2 和 B2 开始到最后一个单元格。

如果使用Irow我得到

运行时错误 1004。

这就是我所拥有的

Public Sub Ordernr()
    Dim W_Vouchernr As String
    Dim W_Inkooporder As String
    Dim lineitems As Long

    Dim LastRow As Long
    LastRow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(x1Up).Row

    Dim IRow as Long
    For Irow = 2 To LastRow

       'vouchernr
    If Worksheets("Sheet2").Cells(iRow, 1) <> "" Then
        W_Vouchernr = Worksheets("Sheet2").Cells(iRow, 1)
    Else
        W_Vouchernr = "xxxxxxxxxx"
    End If

    'inkooporder
    If Worksheets("Sheet2").Cells(iRow, 2) <> "" Then
        W_Inkooporder = Worksheets("Sheet2").Cells(iRow, 2)
    Else
        W_Inkooporder = "xxxxxxxxxx"
    End If

    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(SAP_session) Then    
        Set SAP_session = Connection.Children(0)   
    End If

    If IsObject(Wscript) Then   
        Wscript.ConnectObject SAP_session, "on"
        Wscript.ConnectObject SAPGuiApp, "on"
    End If

    SAP_session.findById("wnd[0]").maximize
    SAP_session.findById("wnd[0]/tbar[0]/okcd").Text = "/nfb02"
    SAP_session.findById("wnd[0]").sendVKey 0
    SAP_session.findById("wnd[0]/usr/txtRF05L-BELNR").Text = W_Vouchernr
    SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").Text = companycode
    SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").SetFocus
    SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").caretPosition = 4
    SAP_session.findById("wnd[0]").sendVKey 0 
    SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell")             
    .currentCellColumn
    SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell")
    .currentCellColumn
    SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell")
    .doubleClickCurrentCell
    SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").Text = W_Inkooporder           
    SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").SetFocus
    SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").caretPosition = 14
    SAP_session.findById("wnd[0]").sendVKey 0
    SAP_session.findById("wnd[0]").sendVKey 11
    'Gets the message from the status bar and save it in column B
    Worksheets("Sheet2").Cells(iRow, 3) =SAP_session.findById("wnd[0]/sbar").Text

Next Irow

End Sub

标签： vbaexcelsap

解决方案

我对 SAP 没有任何想法，但是您需要For围绕我认为的完整代码进行循环，并且您需要定义它是什么LastRow。

所以这可能有效：

Public Sub Ordernr()
    Dim W_Vouchernr As String
    Dim W_Inkooporder As String
    Dim lineitems As Long

    Dim LastRow As Long
    LastRow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'find last used row in column A

    Dim iRow As Long
    For iRow = 2 To LastRow 'loop from row 2 to last used row

        'vouchernr
        If Worksheets("Sheet2").Cells(iRow, 1) <> "" Then
            W_Vouchernr = Worksheets("Sheet2").Cells(iRow, 1)
        Else
            W_Vouchernr = "xxxxxxxxxx"
        End If

        'inkooporder
        If Worksheets("Sheet2").Cells(iRow, 2) <> "" Then
            W_Inkooporder = Worksheets("Sheet2").Cells(iRow, 2)
        Else
            W_Inkooporder = "xxxxxxxxxx"
        End If

        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(SAP_session) Then    
            Set SAP_session = Connection.Children(0)   
        End If

        If IsObject(Wscript) Then   
            Wscript.ConnectObject SAP_session, "on"
            Wscript.ConnectObject SAPGuiApp, "on"
        End If

        SAP_session.findById("wnd[0]").maximize
        SAP_session.findById("wnd[0]/tbar[0]/okcd").Text = "/nfb02"
        SAP_session.findById("wnd[0]").sendVKey 0
        SAP_session.findById("wnd[0]/usr/txtRF05L-BELNR").Text = W_Vouchernr
        SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").Text = companycode
        SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").SetFocus
        SAP_session.findById("wnd[0]/usr/ctxtRF05L-BUKRS").caretPosition = 4
        SAP_session.findById("wnd[0]").sendVKey 0 
        SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell").currentCellColumn
        SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell").currentCellColumn
        SAP_session.findById("wnd[0]/usr/cntlCTRL_CONTAINERBSEG/shellcont/shell").doubleClickCurrentCell
        SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").Text = W_Inkooporder           
        SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").SetFocus
        SAP_session.findById("wnd[0]/usr/ctxtBSEG-SGTXT").caretPosition = 14
        SAP_session.findById("wnd[0]").sendVKey 0
        SAP_session.findById("wnd[0]").sendVKey 11

        'Gets the message from the status bar and save it in column B
        Worksheets("Sheet2").Cells(iRow, 3) =SAP_session.findById("wnd[0]/sbar").Text
    Next iRow 'iterate one row down

End Sub

推荐阅读