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.

Enjoy it.

Cheers
Stefan

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply