How to use Batch Input with Excel via CCo

[stefan_schnell]

Batch Input is a consolidated method for automatic mass input of data. You can find more information about Batch Input here and here.

Here now an example how to use Batch Input with VBA in Excel and the COM Connector (CCo).

The sub procedure Record stores all activities in an Excel table, look at the image below. It calls the function module BDC_RECORD_TRANSACTION.

The sub procedure Replay reads the activities from the Excel table and executes it via the function module RFC_CALL_TRANSACTION_USING.

'-Begin-----------------------------------------------------------------

'-

'- Visual Basic for Application Excel program to use SAP Batch Input

'- resp. Batch Data Communication (BDC)

'-

'- Author: Stefan Schnell

'-

'-----------------------------------------------------------------------



  '-Directives----------------------------------------------------------

    Option Explicit



  '-Constants-----------------------------------------------------------

    Const RFC_OK = 0



  '-Sub Record----------------------------------------------------------

  '-

  '- Records the given transaction code and stores the activities in

  '- the given Excel table.

  '-

  '---------------------------------------------------------------------

    Sub Record(TCode As String, TableName As String)



      '-Variables-------------------------------------------------------

        Dim SAP As CCo.COMNWRFC

        Dim hRFC As Long

        Dim hFuncDesc As Long

        Dim hFunc As Long

        Dim rc As Integer

        Dim hTable As Long

        Dim RowCount As Long

        Dim i As Integer

        Dim hRow As Long

        Dim charBuffer As String



      Set SAP = CreateObject("COMNWRFC")

      If IsObject(SAP) Then



        hRFC = SAP.RfcOpenConnection("ASHOST=NSP, SYSNR=00, " & _

          "CLIENT=001, USER=BCUSER, USE_SAPGUI=2")



        If hRFC Then



          hFuncDesc = SAP.RfcGetFunctionDesc(hRFC, _

            "BDC_RECORD_TRANSACTION")

          If hFuncDesc Then



            hFunc = SAP.RfcCreateFunction(hFuncDesc)

            If hFunc Then



              rc = SAP.RfcSetChars(hFunc, "TCODE", TCode)

              rc = SAP.RfcSetChars(hFunc, "MODE", "A")

              rc = SAP.RfcSetChars(hFunc, "UPDATE", "A")

              rc = SAP.RfcSetChars(hFunc, "AUTHORITY_CHECK", "X")



              If SAP.RfcInvoke(hRFC, hFunc) = RFC_OK Then



                rc = SAP.RfcGetTable(hFunc, "DYNPROTAB", hTable)

                rc = SAP.RfcGetRowCount(hTable, RowCount)

                rc = SAP.RfcMoveToFirstRow(hTable)



                Worksheets(TableName).Cells.Clear

                For i = 1 To RowCount

                  hRow = SAP.RfcGetCurrentRow(hTable)

                  rc = SAP.RfcGetChars(hRow, "PROGRAM", charBuffer, 40)

                  Worksheets(TableName).Range("A" & CStr(i)) = _

                    Trim(charBuffer)

                  rc = SAP.RfcGetChars(hRow, "DYNPRO", charBuffer, 4)

                  Worksheets(TableName).Range("B" & CStr(i)) = _

                    "'" & Trim(charBuffer)

                  rc = SAP.RfcGetChars(hRow, "DYNBEGIN", charBuffer, 1)

                  Worksheets(TableName).Range("C" & CStr(i)) = _

                    Trim(charBuffer)

                  rc = SAP.RfcGetChars(hRow, "FNAM", charBuffer, 132)

                  Worksheets(TableName).Range("D" & CStr(i)) = _

                    Trim(charBuffer)

                  rc = SAP.RfcGetChars(hRow, "FVAL", charBuffer, 132)

                  Worksheets(TableName).Range("E" & CStr(i)) = _

                    "'" & Trim(charBuffer)

                  If i < RowCount Then

                    rc = SAP.RfcMoveToNextRow(hTable)

                  End If

                Next



              End If



              rc = SAP.RfcDestroyFunction(hFunc)

            End If



          End If



          rc = SAP.RfcCloseConnection(hRFC)

        End If



        Set SAP = Nothing

      End If



    End Sub



  '-Sub Replay----------------------------------------------------------

  '-

  '- Replays the given transaction code with the activities from the

  '- given Excel table, in the specified mode.

  '-

  '- Modes: A = All screens are displayed

  '-        E = Screens are not displayed, except if an error occurs

  '-        N = Screens are not displayed

  '-        P = Same as N, but if a breakpoint is reached the classic

  '-            debugger is used

  '-

  '---------------------------------------------------------------------

    Sub Replay(TCode As String, TableName As String, Mode As String)



      '-Variables-------------------------------------------------------

        Dim SAP As CCo.COMNWRFC

        Dim hRFC As Long

        Dim hFuncDesc As Long

        Dim hFunc As Long

        Dim rc As Integer

        Dim hTable As Long

        Dim i As Integer

        Dim hRow As Long



      Set SAP = CreateObject("COMNWRFC")

      If IsObject(SAP) Then



        hRFC = SAP.RfcOpenConnection("ASHOST=NSP, SYSNR=00, " & _

          "CLIENT=001, USER=BCUSER, USE_SAPGUI=2")

        If hRFC Then



          hFuncDesc = SAP.RfcGetFunctionDesc(hRFC, _

            "RFC_CALL_TRANSACTION_USING")

          If hFuncDesc Then



            hFunc = SAP.RfcCreateFunction(hFuncDesc)

            If hFunc Then



              rc = SAP.RfcSetChars(hFunc, "TCODE", TCode)

              rc = SAP.RfcSetChars(hFunc, "MODE", Mode)

              rc = SAP.RfcGetTable(hFunc, "BT_DATA", hTable)

              rc = SAP.RfcDeleteAllRows(hTable)



              For i = 1 To Worksheets(TableName).UsedRange.Rows.Count

                hRow = SAP.RfcAppendNewRow(hTable)

                rc = SAP.RfcSetChars(hRow, "PROGRAM", _

                  Worksheets(TableName).Range("A" & CStr(i)))

                rc = SAP.RfcSetChars(hRow, "DYNPRO", _

                  Worksheets(TableName).Range("B" & CStr(i)))

                rc = SAP.RfcSetChars(hRow, "DYNBEGIN", _

                  Worksheets(TableName).Range("C" & CStr(i)))

                rc = SAP.RfcSetChars(hRow, "FNAM", _

                  Worksheets(TableName).Range("D" & CStr(i)))

                rc = SAP.RfcSetChars(hRow, "FVAL", _

                  Worksheets(TableName).Range("E" & CStr(i)))

              Next



              rc = SAP.RfcInvoke(hRFC, hFunc)



              rc = SAP.RfcDestroyFunction(hFunc)

            End If



          End If



          rc = SAP.RfcCloseConnection(hRFC)

        End If



        Set SAP = Nothing

      End If



    End Sub



  '-Main----------------------------------------------------------------

    Sub Main()

 

      Record "SE16", "Tabelle1"



      Replay "SE16", "Tabelle1", "A"



    End Sub



'-End-------------------------------------------------------------------

After the recording the table looks like this:



It is exactly the same as in the Transaction Recorder SHDB:



But now you have the possibility to modify the table easily, e.g. to duplicate entries - Excel-like.