Tutorial on using VBA to Connect to SAP RFC
SAP RFC (Remote Function Call) allows an external program to connect to the SAP system to either read data or write data to the SAP system. The RFC SDK is available when you install the SAP GUI (Frontend) and specify a custom or full installation. The RFC SDK is a very versatile interface tool; it has a COM interface allowing it to be used with VBA (Visual Basic).
This tutorial will walk you through the process of connecting to a SAP system without user intervention, also known as, silent connection. I will use Excel for this example to return a list of sales items that were sold.
Open the Visual Basic Editor in Excel. You might need to enable the Developer Tab in Excel if it is not visible. Search the documentation or check out Customize the ribbon under the Options.
These are the steps that I will perform:
- Add a reference to SAP
- Create Script
- Retrieve Data
Add a reference to SAP
Under Tools | References in the VBA Editor menu, scroll to you find the SAP objects
- Select SAP Data Provider (sapdattap.ocx)
- Select SAPGUIRessources (sapguirm.ocx)
Under the Modules folder in the VBA Editor in the Project Navigator create a new module (Right click -> new module). I called my module SAP_data but you can give the module any name you want. In the new module create a new Sub, like SAP (see Figure 1).
Enter the code like in Figure 1. You have objects for the worksbook and worksheet; an object for the BAPI Control (oBapiCtrl) and the SAP Login Control (oBapiLogon). Also create an object variable for sales (oSalesOrder) and the sales items (oItems).
Next as is standard practice, you will need to set (initialize) your variables. Once the objects are created, set the logon parameters as in the screenshot. Using the Silent connection requires more information can a user providing the details. In particular, you will need the Application Server address, user and password, the language and finally the system number otherwise you will get an error.
You can attempt to logon by testing for a successful logon. Like the following code snippet, otherwise you can exit the routine gracefully.
If oBapiCtrl.Connection.Logon(0, True) <> True Then
MsgBox "not connected", vbInformation, "SAP Logon"
If the system can’t logon, simply exit the sub or as in the example, notify the end user that we couldn’t log onto the system. Otherwise, setup the Worksheet to receive data from SAP. In my example I will get sales orders from the SAP system.
In the following code snippet, I test to see if the BAPI Control is connected before setting the oSalesOrder object to avoid throwing an error. The rest is pretty much self-explanatory, you simply map the object properties to the cell address.
The last property that I retrieve is the number of items in the order
oSheet.Cells(5, 5).Value = oSalesOrder.items.Count
I will use this value to loop through the items in the order and retrieve the order’s details, as the following code snippet demonstrates:
For Each oItem In oSalesOrder.items
oSheet.Cells(iIndex, 1).Value = oItem.SalesDocumentno
oSheet.Cells(iIndex, 2).Value = oItem.itemNo
The next section contains the complete code that you can copy and paste into your Excel or VB project. Remember to change the logon values to those appropriate for your company or test environment.
Sub sap() Dim oBook As Workbook Dim oSheet As Worksheet Dim oBapiCtrl As Object Dim oBapiLogon As Object Dim oSalesOrder As Object Dim oCustomer As Object Dim oProduct As Object Dim oItem As Object Dim iIndex As Integer Set oBook = Application.ActiveWorkbook Set oSheet = oBook.Worksheets(1) Set oBapiCtrl = CreateObject("sap.bapi.1") Set oBapiLogon = CreateObject("sap.logoncontrol.1") oBapiCtrl.Connection = oBapiLogon.NewConnection oBapiCtrl.Connection.ApplicationServer = "000.000.000.000" oBapiCtrl.Connection.System = "test" oBapiCtrl.Connection.Client = "0000" oBapiCtrl.Connection.User = "someuser" oBapiCtrl.Connection.Password = "xxxxxxxxxx" oBapiCtrl.Connection.Language = "EN" oBapiCtrl.Connection.SystemNumber = "00" If oBapiCtrl.Connection.Logon(0, True) <> True Then MsgBox "not connected", vbInformation, "SAP Logon" Exit Sub End If If oBapiCtrl.Connection.IsConnected Then Set oSalesOrder = oBapiCtrl.GetSAPObject("SalesOrder", "0000000000") oSheet.Cells(4, 1).Value = "Sales Document" oSheet.Cells(4, 2).Value = "Net Value" oSheet.Cells(4, 3).Value = "Customer Number" oSheet.Cells(4, 4).Value = "Document Date" oSheet.Cells(4, 5).Value = "Number of Items" oSheet.Cells(5, 1).Value = oSalesOrder.salesdocument oSheet.Cells(5, 2).Value = oSalesOrder.netvalue oSheet.Cells(5, 3).Value = oSalesOrder.orderingparty.customerno oSheet.Cells(5, 4).Value = oSalesOrder.documentdate oSheet.Cells(5, 5).Value = oSalesOrder.items.Count End If iIndex = 8 â Loop through the order item details oSheet.Cells(7, 1).Value = "Order No" oSheet.Cells(7, 2).Value = "Item No" oSheet.Cells(7, 3).Value = "Product Code" oSheet.Cells(7, 4).Value = "Product Description" oSheet.Cells(7, 5).Value = "Selling Quantity" oSheet.Cells(7, 6).Value = "Selling UoM" oSheet.Cells(7, 7).Value = "Product Description" For Each oItem In oSalesOrder.items oSheet.Cells(iIndex, 1).Value = oItem.SalesDocumentno oSheet.Cells(iIndex, 2).Value = oItem.itemNo oSheet.Cells(iIndex, 3).Value = oItem.material.material oSheet.Cells(iIndex, 4).Value = oItem.material.materialdescription oSheet.Cells(iIndex, 5).Value = oItem.targetquantity oSheet.Cells(iIndex, 6).Value = oItem.targetquantityunit oSheet.Cells(iIndex, 7).Value = oItem.Description iIndex = iIndex + 1 Next oSheet.Columns.AutoFit Set oBapiLogon = Nothing Set oBapiCtrl = Nothing End Sub
Caveat: In is critical to get acquainted with the BAPI Explorer, Transaction: BAPI, in SAP to find the Business Objects, the import parameters and fields and methods. Also you will find the corresponding BAPIs and you can run tests to check out what the function is returning as values.
I hope the article was helpful and remember to get to know the BAPI Explorer. The answers are in there, except you will need to look for them.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.