vba 使用 RFC 将 Excel 与 SAP 连接

Connect Excel with SAP using RFC

提问by Arturo

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.

我需要知道如何使用 RFC 将 Excel 与 SAP 连接起来。到目前为止，我还没有设法使用找到的代码将任何 SAP 数据导入 Excel。

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.

我希望能够从任何已知交易中导入数据（例如来自交易 CO03 的物料清单）。由此我将尝试了解如何提取其他类型的表。

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

我的目标是能够使用 RFC 在 Excel 电子表格中导入任何 SAP 数据。那将是一个好的开始。

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

我需要一个特殊的 SAP 帐户吗？如何验证我的帐户是否可以执行此类任务？

回答by Suncatcher

It is not possible to call anystandard 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.

不可能远程调用任何标准事务，因为它们中的大多数都是类似遗留的并且不直接返回任何内容。有几种方法可以从任何事务中获取数据，但它们超出了本问题的范围。
从 SAP 检索数据到 Excel 的最实用方法是找到合适的 BAPI 或远程启用的 FM，（包括编写自己的包装器 FM），这就是我将在此处描述的方式。

1. You don't need special account, you just need to have proper authorizations for RFC-calls, which mainly comprise of S_RFCauthorization 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
   

1. 您不需要特殊帐户，您只需要对 RFC 调用有适当的授权，主要包括S_RFC授权对象
2. 如果使用 BAPI，则可以省略这一点。如果您创建了自己的包装器，那么您必须确保它是远程启用的。

3. 然后您可以在 VBA 代码中调用您的 FM 并将结果返回到 Excel 工作簿。这是示例代码：

   ' 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\SAPguidirectory:

PS 要在您的 VBA 项目中使用所有这些，您应该添加对 SAP ActiveX 控件的引用，这些控件位于%ProgramFiles%\SAP\FronEnd\SAPgui目录中：

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

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

So references list of your VBA project should look like this

所以你的 VBA 项目的参考列表应该是这样的

回答by itsergiu

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/

除了 Excel 解决方案，您还可以尝试我很久以前创建并多次使用的开源 MS Access 应用程序：https: //blogs.sap.com/2013/08/16/read-data-from-sap-tables-into -ms-access-2003-database/