Remote Function Call to SAP System using Microsoft Excel

[Former Member]

Remote Function Call to SAP System using Microsoft Excel

Remote Function Call to SAP System is quite important and useful feature when we require SAP data for a non SAP application. We have quite a few techniques to make RFC to SAP using connectors like JCo. This weblog explains how to make Remote Function Calls to SAP system with a much familiar Microsft Excel.

Add the necessary SAP ActiveX Components to Excel

The first important thing is to add the required SAP ActiveX Components to Microsoft Excel. These SAP ActiveX Components will be available if the SAP Front End is installed in your machine. You can find all the availabe SAP controls in the Controls ToolBox of Excel.



Here we are using the SAP BAPI ActiveX Component and SAP Table Factory ActiveX Component to access SAP and fetch some data to display in Excel. There are many other SAP ActiveX Components which will provide you a very rich functionality.

SAP BAPI ActiveX Component



SAP Table Factory ActiveX Component



The Design

For this test application a simple form as shown below is required. This application will fetch all the company codes from the SAP System.



The Source Code

Here the source code is broken as per their operation.

Declaration

' Declaration for the necessary objectsDim objBAPICortrol, objConnection, objCompanyCodes As ObjectDim login As Boolean

Connecting and Disconnecting to SAP System

Private Sub CommandButton1_Click()If login = False Then' Setting the necessary variables for R/3 connectionSet objBAPICortrol = CreateObject("SAP.Functions")Set objConnection = objBAPICortrol.Connection' Establish a connectionIf objConnection.Logon(0, False) Then    login = True    MsgBox "Connection Established"    CommandButton1.Caption = "Disconnect"End IfElseCommandButton1.Caption = "Connect 2 SAP"login = FalseobjConnection.LogoffSet objConnection = NothingSet objBAPICortrol = NothingEnd IfEnd Sub



Get Company codes form SAP Sytem using BAPI

Private Sub CommandButton2_Click()On Error Resume Next' The required BAPISet objCompanyCodes = objBAPICortrol.Add("BAPI_COMPANYCODE_GETLIST")objCompanyCodes.call' Getting the result set in a tableDim objTable As TableSet objTable = objCompanyCodes.Tables("COMPANYCODE_LIST")' Printing the number of company codesMsgBox "Row count :" & objTable.RowCount' Displaying the company codes to the Excel SheetFor i = 1 To objTable.RowCountActiveSheet.Cells(8 + i, 1) = objTable.Cell(i, 1)ActiveSheet.Cells(8 + i, 2) = objTable.Cell(i, 2)Next i'if any error then displaying the same to the usersIf Err Then    MsgBox Err.DescriptionEnd IfEnd Sub

Clear the List

Private Sub CommandButton3_Click()While (Worksheets("Sheet1").Cells(9, 1).Rows <> "")    Worksheets("Sheet1").Cells(9, 1).Delete    Worksheets("Sheet1").Cells(9, 2).DeleteWendEnd Sub

The Result

Well the application did work nicely fetching all the required details from the R/3 System. I was able to get the record count, records and displayed it on the Excel sheet as shown below.





A nice way to make simple reporting with SAP RFC and Microsoft Excel.