Accessing SAP Functions from Excel using Visual Basic Applications.
SAP has provided BAPIs which can be used to access SAP functions from Non SAP applications like Excel, VB, Java and C++.
Overview............................................................................................................... 1 Call Standard SAP Function....................................................................................... 1 Whole Code........................................................................................................ 3 Create Custom BAPIs............................................................................................... 6 Catching Function Module Exception from VB............................................................. 6 Creating table..................................................................................................... 7 Create BAPI ZEMP_BAPI_INS_SMPL.......................................................................... 7 Import Parameters................................................................................................ 7 Exception........................................................................................................... 7 Function Module Code........................................................................................... 8 VB Code............................................................................................................. 8 Catching Exception in VB....................................................................................... 9 Passing Table Parameters as Input............................................................................. 9 Create Structure.................................................................................................. 9 Pass Table Parameter........................................................................................... 9 Function Module Code......................................................................................... 10 VB Code........................................................................................................... 10 OverviewIn this Blog, we will see how to call standard RFC enabled SAP function modules and custom Function Modules using VBA. To access SAP from Excel, SAP GUI client must be installed on the pc. Also only RFC (Remote Function Call) enabled functions can be called by External Applications. The list of RFC enabled Function Modules in SAP can be obtained from the table TFDIR and set condition FMODE = ‘R'.
In this blog I have assumed the user is comfortable with Visual Basic and creating VBA macros in Excel.
Call Standard SAP Function
Example to get UserDetails. In this example the BAPI BAPI_USER_GET_DETAIL is executed. This BAPI can be executed on almost all SAP systems. The parameters to this BAPI may change depending on the release number and type of system.
For ease the following constants are declared in VBA. Public Const CNT_STR_USR As String = "MyId" Public Const CNT_STR_PWD As String = "MYPWD" ‘It is better to make password in CAPITAL and less than 8 characters, because in some systems the password is case sensitive and password is send in CAPITAL to SAP system. Public Const CNT_STR_APPLN_SRVR As String = "ides47" Public Const CNT_STR_SYSTEM As String = "IDS" Public Const CNT_STR_SYS_NUM As String = "00" Public Const CNT_STR_CLIENT As String = "800" Public Const CNT_STR_LOGON_LANG As String = "EN" Public Const CNT_STR_LOG_FILE As String = "C:\sap_vb.txt" Public Const CNT_INT_LOG_LEVEL As Integer = 9 Const CNT_STR_BAPI_USER_DETAILS as String = "BAPI_USER_GET_DETAIL"
Create a macro in Excel called GetSAPUserDetails
Declaring the SAP Function control. This ActiveX control is used to connect to SAP and call the Function Modules. Either you can call this object at Runtime or at Design Time. For calling this object at Design time, add a reference to the ActiveX Control "c:\program files\sap\frontend\sapgui\wdtfuncs.ocx". For getting the exact path of this ActiveX control, search in Windows registry for "SAP.Functions"
i.e. Dim obSAPFnCntrl as Object or Dim obSAPFnCntrl as SAPFunctions
The advantage of calling object with Reference at runtime is that it is easy to change the code if you want to use it for VBScript.
Set obSAPFnCntrl = CreateObject ("SAP.Functions") Create a Connection Object to connect to SAP Dim obSAPConn as Object Set obSAPConn = obSAPFnCntrl.Connection
‘Set the Logfile and LogLevel Details obSAPFnCntrl.LogLevel = CNT_INT_LOG_LEVEL obSAPFnCntrl.LogFileName = CNT_STR_LOG_FILE
Set the properties for the connection control With obSapConn .ApplicationServer = CNT_STR_APPLN_SRVR .SystemNumber = CNT_STR_SYS_NUM .User = CNT_STR_USR .Password = CNT_STR_PWD .Language = CNT_STR_LOGON_LANG .Client = CNT_STR_CLIENT End With
Logging to SAP System
If obSapConn.Logon(0, True) = False Then MsgBox "R/3 connection failed" Exit Sub End If The Second Parameter True for the Logon method tells that it is a silent logon. If this parameter is set to False, then the LogonControl will be shown.
Create a BAPI Function Object. Dim obSAPFnUserDetail as Object ‘Use the Add method of Function Control to attach Function. Set obSAPFnUserDetail = obSapFnCntrl.Add(CNT_STR_BAPI_USER_DETAILS)
Declare the Export Parameter. This is similar to Export Parameter while calling Function Modules. The import parameter of Function Module is set as Export Parameter while calling the Function Module. Here the only Export parameter is UserId Call the Exports property of the function being called. Dim obSAPExpUserName as Object Set obSAPExpUserName = obSAPFnUserDetail.Exports("USERNAME") Assign value for the Export Parameter obSAPExpUserName.Value = CNT_STR_USR
Declare the Import Parameters The import parameters can be set either before calling function or after calling Function The import parameters to function are BAPI_USER_GET_DETAIL. Here all Import Parameters are of type Structure
Here we will use only the first 2 parameters logonData and Defaults Declare objects for each parameter Dim obSAPImpLogonData as Object ,obSAPImpDefaults as Object
Set obSAPImpLogonData = obSAPFnUserDetail.Imports("LOGONDATA") Set obSAPImpDefaults = obSAPFnUserDetail.Imports("DEFAULTS")
The parameters can be of normal datatype like char or integer or structure. The Set statement assigns the parameter type to the objects.
RETURN parameter. The Return parameter can be of structure BAPIRET1 or BAPIRET2.
Declare the Table Parameters Dim obSAPTblParameter as Object ,obSAPTblProfiles as Object , obSAPTblReturn as Object ‘Now Set the objects Set obSAPTblParameter = obSAPFnUserDetail.Tables("PARAMETER") Set obSAPTblProfiles = obSAPFnUserDetail.Tables("PROFILES") Set obSAPTblReturn = obSAPFnUserDetail.Tables("RETURN")
Now Call the function. If obSapFnUserDetail.Call = False Then Msgbox "Function Call Error" ‘If there is any exception declared in code, it can be called as obSapFnUserDetail.Exception EndIf
Now populate the structure details, 1 by 1. At end remember to call the LogOff method of Connection object.
Whole CodePublic Sub GetUserDetails() Dim obSAPFnCntrl As Object 'Object for establishing Connection and Calling BAPI Dim obSAPConn As Object ' SAP Connection Object Dim obSAPFnUserDetail As Object ' Object used for calling BAPI function Dim obSAPExpUserName As Object ' Export Parameter to hold the user Value 'Import Parameters Dim obSAPImpLogonData As Object, obSAPImpDefaults As Object, obSAPImpAddress As Object Dim iColCnt As Integer, iIndex As Integer, iRowCount As Integer, iRowIndex As Integer, iColNameIndex As Integer Dim obSAPTblActivitygroups As Object, obSAPTblAddtel As Object, obSAPTblAddfax As Object, obSAPTblAddttx As Object
Dim oColumn As Object 'Table Parameters Dim obSAPTblParameter As Object, obSAPTblProfiles As Object, obSAPTblReturn As Object
Set obSAPFnCntrl = CreateObject("SAP.Functions") Set obSAPConn = obSAPFnCntrl.Connection
obSAPFnCntrl.LogLevel = CNT_INT_LOG_LEVEL obSAPFnCntrl.LogFileName = CNT_STR_LOG_FILE
'Set the properties for the connection control With obSAPConn .ApplicationServer = CNT_STR_APPLN_SRVR .SystemNumber = CNT_STR_SYS_NUM .User = CNT_STR_USR .Password = CNT_STR_PWD .Language = CNT_STR_LOGON_LANG .Client = CNT_STR_CLIENT End With
'Logon to SAP in silent mode If obSAPConn.Logon(0, True) = False Then MsgBox "R/3 connection failed" Exit Sub End If
'Use the Add method of Function Control to attach Function. Set obSAPFnUserDetail = obSAPFnCntrl.Add(CNT_STR_BAPI_USER_DETAILS)
Set obSAPExpUserName = obSAPFnUserDetail.Exports("USERNAME") obSAPExpUserName.Value = "BASIS" 'Get Details of user BASIS
Set obSAPImpLogonData = obSAPFnUserDetail.Imports("LOGONDATA") Set obSAPImpDefaults = obSAPFnUserDetail.Imports("DEFAULTS")
' Now Set the objects Set obSAPTblParameter = obSAPFnUserDetail.Tables("PARAMETER") Set obSAPTblProfiles = obSAPFnUserDetail.Tables("PROFILES") Set obSAPTblReturn = obSAPFnUserDetail.Tables("RETURN")
'Now Call the function. If obSAPFnUserDetail.Call = False Then MsgBox "Function Call Error" Else 'Function Call is Successfull 'Now get the Imported Structure Details 'Here we are calling the ColumnCount property of Structure object 'This is because for this BAPI, all import parameters are Structures
iColCnt = obSAPImpLogonData.ColumnCount For iIndex = 1 To iColCnt 'Populate Contents into Excel Sheet Sheet1.Cells(2, iIndex) = obSAPImpLogonData.ColumnName(iIndex) Sheet1.Cells(3, iIndex).FormulaR1C1 = "'" & obSAPImpLogonData.Value(iIndex) Next
iColCnt = obSAPTblParameter.ColumnCount iRowCount = obSAPTblParameter.RowCount 'Now Get The Table Properties for Parameter iColNameIndex = 1 iIndex = 0 For Each oColumn In obSAPTblParameter.Columns iIndex = iIndex + 1 Sheet2.Cells(iColNameIndex, iIndex) = oColumn.Name Next oColumn
For iRowIndex = 1 To iRowCount For iIndex = 1 To iColCnt Sheet2.Cells(iRowIndex + iColNameIndex, iIndex).FormulaR1C1 = obSAPTblParameter.Value(iRowIndex, iIndex) Next Next
'If there is any exception decalred in code, it can be called as obSapFnUserDetail.Exception End If 'Get the whole tables Data Dim oTables As Object 'Use the Tables collection Set oTables = obSAPFnUserDetail.Tables Call GetColumnDetails(oTables) Set oTables = Nothing
'Get the Structure Details Dim obImports As Object Set obImports = obSAPFnUserDetail.Imports Call GetImportDetails(obImports) Set obImports = Nothing
obSAPFnCntrl.Remove (CNT_STR_BAPI_USER_DETAILS) obSAPConn.LogOff
Set obSAPImpAddress = Nothing Set obSAPImpLogonData = Nothing Set obSAPImpDefaults = Nothing Set obSAPTblParameter = Nothing Set obSAPTblProfiles = Nothing Set obSAPExpUserName = Nothing Set obSAPTblReturn = Nothing
Set obSAPFnUserDetail = Nothing Set obSAPConn = Nothing Set obSAPFnCntrl = Nothing End Sub
Private Sub GetColumnDetails(ByVal obTables As Object) Dim iLoop As Integer, iColIndx As Integer, iColValuePos As Integer Dim iTblCnt As Integer, iColCnt As Integer, iRowCnt As Integer, iRowIndx As Integer Dim oTable As Object, oColumn As Object, iValuePosn As Integer
iTblCnt = obTables.Count iValuePosn = 1 For iLoop = 1 To iTblCnt Set oTable = obTables.Item(iLoop) iColCnt = oTable.ColumnCount iRowCnt = oTable.RowCount iColValuePos = 1 'Write the Table Name Sheet3.Cells(iValuePosn, 1) = "The columns in table : " & oTable.Name 'Get ColumnNames iValuePosn = iValuePosn + 1 For Each oColumn In oTable.Columns Sheet3.Cells(iValuePosn, iColValuePos) = oColumn.Name iColValuePos = iColValuePos + 1 Next oColumn
'Enter the column Values For iRowIndx = 1 To iRowCnt iValuePosn = iValuePosn + 1 For iColIndx = 1 To iColCnt Sheet3.Cells(iValuePosn, iColIndx) = oTable.Value(iRowIndx, iColIndx) Next Next iValuePosn = iValuePosn + 2 Set oTable = Nothing Next
End Sub
Private Sub GetImportDetails(ByVal obImpParameter As Object) Dim iImpCnt As Integer, iLoop As Integer, iRowPosn As Integer Dim iStrCols As Integer, iStrIndx As Integer Dim obImpStructure As Object
iRowPosn = 1 iImpCnt = obImpParameter.Count For iLoop = 1 To iImpCnt 'Sheet3.Cells(iRowPosn, 1) = obImpParameter.Item(iLoop).Name Set obImpStructure = obImpParameter.Item(iLoop) If obImpStructure.IsStructure = True Then Sheet4.Cells(iRowPosn, 1) = obImpStructure.Name & " is a Structure" iStrCols = obImpStructure.ColumnCount 'Get Structure Names and Values For iStrIndx = 1 To iStrCols Sheet4.Cells(iRowPosn + 1, iStrIndx) = obImpStructure.ColumnName(iStrIndx) Sheet4.Cells(iRowPosn + 2, iStrIndx) = obImpStructure.Value(iStrIndx) Next 'Now Get Columns of Structure iRowPosn = iRowPosn + 4 End If
Set obImpStructure = Nothing Next
End Sub
Create Custom BAPIsCatching Function Module Exception from VBFor creating custom BAPIs, we will create a custom table ZEMP_DTLS with 4 columns. Creating table
Create BAPI ZEMP_BAPI_INS_SMPLWhile creating BAPI ensure that in Attributes tab, the Remote Enabled Module is checked.
Import Parameters
In Import Parameter insure that the Passby Value is ticked. Exception
Function Module CodeFUNCTION ZEMP_BAPI_INS_SMPL.
data: wa_emp type ZEMP_DTLS. "Declare Work area for the table
wa_emp-empid = EMPID. "Populate the Work Area wa_emp-doj = DOJ. if doj<'19010101'. raise INVALID_DATE_OF_JOIN. endif. insert into ZEMP_DTLS values wa_emp.
ENDFUNCTION.
VB CodePublic Sub InsertBapiEmp() Dim obSAPFnCntrl As Object 'Object for establishing Connection and Calling BAPI Dim obSAPConn As Object ' SAP Connection Object Dim obSAPFnEmpIns As Object ' Object used for calling BAPI function Dim obSAPExpEmpId As Object ' Export Parameter to hold the user Value Dim obSAPExpDOJ As Object
Set obSAPFnCntrl = CreateObject("SAP.Functions") Set obSAPConn = obSAPFnCntrl.Connection
obSAPFnCntrl.LogLevel = CNT_INT_LOG_LEVEL obSAPFnCntrl.LogFileName = CNT_STR_LOG_FILE
'Set the properties for the connection control With obSAPConn .ApplicationServer = CNT_STR_APPLN_SRVR .SystemNumber = CNT_STR_SYS_NUM .User = CNT_STR_USR .Password = CNT_STR_PWD .Language = CNT_STR_LOGON_LANG .Client = CNT_STR_CLIENT End With
'Logon to SAP in silent mode If obSAPConn.Logon(0, True) = False Then MsgBox "R/3 connection failed" Exit Sub End If
'Use the Add method of Function Control to attach Function. Set obSAPFnEmpIns = obSAPFnCntrl.Add(CNT_STR_BAPI_EMP_INS)
Set obSAPExpEmpId = obSAPFnEmpIns.Exports("EMPID") obSAPExpEmpId.Value = "K10021" Set obSAPExpDOJ = obSAPFnEmpIns.Exports("DOJ") obSAPExpDOJ.Value = "18980506"
If obSAPFnEmpIns.Call = False Then MsgBox "Function Call Error, Exception is " & obSAPFnEmpIns.Exception End If obSAPFnCntrl.Remove (CNT_STR_BAPI_EMP_INS) Set obSAPExpDOJ = Nothing Set obSAPExpEmpId = Nothing Set obSAPFnEmpIns = Nothing obSAPConn.LogOff Set obSAPConn = Nothing Set obSAPFnCntrl = Nothing End Sub
Catching Exception in VBThe Macro will throw exception from SAP side
Now in the Macro, change value of DOJ parameter to "20070202". Single record will be inserted to Database Tables.
Passing Table Parameters as Input
Now we will pass a Table as an Input Parameter. TO pass tables, it is required to create a structure first. Create StructureUsing T-code SE11, create a structure ZEMP_STR
Now create a function ZEMP_BAPI_INS_TBL using T-code se37. While creating function ensure that the Remote Function enabled radio button is clicked. Pass Table ParameterIn Tables parameter add a parameter as shown below.
Function Module CodeThe source code for function is below FUNCTION zemp_bapi_ins_tbl. DATA: wa_emp TYPE zemp_dtls. *Insert data clear wa_emp. LOOP AT zemp_tbl INTO wa_emp. wa_emp-mandt = sy-mandt. INSERT INTO zemp_dtls VALUES wa_emp. CLEAR wa_emp. ENDLOOP. ENDFUNCTION.
Now in Excel create a Macro BapiInsertWithTable for calling this Function.
VB CodePublic Sub BapiInsertWithTable() Dim obSapFn As Object Dim obSapCon As Object Dim obEmpFn As Object, obTblEmp As Object Dim iSLoop As Long, iSRows As Long 'Variables for looping through Excel Sheet
Set obSapFn = CreateObject("SAP.Functions") obSapFn.LogLevel = CNT_INT_LOG_LEVEL obSapFn.LogFileName = CNT_STR_LOG_FILE
Set obSapCon = obSapFn.Connection With obSapCon .ApplicationServer = CNT_STR_APPLN_SRVR .System = CNT_STR_SYSTEM .SystemNumber = CNT_STR_SYS_NUM
.User = CNT_STR_USR .Password = CNT_STR_PWD .Language = CNT_STR_LOGON_LANG .Client = CNT_STR_CLIENT ' .AbapDebug = True .Tracelevel = True ' .RfcWithDialog = True .HostName = CNT_STR_APPLN_SRVR .LowSpeedConnection = False .MessageServer = CNT_STR_APPLN_SRVR End With
'Check For Connection If obSapCon.Logon(0, True) = False Then MsgBox "R/3 connection failed" Exit Sub End If
Set obEmpFn = obSapFn.Add("ZEMP_BAPI_INS_TBL") Set obTblEmp = obEmpFn.Tables("ZEMP_TBL") 'This creates the Table Type Object obTblEmp.FreeTable iSRows = 8 ' Number of rows to be looped 'Assign values for each column of the table. For iSLoop = 2 To iSRows obTblEmp.Rows.Add 'Use the Rows.Add method to add row to table 'obTblEmp.Value(obTblEmp.RowCount, "MANDT") = CNT_STR_CLIENT obTblEmp.Value(obTblEmp.RowCount, "EMPID") = Sheet1.Cells(iSLoop, 1) obTblEmp.Value(obTblEmp.RowCount, "DOJ") = CDate(Sheet1.Cells(iSLoop, 2)) obTblEmp.Value(obTblEmp.RowCount, "LOCN") = Sheet1.Cells(iSLoop, 3) Next
If obEmpFn.Call = False Then MsgBox "Error in calling Sap Fn " End If obTblEmp.FreeTable 'After calling function free the internal table object obSapFn.RemoveAll ' Remove function from the object Set obTblEmp = Nothing Set obEmpFn = Nothing obSapCon.LogOff Set obSapCon = Nothing Set obSapFn = Nothing End Sub
The Excel data is shown below.
After executing the Macro, 8 records will be inserted into the table ZEMP_DTLS. Reference: http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCFESDE5/BCFESDE5.pdf
|
User | Count |
---|---|
1 | |
1 | |
1 | |
1 |