EXCEL VBA Macro to Download SAP Table contents



Here is the Code to download any SAP table contents to excel without login in to SAP GUI.

Function Module RFC_READ_TABLE is used to get the table contents.


 Sub Read_Table()  
  Dim LogonControl As Object  
  Dim R3Connection As Object  
  Dim retcd    As Boolean  
  Dim SilentLogon As Boolean  
  Dim returnFunc As Boolean  
  Dim objTableContent As Object  
  Dim tOptions As Object  
  Dim tFields As Object  
  Dim tData As Object  
  Dim RowData$()  
  Dim j&, i&, k&  
 'Set Connection  
  Set LogonControl = CreateObject("SAP.LogonControl.1")  
  Set objBAPIControl = CreateObject("SAP.Functions")  
  Set R3Connection = LogonControl.NewConnection  
  Dim boBapiService As Object  
 'SAP connection  
  R3Connection.client = Sheet4.Cells(3, 2).Value  
  R3Connection.ApplicationServer = Sheet4.Cells(4, 2).Value  
  R3Connection.Language = Sheet4.Cells(5, 2).Value  
  R3Connection.User = ""  
  R3Connection.Password = ""  
  R3Connection.System = Sheet4.Cells(6, 2).Value  
  R3Connection.SystemNumber = Sheet4.Cells(7, 2).Value  
  R3Connection.UseSAPLogonIni = False  
  SilentLogon = False  
  Dim oMsgReturn As Object  
  Dim oMsgText As String  
  retcd = R3Connection.logon(0, SilentLogon)  
  If retcd <> True Then MsgBox "Logon failed": Exit Sub  
  objBAPIControl.Connection = R3Connection  
 'Call the Function Module by BAPI Service  
   Set objTableContent = objBAPIControl.Add("RFC_READ_TABLE")  
   With objTableContent  
     .exports("QUERY_TABLE") = Sheet4.Cells(15, 2).Value  
     .exports("DELIMITER") = Sheet4.Cells(16, 2).Value  
   End With  
   Set tOptions = objTableContent.Tables("OPTIONS")  
   Set tFields = objTableContent.Tables("FIELDS")  
   Set tData = objTableContent.Tables("DATA")  
   tOptions.Rows.Add  
   tOptions(1, "TEXT") = Sheet4.Cells(17, 2).Value  
 '  tOptions.Rows.Add  
 '  tOptions(2, "TEXT") = Sheet4.Cells(4, 2).Value  
 '  tOptions.Rows.Add  
 '  tOptions(3, "TEXT") = Sheet4.Cells(5, 2).Value  
 '  
 '  
 '  tOptions.Rows.Add  
 '  tOptions(1, "TEXT") = "BUKRS = '1000' "  
 '  tOptions.Rows.Add  
 '  tOptions(2, "TEXT") = "AND WT_EXDT GE '20110101' "  
 '  tOptions.Rows.Add  
 '  tOptions(3, "TEXT") = "AND WT_EXDT LE '20111231' "  
 '  tFields.Rows.Add  
 '  tFields(1, "FIELDNAME") = "BUKRS"  
 '  tFields.Rows.Add  
 '  tFields(2, "FIELDNAME") = "LIFNR"  
 '  tFields.Rows.Add  
 '  tFields(3, "FIELDNAME") = "WT_SUBJCT"  
 '  tFields.Rows.Add  
 '  tFields(4, "FIELDNAME") = "WT_WTSTCD"  
 '  tFields.Rows.Add  
 '  tFields(5, "FIELDNAME") = "WT_WITHCD"  
 '  tFields.Rows.Add  
 '  tFields(6, "FIELDNAME") = "WT_EXNR"  
 '  tFields.Rows.Add  
 '  tFields(7, "FIELDNAME") = "WT_EXRT"  
 '  tFields.Rows.Add  
 '  tFields(8, "FIELDNAME") = "WT_EXDF"  
 '  tFields.Rows.Add  
 '  tFields(9, "FIELDNAME") = "WT_EXDT"  
   tFields.Rows.Add  
   tFields(1, "FIELDNAME") = Sheet4.Cells(21, 1).Value  
   tFields.Rows.Add  
   tFields(2, "FIELDNAME") = Sheet4.Cells(21, 2).Value  
   tFields.Rows.Add  
   tFields(3, "FIELDNAME") = Sheet4.Cells(21, 3).Value  
   tFields.Rows.Add  
   tFields(4, "FIELDNAME") = Sheet4.Cells(21, 4).Value  
   tFields.Rows.Add  
   tFields(5, "FIELDNAME") = Sheet4.Cells(21, 5).Value  
   tFields.Rows.Add  
   tFields(6, "FIELDNAME") = Sheet4.Cells(21, 6).Value  
   tFields.Rows.Add  
   tFields(7, "FIELDNAME") = Sheet4.Cells(21, 7).Value  
   tFields.Rows.Add  
   tFields(8, "FIELDNAME") = Sheet4.Cells(21, 8).Value  
   tFields.Rows.Add  
   tFields(9, "FIELDNAME") = Sheet4.Cells(21, 9).Value  
 'if value is returned by BAPI call copy it to worksheet  
   returnFunc = objTableContent.Call  
   If returnFunc = True Then  
     j = tData.RowCount  
     MsgBox ("Row count :" & tData.RowCount)  
     If j Then  
       For i = 1 To j  
         RowData = Split(tData(i, "WA"), "|")  
         For k = 1 To 9  
           Sheet4.Cells(21 + i, k).Value = RowData(k - 1)  
         Next  
       Next  
     End If  
   Else  
     MsgBox "Error when accessing BAPI in R/3 ! "  
     Exit Sub  
   End If  
   objBAPIControl.Connection.logoff  
   Set R3Connection = Nothing  
   MsgBox "Done!", 0, "Exit"  
 End Sub  


OUTPUT





1 comments:

jake george said...
This comment has been removed by a blog administrator.