Skip to Content
Technical Articles
Author's profile photo Stefan Schnell

How to use Batch Input with Excel via CCo

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:

BdcRecorder001.jpg

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

BdcRecorder002.jpg

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

Assigned Tags

      38 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ziv Markovich
      Ziv Markovich

      Hi Stefan ,

      Thanks alot for this these CCo exemples   , dose function CAT_TCD_CAL should be supported also with CCo  ?  since via Classic RFC SDK it dose not works from VBA  (see this thread )

      Regards,

      Ziv

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Ziv,

      I don't see any reasons why not.

      The package SCAT (Computer Aided Test tools) offers many interesting RFC-enabled function modules. CAT_TCD_CAL (CATT - CALL TRANSACTION via RFC) is one of them.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      I have a requirement to create a vba code for Not a batch input session. Currently I am using ABAP4_CALL_TRANSACTION FM to execute/replay. Could you please help me on how to add CTU_PARAMS structure in ABAP4_CALL_TRANSACTION in VBA.

      Your help is much appreciated.

      Regards,

      Balasubramanian V

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      welcome in the Scripting Language forum.

      Could you please explain your requirement more in detail. I can't find in the interface of the function module ABAP4_CALL_TRANSACTION a parameter with the type CTU_PARAMS.

      Here the interface:

      FUNCTION ABAP4_CALL_TRANSACTION.
      *"----------------------------------------------------------------------
      *"*"Local Interface:
      *"  IMPORTING
      *"     VALUE(TCODE) LIKE  SY-TCODE
      *"     VALUE(SKIP_SCREEN) LIKE  SY-FTYPE DEFAULT SPACE
      *"     VALUE(MODE_VAL) LIKE  SY-FTYPE DEFAULT 'A'
      *"     VALUE(UPDATE_VAL) LIKE  SY-FTYPE DEFAULT 'A'
      *"  EXPORTING
      *"     VALUE(SUBRC) LIKE  SY-SUBRC
      *"  TABLES
      *"      USING_TAB STRUCTURE  BDCDATA OPTIONAL
      *"      SPAGPA_TAB STRUCTURE  RFC_SPAGPA OPTIONAL
      *"      MESS_TAB STRUCTURE  BDCMSGCOLL OPTIONAL
      *"  EXCEPTIONS
      *"      CALL_TRANSACTION_DENIED
      *"      TCODE_INVALID
      *"----------------------------------------------------------------------

      The structure CTU_PARAMS is necessary if you use the command CALL TRANSACTION with the addition OPTIONS FROM, but the function module offers no possibility to use this.

        IF SKIP_SCREEN EQ SPACE AND L_USING_TAB_LINES EQ 0.
          CALL TRANSACTION TCODE.
        ELSEIF SKIP_SCREEN NE SPACE.
          CALL TRANSACTION TCODE AND SKIP FIRST SCREEN.
        ELSE.
          CALL TRANSACTION TCODE
            USING USING_TAB
            MODE MODE_VAL
            UPDATE UPDATE_VAL
            MESSAGES INTO MESS_TAB.
          SUBRC = SY-SUBRC.
        ENDIF.
      

      The function module /ISDFPS/CALL_TRANSACTION offers the possibility.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      Yes you are right. CTU_PARAMS not parameter of ABAP4_CALL_TRANSACTION FM.

      We would like to check few fields in CTU_PARAMS structure such as DEFSIZE = 'X', NOBINPT = 'X'

      DEFSIZE - Default screen size

      NOBINPT - Not a Batch Input sesstion


      existing script:


          Set sapconn= CreateObject("SAP.Functions")

          sapconn.Connection.RfcWithDialog = True

          Set sapfunc = sapconn.Add("ABAP4_CALL_TRANSACTION")

          sapfunc.Exports("MODE_VAL") = "A" ''No display

          sapfunc.Exports("UPDATE_VAL") = "A" ''Asynchronous update

          Set BdcTable = sapfunc.Tables("USING_TAB")

           sapfunc.call


      Can you help me how to call ABAP dictionary objects/Structures in VBA? or how to create a work area in VBA.



      Regards,

      Balasubramanian V



      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      to use CTU_PARAMS it is necessary to modify ABAP4_CALL_TRANSACTION. Copy it to your own namespace, in my case a local object in $TMP, and do the following changes (marked bold):

      FUNCTION ZABAP4_CALL_TRANSACTION.
      *"-------------------------------------------------------
      *"*"Local Interface:
      *"  IMPORTING
      *"     VALUE(TCODE) TYPE  SY-TCODE
      *"     VALUE(SKIP_SCREEN) TYPE  SY-FTYPE DEFAULT SPACE
      *"     VALUE(MODE_VAL) TYPE  SY-FTYPE DEFAULT 'A'
      *"     VALUE(UPDATE_VAL) LIKE  SY-FTYPE DEFAULT 'A'
      *"    VALUE(OPT_VAL) LIKE  CTU_PARAMS
      *"      STRUCTURE  CTU_PARAMS OPTIONAL

      *"  EXPORTING *"     VALUE(SUBRC) TYPE  SY-SUBRC *"  TABLES *"      USING_TAB STRUCTURE  BDCDATA OPTIONAL *"      SPAGPA_TAB STRUCTURE  RFC_SPAGPA OPTIONAL *"      MESS_TAB STRUCTURE  BDCMSGCOLL OPTIONAL *"  EXCEPTIONS *"      CALL_TRANSACTION_DENIED *"      TCODE_INVALID *"-------------------------------------------------------   TABLES: TSTC.   DATA:   L_USING_TAB_LINES LIKE SY-TABIX.   TRANSLATE TCODE TO UPPER CASE.  "#EC SYNTCHAR   SELECT SINGLE * FROM TSTC WHERE TCODE = TCODE.   IF SY-SUBRC <> 0 OR TSTC-PGMNA(4) = 'MENU'.     RAISE TCODE_INVALID.   ENDIF.   CALL FUNCTION 'AUTHORITY_CHECK_TCODE'        EXPORTING             TCODE  = TCODE        EXCEPTIONS             OK     = 1             NOT_OK = 2             OTHERS = 3. *   IF SY-SUBRC <> 1.     RAISE CALL_TRANSACTION_DENIED.   ENDIF. *   DESCRIBE TABLE USING_TAB LINES L_USING_TAB_LINES. *   IF SKIP_SCREEN NE SPACE AND L_USING_TAB_LINES > 0.     RAISE CALL_TRANSACTION_DENIED.   ENDIF. *   LOOP AT SPAGPA_TAB.     SET PARAMETER ID SPAGPA_TAB-PARID FIELD       SPAGPA_TAB-PARVAL.   ENDLOOP. *   IF SKIP_SCREEN EQ SPACE AND L_USING_TAB_LINES EQ 0.     CALL TRANSACTION TCODE.   ELSEIF SKIP_SCREEN NE SPACE.     CALL TRANSACTION TCODE AND SKIP FIRST SCREEN.   ELSEIF OPT_VAL IS NOT INITIAL.
          CALL TRANSACTION TCODE
            USING USING_TAB
            OPTIONS FROM OPT_VAL
            MESSAGES INTO MESS_TAB.

        ELSE.     CALL TRANSACTION TCODE USING USING_TAB       MODE MODE_VAL       UPDATE UPDATE_VAL       MESSAGES INTO MESS_TAB.     SUBRC = SY-SUBRC.   ENDIF. * ENDFUNCTION.

      Add in the import parameters OPT_VAL from the type STRUCTURE CTU_PARAMS and in the CALL TRANSACTION block another option to call a transaction with OPTIONS addition. Mark the function module as RFC-enabled, activate it and now you can use it with VBA via CCo:

      '-Begin--------------------------------------------------
       
        '-Directives-------------------------------------------
          Option Explicit
      
        '-Constants--------------------------------------------
          Const RFC_OK = 0
      
        '-Sub Replay-------------------------------------------
          Sub Replay(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 i As Integer
              Dim hRow As Long
              Dim hStruct As Long
      
            Set SAP = CreateObject("COMNWRFC")
            If IsObject(SAP) Then
      
              hRFC = SAP.RFCOPENCONNECTION("ASHOST=ABAP, " & _
                "SYSNR=00, CLIENT=001, USER=BCUSER, USE_SAPGUI=2")
              If hRFC Then
      
                hFuncDesc = SAP.RFCGETFUNCTIONDESC(hRFC, _
                  "ZABAP4_CALL_TRANSACTION")
                If hFuncDesc Then
      
                  hFunc = SAP.RFCCREATEFUNCTION(hFuncDesc)
                  If hFunc Then
      
                    rc = SAP.RFCSETCHARS(hFunc, "TCODE", TCode)
                    rc = SAP.RFCGETSTRUCTURE(hFunc, "OPT_VAL", hStruct)
                    rc = SAP.RFCSETCHARS(hStruct, "UPDMODE", "A")
                    rc = SAP.RFCSETCHARS(hStruct, "DISMODE", "A")

                    rc = SAP.RFCGETTABLE(hFunc, "USING_TAB", hTable)               rc = SAP.RFCDELETEALLROWS(hTable)               For i = 1 To Worksheets("Tabelle1").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()       Replay "SE16", "Tabelle1"     End Sub '-End----------------------------------------------------

      You can get the structure with RfcGetStructure and set the elements of the structure with RfcSetChar.

      Hope it is possible for you to use COM Connector and not the old fashion ActiveX libraries.

      Enjoy it.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Thank You Stepan. Let me try this.

      Do we have any other FMs Similar to ABAP4_CALL_TRANSACTION with CTU_PARAMS by default.

      Author's profile photo Holger Köhn
      Holger Köhn

      /ISDFPS/CALL_TRANSACTION is an RFC-enabled FM. May you can check this one.

      Author's profile photo Former Member
      Former Member

      Hi Thanks, But BDCDATA table is missing in this FM, but i can see it in Import parameter. I am just eager to know how to use import parameter as a table in this case.

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      the function module /ISDFPS/CALL_TRANSACTION contains  BDCDATA_TAB in the import parameters, not in the tables.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      Can we use this import parameter as a table here to replay.

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      sure, exactly like in the example.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      Can you help me more in detail. In FM /ISDFPS/CALL_TRANSACTION contains  BDCDATA_TAB as STRUCTURE but not the TABLE, how can i store all the below column headers

      PROGRAM

      DYNPRO

      DYNBEGIN

      FNAM

      FVAL

      in a table as in ABAP4_CALL_TRANSACTION FM.

      Author's profile photo Holger Köhn
      Holger Köhn

      When you check FM in SE37 you can see that this structure can Setup as structure table (add line)

      So you Need a variable in VBA for this structure table

      Dim structbl_IT_BDCDATA

      Then set variable struc_IT_BDCDATA

      Set structbl_IT_BDCDATA = sapfunc.exports("IT_BDCDATA")

      Now you Need a Loop over your BCDDATA Excel range

      Column 1 = PROGRAM

      Column 2 = DYNPRO

      Column 3 = DYNBEGIN

      Column 4 = FNAM

      Column 5 = FVAL

      If you have headers row Loop should start on second row up to last row in used range.

      For lngRow = 2 to LastUsedRow

          strPROGRAM = Cells(lngRow, 1).Value

          strDYNPRO = Cells(lngRow, 2).Value

          strDYNBEGIN = Cells(lngRow, 3).Value

          strFNAM = Cells(lngRow, 4).Value

          strFVAL = Cells(lngRow, 5).Value

          structbl_IT_BDCDATA.Rows.Add
      structbl_IT_BDCDATA.Value((lngRow - 1), "PROGRAM") = strPROGRAM

              structbl_IT_BDCDATA.Value((lngRow - 1), "DYNPRO") = strDYNPRO

              structbl_IT_BDCDATA.Value((lngRow - 1), "DYNBEGIN") = strDYNBEGIN

              structbl_IT_BDCDATA.Value((lngRow - 1), "FNAM") = strFNAM

              structbl_IT_BDCDATA.Value((lngRow - 1), "FVAL") = strFVAL

      Next

      I have not test it. But use a similar code for another structure table in Imports. So I guess this will help you.

      Br, Holger

      Author's profile photo Former Member
      Former Member

      Hi Kohn, It is not working i am getting error at structbl_IT_BDCDATA.Row.Count

      The below is my script

      Set sapfunc = sapconn.Add("/ISDFPS/CALL_TRANSACTION")

          sapfunc.Exports("IS_OPTIONS").Value("NOBINPT") = "X"

          sapfunc.Exports("IS_OPTIONS").Value("DISMODE") = "N"

          sapfunc.Exports("IS_OPTIONS").Value("UPDMODE") = "A"

          Set structbl_IT_BDCDATA = sapfunc.Exports("IT_BDCDATA")

         

              structbl_IT_BDCDATA.Row.Count

              structbl_IT_BDCDATA.Value(1, "PROGRAM") = strPROGRAM

              structbl_IT_BDCDATA.Value(1, "DYNPRO") = strDYNPRO

              structbl_IT_BDCDATA.Value(1, "DYNBEGIN") = strDYNBEGIN

              structbl_IT_BDCDATA.Value(1, "FNAM") = strFNAM

              structbl_IT_BDCDATA.Value(1, "FVAL") = strFVAL

      I want to check CTU_PARAMS in replay coding. /ISDFPS/CALL_TRANSACTION looks interesting but i am strucked here on how to use structure as a table.

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      I don't use ActiveX libraries anymore - you can find one reason here. This seems to be your problem.

      Here a solution in VBA with COM Connector.

      I use my own function module but with the same signature as /ISDFPS/CALL_TRANSACTION.

      FUNCTION z_call_transaction.
      *"----------------------------------------------------------------------
      *"*"Lokale Schnittstelle:
      *"  IMPORTING
      *"     VALUE(IF_TCODE) TYPE  TCODE
      *"     VALUE(IF_SKIP_FIRST_SCREEN) TYPE  FLAGE DEFAULT SPACE
      *"     VALUE(IT_BDCDATA) TYPE  BDCDATA_TAB OPTIONAL
      *"     VALUE(IS_OPTIONS) TYPE  CTU_PARAMS OPTIONAL
      *"  EXPORTING
      *"     VALUE(ET_MSG) TYPE  ETTCD_MSG_TABTYPE
      *"  TABLES
      *"      CT_SETGET_PARAMETER STRUCTURE  RFC_SPAGPA OPTIONAL
      *"  EXCEPTIONS
      *"      IMPORT_PARA_ERROR
      *"      TCODE_ERROR
      *"      AUTH_ERROR
      *"      TRANS_ERROR
      *"----------------------------------------------------------------------
      
        BREAK-POINT.
      
      ENDFUNCTION.
      

      Here the VBA program:

      Option Explicit
      
        '-Constants-----------------------------------------------------------
          Const RFC_OK = 0
      
      
      '-Sub TestCallTransaction-----------------------------------------------
        Sub TestCallTransaction()
       
          '-Variables---------------------------------------------------------
            Dim SAP As CCo.COMNWRFC
            Dim hRFC As Long
            Dim rc As Integer
            Dim hFunc As Long
            Dim hFuncDesc As Long
            Dim hTable As Long
            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, ABAP_DEBUG=1")
            If hRFC Then
             
              hFuncDesc = SAP.RFCGETFUNCTIONDESC(hRFC, "Z_CALL_TRANSACTION")
              If hFuncDesc Then
                hFunc = SAP.RFCCREATEFUNCTION(hFuncDesc)
                If hFunc Then
                  rc = SAP.RFCGETTABLE(hFunc, "IT_BDCDATA", hTable)
                  hRow = SAP.RFCAPPENDNEWROW(hTable)
                  rc = SAP.RFCSETCHARS(hRow, "PROGRAM", "TestProgram")
                  rc = SAP.RFCSETCHARS(hRow, "DYNPRO", "0100")
                  rc = SAP.RFCSETCHARS(hRow, "FNAM", "Bambi")
                  rc = SAP.RFCSETCHARS(hRow, "FVAL", "Rendeer")
                 
                  If SAP.RFCINVOKE(hRFC, hFunc) = RFC_OK Then
                    'BREAK-POINT in function module
                  End If
                  rc = SAP.RFCDESTROYFUNCTION(hFunc)
                End If
              End If
             
              rc = SAP.RFCCLOSECONNECTION(hRFC)
            End If
            Set SAP = Nothing
       
          End If
       
        End Sub
      

      As Holger described, set the RFCSetChars in a loop an fill the rows with your content.

      If I start the VBA program the ABAP debugger opens

      /wp-content/uploads/2016/02/001_886231.jpg

      and I got the correct content in the fields

      /wp-content/uploads/2016/02/002_886301.jpg

      My suggestion to solve your requirement is to use CCo.

      Cheers

      Stefan

      Author's profile photo Holger Köhn
      Holger Köhn

      Ok. Have done a test on it right now. Seems like using table structure is not supported via SAP Functions for IMPORT structures.

      One Option is to use CCOM as Stefan suggested.

      Another Option is an customized functionmodule where function module Interface have BDCDATA as table.

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Holger,

      thanks for your support

      🙂

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan, Appreciate your help. a.JPG

      I tried to use COMNWRFC but i am encountering Activex component can't create object. Could you please assist me what DLL file should i add as reference in excel workbook

      I downloaded the Cco.Zip from CCo (COM Connector) for SAP NetWeaver RFC , but when i tried to load the DLL in Workbook i am getting (Using MS 2013 workbook)Error.JPG

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      add CCo.dll as reference.

      Download also from the SAP Service Market Place the SAP NetWeaver RFC SDK, here you find the necessary DLLs.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan, Thanks for patience and replying to my query. I am beginner to this Cco.

      I am getting the below error while executing the below line.

      Set SAP = CreateObject("COMNWRFC")

      Cco1.JPG

      hRFC = SAP.RFCOPENCONNECTION("ASHOST=sapnt3-inc.sapnet.hp.com, SYSNR=00, " & _

              "CLIENT=800, USER=20225958, USE_SAPGUI=2, ABAP_DEBUG=1")

      CCo .JPG

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balsubramanian V,

      CCo is a 32-bit COM library, it needs also SAP RFC SDK libraries 32-bit for Windows.

      CCo needs the following 32-bit SAP RFC SDK libraries in the same directory as itself:

      • icudt34.dll
      • icuin34.dll
      • icuuc34.dll
      • libicudecnumber.dll
      • libsapucum.dll
      • sapnwrfc.dll

      You can find information how to differentiate between x86 and x64 version here.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan, Could you please guide me where can i download this. I tried it in https://websmp105.sap-ag.de/swdc.

      It is asking me SAP credentials. It would be great if you could share with me the .dll.

      Since i am working on high priority (SAP automation in Excel). For Batch input session it works perfect. But i am facing issue with not a batch input mode.

      Please help me to provide or guide me on the above dll file

      Author's profile photo Holger Köhn
      Holger Köhn

      Hello.

      You Need to contact your local SAP Basis Administrator. They should be able to provide required files.

      If you have a Need for an fast solution I still recommend you request a customized RFC-enabled functionmodule from your development Team which fulfil all your requirements. This can be used in Excel VBA.

      Author's profile photo Former Member
      Former Member

      Thank you Kohn and Stefan 🙂

      Author's profile photo Former Member
      Former Member

      Hi Stefen, Good day to you.

      Can you help me how to write a call statement in VBA. I need vba code for something below.

      Call the xFunc remote function:

      R3.xFunc IP:= 1, SEP:= objStruct, EP:= nVar, TP:=objTable

      I tried above syntax for ABAP4_CALL_TRANSACTION FM, but my excel workbook was not responded and restarted automatically. The below is my code

      Set Func = CreateObject("SAP.Functions")

      Set sapfun = Func.Add("ABAP4_CALL_TRANSACTION")

      If IsObject(Func) Then

          Set Conn = Func.Connection  

          Conn.RfcWithDialog = True

          If Not Conn.Logon(0, False) Then

            Debug.Print "Connection not successful"

          Else

            Debug.Print "Connection successful"

          End If

      End If

      result_x = Func.sapfun(TCODE:="VA42", MODE_VAL:="A", UPDATE_VAL:="A", USING_TAB:=bdctable)

      What is wrong with my call statement.

      Author's profile photo Former Member
      Former Member

      Hi Stefen, Good day to you.

      Can you help me how to write a call statement in VBA. I need vba code for something below.

      Call the xFunc remote function:

      R3.xFunc IP:= 1, SEP:= objStruct, EP:= nVar, TP:=objTable

      I tried above syntax for ABAP4_CALL_TRANSACTION FM, but my excel workbook was not responded and restarted automatically. The below is my code

      Set Func = CreateObject("SAP.Functions")

      Set sapfun = Func.Add("ABAP4_CALL_TRANSACTION")

      If IsObject(Func) Then

          Set Conn = Func.Connection   

          Conn.RfcWithDialog = True

          If Not Conn.Logon(0, False) Then

            Debug.Print "Connection not successful"

          Else

            Debug.Print "Connection successful"

          End If

      End If

      result_x = Func.sapfun(TCODE:="VA42", MODE_VAL:="A", UPDATE_VAL:="A", USING_TAB:=bdctable)

      What is wrong with my call statement.

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      here an approach how to call ABAP4_CALL_TRANSACTION:

      Option Explicit
      
      Sub Test()
      
        Dim Func As SAPFunctionsOCX.SAPFunctions
        Dim Conn As SAPLogonCtrl.Connection
        Dim sapfun As SAPFunctionsOCX.Function
        Dim BDCTable As SAPTableFactoryCtrl.Table
      
        Set Func = CreateObject("SAP.Functions")
        If IsObject(Func) Then
          Set Conn = Func.Connection
          Conn.RfcWithDialog = True
          If Not Conn.Logon(0, False) Then
            Debug.Print "Connection not successful"
          Else
            Debug.Print "Connection successful"
            Set sapfun = Func.Add("ABAP4_CALL_TRANSACTION")
            sapfun.Exports("TCODE") = "VA42"
            Set BDCTable = sapfun.Tables("USING_TAB")
            'Add your rows here, look the example from Holger above
            If Not sapfun.Call Then
              Debug.Print "Call not successful"
            Else
              Debug.Print "Call successful"
            End If
            Conn.Logoff
          End If
          Set Func = Nothing
        End If
      
      End Sub
      

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      Thanks for your response. In my organization, we are restricted to use CCO. Since we are part of internal automation team. So we have to use Activex method only, but we are still struggling to find the FM with both CTU_PARAMS AND USING_TAB for execution.

      Can you assist me on the below queries.

      Could you add the import parameter "CTU_PARAMS" to ABAP4_CALL_TRANSACTION from other FMs in runtime.

      or

      Could we add external table "USING_TAB" from other FMs to /ISDFPS/CALL_TRANSACTION, since in /ISDFPS/CALL_TRANSACTION CTU_PARAMS is there but not the USING_TAB.

      or only solution is to create a new FM with both CTU_PARAMS AND USING_TAB

      Regards,

      Balasubramanian V

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      as I said above, the ActiveX interface has some restrictions. Also the support for the classic RFC library ends next month and the ActiveX interface bases on the classic RFC library - you can find more information here. In my eyes it is not a good idea to build software now which uses the ActiveX interface.

      I don't know a way to manipulate FM calls at runtime and believe me this is not the correct way. The last option is to code your own RFC-enabled FM, as you said.

      Cheers

      Stefan

      Author's profile photo Former Member
      Former Member

      Hi Stefan,

      Thanks for your input. We are working on to use CCO object to call SAP objects from external systems. We are trying to install the below DLLs.

      • icudt34.dll
      • icuin34.dll
      • icuuc34.dll
      • libicudecnumber.dll
      • libsapucum.dll
      • sapnwrfc.dll

      We are working with SAP internal IT team to get all these DLLs. We need your assistance here, Is this enough to get the DLLs alone and store it into the CCO directory (Here it is "C:\Dummy\") at the client side.

      Regards,

      Balasubramanian V

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Balasubramanian V,

      yes, that is all you need. But be sure that you get the x86 version of the libraries.

      Cheers

      Stefan

      Author's profile photo Ziv Markovich
      Ziv Markovich

      Hi Stefan,


      Thanks for the Exemple it is very helpfull.

      When we run multi excel lines for the purpose of massive load , not just for one record  in a loop.

      In order to reduce run time  ,is it possible and how with CCO to check before reconnect if the connection is still open like it is possible with Classic RFC library

          If msapConn.Connection.IsConnected = 1 Then

                   MsgBox("Already logged on to " )

          End If

      Regards,

      Ziv

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Hello Ziv,

      sure is it possible, try this:

      '-Begin-----------------------------------------------------------------
      
        '-Directives----------------------------------------------------------
          Option Explicit
      
        '-Sub Main------------------------------------------------------------
          Sub Main()
      
            '-Variables-------------------------------------------------------
              Dim SAP, hRFC, rc, IsValid
      
            Set SAP = CreateObject("COMNWRFC")
            If Not IsObject(SAP) Then
              Exit Sub
            End If
      
            hRFC = SAP.RFCOPENCONNECTION("ASHOST=ABAP, SYSNR=00, " & _
              "CLIENT=001, USER=BCUSER")
      
            rc = SAP.RFCISCONNECTIONHANDLEVALID(hRFC, IsValid)
            MsgBox CStr(IsValid)
      
            rc = SAP.RFCCLOSECONNECTION(hRFC)
      
            rc = SAP.RFCISCONNECTIONHANDLEVALID(hRFC, IsValid)
            MsgBox CStr(IsValid)
      
            Set SAP = Nothing
      
          End Sub
      
        '-Main----------------------------------------------------------------
          Main
      
      '-End-------------------------------------------------------------------

      Cheers

      Stefan

      Author's profile photo Ricardo Monteiro
      Ricardo Monteiro

      Hi Stefan,

       

      Great help with this blog..

      But now, can you help me with something ?

      I'm calling a RFC for executing a batch input in DEV system, but at some point the BI needs to connect to the TST System (it appears a popup for connecting) but in the recording I don't have that.  Do you know of a way of doing this, if it is even possible ?

       

      Thank you,

      Ricardo Monteiro

      Author's profile photo Stefan Schnell
      Stefan Schnell
      Blog Post Author

      Ricardo Monteiro

      Hello Ricardo,

      this should be possible. I assume you can automate this native dialog e.g. with AutoItX. It is part of the AutoIt full installation.

      Best regards
      Stefan

      Author's profile photo Prakash Bhagat
      Prakash Bhagat

      Hi Stefan,

       

      I am trying to setup connection with SAP using vb in excel.

      But getting ActiveX Component can't create object error.

      I have CCo.dll file already uploaded but still not sure reason of this error.

      Please help.

       

      Thanks.

      Regards,

      Prakash

      Author's profile photo Stefan Schnell
      Stefan Schnell

      Prakash Bhagat

       

      Hello Prakash,
      CCo needs the SAP RFC library. You can use CCo if you have an SAP GUI for Windows > 7.50 PL 6 installed or if you have an SAPNWRFC SDK. Then you have to register CCo, best way is to register it via regsvr32. If these requirements are met, the error should no longer occur.
      Let us know your results.
      Best regards
      Stefan