Connect Excel with SAP using RFC

Question

I need to know how to connect Excel with SAP using RFC. I have not managed to import any SAP data to Excel using the codes found so far.

I would like to be able to import data from any known transaction (e.g. a bill of materials from transaction CO03). From this I would try to understand how to extract other type of tables.

My goal is to be able to import any SAP data on a Excel spreadsheet using RFC. That would be a good start.

Do I need a special SAP account? How to verify my account is enabled to perform this type of tasks?

Answer 1

It is not possible to call any standard transaction remotely as most of them are legacy-like and doesn't return anything directly. There are couple of ways to fetch data from any transaction but they are out of the scope of this question.
The most practical way of retrieveing data from SAP to Excel is to find proper BAPI or remote-enabled FM, (including writing own wrapper FM) and this is the way I gonna describe here.

1. You don't need special account, you just need to have proper authorizations for RFC-calls, which mainly comprise of S_RFC authorization object

2. If you use BAPI, you can omit this point. If you created own wrapper, then you have to assure it is remote-enabled.

3. And then you can call your FM in VBA code and return results to Excel book. Here is the sample code:

    ' Logging in
   
      Dim retcd        As Boolean
      Dim SilentLogon  As Boolean
      Set LogonControl = CreateObject("SAP.LogonControl.1")
      Set objBAPIControl = CreateObject("SAP.Functions")
      Set R3Connection = LogonControl.NewConnection
      R3Connection.Client = "700"
      R3Connection.ApplicationServer = "server_address" 
      R3Connection.Language = "EN"
      R3Connection.User = "sap_user"
      R3Connection.Password = "sap_pass"
      R3Connection.System = "system_id"
      R3Connection.SystemNumber = "sys_num"
      R3Connection.UseSAPLogonIni = False
      retcd = R3Connection.Logon(0, SilentLogon)
      If retcd <> True Then MsgBox "Logon failed": Exit Sub
   
      ' Declaring FM interface
   
       objBAPIControl.Connection = R3Connection
       Set objgetaddress = objBAPIControl.Add("ZNM_GET_EMPLOYEE_DETAILS")
       Set objkunnr = objgetaddress.Tables("ET_KUNNR")
       Set objaddress = objgetaddress.Tables("ET_CUST_LIST")
   
       ' Filling select-options values table from sheet
   
       Dim sht As Worksheet
       Set sht = ThisWorkbook.ActiveSheet
       If sht.Cells(6, 2).Value <> " " Then
       objkunnr.Rows.Add
       objkunnr.Value(1, "SIGN") = sht.Cells(6, 2).Value
       objkunnr.Value(1, "OPTION") = sht.Cells(6, 3).Value
       objkunnr.Value(1, "LOW") = sht.Cells(6, 4).Value
       objkunnr.Value(1, "HIGH") = sht.Cells(6, 5).Value
       R3Connection.Logoff
   

P.S. For all this to work in your VBA project you should add references to SAP ActiveX controls, which are located in %ProgramFiles%\SAP\FronEnd\SAPgui directory:

• wdtaocxU.ocx
• wdtfuncU.ocx
• wdtlogU.ocx
• wdobapiU.ocx

So references list of your VBA project should look like this