Reading SAP tables with Excel

Reading SAP tables from Excel is very easy with Rfc Connector, and this example can be used as a handy tool for all kind of data extraction tasks.

Note: To be able to execute this example, you need to allow execution of unsigned macros in Excel. The exact procedure depends on your Office version and your security settings. Instructions on how to do this can be found in our Knowledge Base.

Example source

 ' get a table reader (table name taken from cell $E$5)
 Dim tr As TableReader
 Set tr = s.GetTableReader(Cells(5, 5))
 
 ' clear the result area
 ActiveSheet.UsedRange.Offset(14, 0).clear
 
 ' read the data (limits taken from worksheet, too)
 tr.Read Cells(7, 5), Cells(6, 5)
 
 Dim row As Integer, col As Integer
 
 row = 15
 col = 1
 
 ' this loop will create a legend based on the field names
 ' we got from TableReader
 For Each fld In tr.Rows.RfcType.StructFields
   Cells(row, col) = fld.Name
   col = col + 1
 Next fld
 
 row = 16
 
 ' finally, fill in the values
 Dim rw As RfcFields
 For Each rw In tr.Rows
    col = 1
    For Each field In rw
      Cells(row, col) = field.Value
      col = col + 1
    Next field
    row = row + 1
 Next rw

Download


icon Reading SAP tables in Excel ExcelReadTable.zip | 152.02 kB | Date: 2012-06-22

Shows how to use the TableReader to import SAP data directly into Excel