Silent Connection with SAPLOGON with RFC SDK Example | RFC | Excel | VBA
By klanguedoc
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)
Create Script
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 an 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.
Logon Procedure
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"
Exit Sub
End If
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.
Retrieving Data
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.
Complete Code
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.
Comments
No comments yet.
