Buddies – Excel-VBA and RFC_READ_TABLE

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