Technical Articles

Stefan Schnell

February 3, 2016 3 minute read

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:

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.

38 Comments

You must be Logged on to comment or reply to a post.

Ziv Markovich

February 3, 2016 at 6:27 pm

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

Stefan Schnell

Blog Post Author

February 4, 2016 at 7:35 am

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

Former Member

February 9, 2016 at 3:22 pm

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

Stefan Schnell

Blog Post Author

February 9, 2016 at 3:56 pm

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

Former Member

February 9, 2016 at 4:25 pm

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

Stefan Schnell

Blog Post Author

February 9, 2016 at 8:19 pm

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

Former Member

February 10, 2016 at 1:21 am

Thank You Stepan. Let me try this.

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

Holger Kohn

February 10, 2016 at 6:52 am

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

Former Member

February 10, 2016 at 8:28 am

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.

Stefan Schnell

Blog Post Author

February 10, 2016 at 8:34 am

Hello Balasubramanian V,

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

Cheers

Stefan

Former Member

February 10, 2016 at 8:53 am

Hi Stefan,

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

Stefan Schnell

Blog Post Author

February 10, 2016 at 9:20 am

Hello Balasubramanian V,

sure, exactly like in the example.

Cheers

Stefan

Former Member

February 11, 2016 at 12:14 pm

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.

Holger Kohn

February 11, 2016 at 3:16 pm

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

Former Member

February 11, 2016 at 6:07 pm

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.

Stefan Schnell

Blog Post Author

February 12, 2016 at 7:05 am

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

and I got the correct content in the fields

My suggestion to solve your requirement is to use CCo.

Cheers

Stefan

Holger Kohn

February 12, 2016 at 7:21 am

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.

Stefan Schnell

Blog Post Author

February 12, 2016 at 6:53 am

Hello Holger,

thanks for your support

🙂

Cheers

Stefan

Former Member

February 12, 2016 at 9:07 am

Hi Stefan, Appreciate your help.

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)

Stefan Schnell

Blog Post Author

February 12, 2016 at 9:20 am

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

Former Member

February 12, 2016 at 10:02 am

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")

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

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

Stefan Schnell

Blog Post Author

February 12, 2016 at 10:54 am

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

Former Member

February 12, 2016 at 12:19 pm

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

Holger Kohn

February 12, 2016 at 12:37 pm

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.

Former Member

February 12, 2016 at 12:45 pm

Thank you Kohn and Stefan 🙂

Former Member

February 18, 2016 at 7:10 am

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

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

What is wrong with my call statement.

Former Member

February 17, 2016 at 5:22 pm

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

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

What is wrong with my call statement.

Stefan Schnell

Blog Post Author

February 19, 2016 at 8:12 am

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

Former Member

February 23, 2016 at 9:32 am

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.

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

Stefan Schnell

Blog Post Author

February 23, 2016 at 10:51 am

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

Former Member

February 24, 2016 at 9:42 am

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

Stefan Schnell

Blog Post Author

February 24, 2016 at 10:29 am

Hello Balasubramanian V,

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

Cheers

Stefan

Ziv Markovich

July 29, 2016 at 6:15 am

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

Stefan Schnell

Blog Post Author

July 29, 2016 at 8:25 am

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

Ricardo Monteiro

February 11, 2020 at 1:37 pm

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

Stefan Schnell

Blog Post Author

February 11, 2020 at 4:05 pm

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

Prakash Bhagat

May 17, 2020 at 6:06 am

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

Stefan Schnell

May 18, 2020 at 5:57 am

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

Technical Articles

How to use Batch Input with Excel via CCo

Assigned Tags