How to fetch table data from SAP using MS excel

How to fetch table data from SAP using MS excel

In this blog, i will give example of getting table entries from SAP using excel. 

The function is being called by passing R3 connection object. See my previous blog to see how to connect to SAP

Function Module Name : RFC_READ_TABLE

Input : 

              Query_table (string): Table you want to get data from

              Fields (table/array):  This RFC cannot get data more than 512 characters, so reduce / select the field that you want data from

              Options (table/array): can pass native "where" SQL statements upto 160 characters per line. You can add lines to create AND / OR statements

              

Output : 

              Data (table/array): Here is where the data is passed back from SAP. This can be seen as an ARRAY of string (512 char long). We have to use the fields to get the lenght for each filed and truncate / extract data.

Caution

1. This RFC can get data only upto 512 char

2. When getting transactional data, it may take too long. Not worth using Excel

Sub z_Read_RFC_TAB_Function(R3 As Object) 'Define function Dim myFunc As Object Set myFunc = R3.Add("RFC_READ_TABLE") Dim objQueryTab, objRowCount As Object Set objQueryTab = myFunc.exports("QUERY_TABLE") objQueryTab.Value = zTable Set objRowCount = myFunc.exports("ROWCOUNT") 'objRowCount.Value = 10 Dim zOptions, zFields, zDATA As Object Set zOptions = myFunc.tables("OPTIONS") Set zFields = myFunc.tables("FIELDS") Set zDATA = myFunc.tables("DATA") If myFunc.call = False Then MsgBox myFunc.Exception End If     Dim objDatRec As Object Dim objFldRec As Object For Each objDatRec In zDATA.Rows     For Each objFldRec In zFields.Rows         Sheets("TMP").Cells(objDatRec.Index + 1, objFldRec.Index) = Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))     Next Next End Sub

Where can we use this ? : Get status for batch jobs, idocs, TRFCs, fetch config tables etc....

Let me know if anyone finds this good