Buddies – Excel-VBA and RFC_READ_TABLE

[Stefan-Schnell]

In many discussions with Excel-VBA programmers around the world I heard that they use the function module RFC_READ_TABLE. This function module (FM) is very important for them to get data from an SAP system. Very often respectively always they don’t have the possibility to use an individual copy with modifications of this FM. So they are dependent from this FM.

The fresh SAP GUI for Windows 7.50 contains the SAP NetWeaver RFC library version 7.49 PL 0. Now it isn’t longer necessary to download the library from the support portal explicit. Collect the files sapnwrfc.dll, libicudecnumber.dll and also the international components for unicode icudt50.dll, icuin50.dll and icuuc50.dll. This is the base to use COM Connector (CCo), an ActiveX wrapper around the functions of the SAP NetWeaver RFC library. With CCo you can easily use the SAP NetWeaver RFC library with Visual Basic for Applications (VBA). You can find more information here and download it here.

Here an Excel-VBA example how to use RFC_READ_TABLE with the table DD03L, which contains information about the fields of the transparent tables of an SAP system. With this program is it possible to get all information e.g. name, length etc. of the fields of a transparent table, to use it inside own VBA programs with the RFC_READ_TABLE FM. In the table parameter FIELDS you can set the fields you need. With the column LENG you can add the total length of the fields you need and you can see whether they exceed the maximum length of the DATA parameter with 512 characters.

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



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

    Option Explicit



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

    Const RFC_OK = 0



'-Sub GetTableInfo------------------------------------------------------

'-

'- Reads a few technical information about a transparent table from

'- data dictionary table DD03L from an SAP system via CCo

'-

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

  Sub GetTableInfo(TableName As String)



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

      #If Win64 Then

        Dim SAP As Object

      #Else

        Dim SAP As CCo.COMNWRFC

      #End If

      Dim hRFC As Long

      Dim hFuncDesc As Long

      Dim rc As Integer

      Dim hFunc As Long

      Dim hOptions As Long

      Dim hTable As Long

      Dim hFields As Long

      Dim hRow As Long

      Dim rowCount As Long

      Dim charBuffer As String

      Dim i As Long

      Dim Fields() As String

      Dim j As Long



    Set SAP = CreateObject("COMNWRFC")

    If Not IsObject(SAP) Then

      Exit Sub

    End If

      

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

      "CLIENT=001, USER=BCUSER")

    If hRFC = 0 Then

      Set SAP = Nothing

      Exit Sub

    End If

      

    hFuncDesc = SAP.RfcGetFunctionDesc(hRFC, "RFC_READ_TABLE")

    If hFuncDesc = 0 Then

      rc = SAP.RfcCloseConnection(hRFC)

      Set SAP = Nothing

      Exit Sub

    End If

      

    hFunc = SAP.RfcCreateFunction(hFuncDesc)

    If hFunc = 0 Then

      rc = SAP.RfcCloseConnection(hRFC)

      Set SAP = Nothing

      Exit Sub

    End If

              

    rc = SAP.RfcSetChars(hFunc, "QUERY_TABLE", "DD03L")

    rc = SAP.RfcSetChars(hFunc, "DELIMITER", "~")



    If SAP.RfcGetTable(hFunc, "FIELDS", hFields) = RFC_OK Then

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "TABNAME")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "FIELDNAME")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "POSITION")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "KEYFLAG")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "DATATYPE")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "LENG")

      hRow = SAP.RfcAppendNewRow(hFields)

      rc = SAP.RfcSetChars(hRow, "FIELDNAME", "DECIMALS")

    End If



    If SAP.RfcGetTable(hFunc, "OPTIONS", hOptions) = RFC_OK Then

      hRow = SAP.RfcAppendNewRow(hOptions)

      rc = SAP.RfcSetChars(hRow, "TEXT", "TABNAME = '" & TableName & "'")

    End If



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

      

      '-Get the column titels-------------------------------------------

      rc = SAP.RfcGetTable(hFunc, "FIELDS", hFields)

      If SAP.RfcGetRowCount(hFields, rowCount) = RFC_OK Then

        rc = SAP.RfcMoveToFirstRow(hFields)

        For i = 1 To rowCount

          hRow = SAP.RfcGetCurrentRow(hFields)

          rc = SAP.RfcGetChars(hRow, "FIELDNAME", charBuffer, 30)

          Cells(1, i) = Trim(charBuffer)

          If i < rowCount Then

            rc = SAP.RfcMoveToNextRow(hFields)

          End If

        Next

      End If

      

      '-Get the table data of data dictionary table DD03L---------------

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

      If SAP.RfcGetRowCount(hTable, rowCount) = RFC_OK Then

        rc = SAP.RfcMoveToFirstRow(hTable)

        For i = 1 To rowCount

          hRow = SAP.RfcGetCurrentRow(hTable)

          rc = SAP.RfcGetChars(hRow, "WA", charBuffer, 512)



          Fields = Split(charBuffer, "~")

          For j = 0 To UBound(Fields)

            Cells(i + 1, j + 1) = Trim(Fields(j))

          Next



          If i < rowCount Then

            rc = SAP.RfcMoveToNextRow(hTable)

          End If

        Next

      End If

    

    End If

              

    rc = SAP.RfcDestroyFunction(hFunc)

    rc = SAP.RfcCloseConnection(hRFC)

    Set SAP = Nothing



  End Sub



'-Sub Main--------------------------------------------------------------

  Sub Main()

  

    Dim TableName As String

    

    TableName = InputBox("Name of the transparent table")

    If TableName <> "" Then

      UsedRange.ClearContents

      GetTableInfo TableName

    End If

  

  End Sub



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

 

Here an example with the table SFLIGHT. All fields with its name are listed in the column FIELDNAME and the length in the column LENG.



 

This example shows how easy it is to load data from an SAP system into an Excel sheet via CCo. Also this example delivers a good way to detect information about a transparent table to use it in own VBA programs.