Accessing SAP Functions from Excel using Visual Ba...

Former Member · ‎12-12-2007

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

Overview

In 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

ParameterName	Type	AssociatedText	ShortText
LOGONDATA	LIKE	BAPILOGOND	Structure with Logon Data
DEFAULTS	LIKE	BAPIDEFAUL	Structure with User Defaults
ADDRESS	LIKE	BAPIADDR3	Address Data
COMPANY	LIKE	BAPIUSCOMP	Company for Company Address
SNC	LIKE	BAPISNCU	Secure Network Communication Data
REF_USER	LIKE	BAPIREFUS	User Name of the Reference User
ALIAS	LIKE	BAPIALIAS	User Name Alias

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 Code

Public 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)

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)

'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)

iValuePosn = iValuePosn + 2

Set oTable = Nothing

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)

iRowPosn = iRowPosn + 4

End If

Set obImpStructure = Nothing

Create Custom BAPIs

Catching Function Module Exception from VB

For creating custom BAPIs, we will create a custom table ZEMP_DTLS with 4 columns.

Creating table

Create BAPI ZEMP_BAPI_INS_SMPL

While 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 Code

FUNCTION 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 Code

Public 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 VB

The 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 Structure

Using 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 Parameter

In Tables parameter add a parameter as shown below.

Function Module Code

The 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 Code

Public 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)

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