3

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?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Arturo
  • 31
  • 1
  • 1
  • 3

2 Answers2

6

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

enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Suncatcher
  • 10,355
  • 10
  • 52
  • 90
  • Thanks Suncatcher for taking the time to help me. I have three questions for you: What is the difference between BAPI and a remote enabled FM? Can any of those approaches read data from any SAP transaction? Each employee in the company I work for has access to specific SAP transactions depending on the employee role so I will need to request access to developer transactions – Arturo Jan 04 '17 at 22:18
  • What are those transactions I need to have access? – Arturo Jan 04 '17 at 22:24
  • Across your questions: 1. In context of your task - no difference, in other cases read [help](https://help.sap.com/saphelp_nw73ehp1/helpdata/en/c2/0da27f769e4c7d99f119110f6a24f4/frameset.htm). 2. No, read my introduction thoroughly. 3. No, read my introductions thoroughly. You need to find equivalent BAPI for each employee tcode or write your own wrappers. – Suncatcher Jan 05 '17 at 08:54
0

Additionally to Excel solution you may try this open source MS Access application I created long time ago and used many times: https://blogs.sap.com/2013/08/16/read-data-from-sap-tables-into-ms-access-2003-database/

itsergiu
  • 662
  • 6
  • 11
  • 2
    Whilst this may theoretically answer the question, [it would be preferable](//meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Tim Diekmann Jun 18 '18 at 21:46