Skip to Content

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)

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

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

 

 

 

 

 

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Wes Feimster

    Vikas – thank you for this very detailed explanation.  I see that you have described the Exception function.  I have used that for function module CSAP_MAT_BOM_MAINTAIN and found that Exception lacks any detail whatsoever when running in VBA.  If I were creating this within ABAP, I would have access to the structure SYST to see the more detailed message class, number, and variables.  Are you aware of any way to get to this structure?

    (0) 
  2. Setu Kansara

    My company use Web Portal to logon to SAP, so i can not logon using this code, can you guide as is there any other method i should use to logon.
    I have WinShuttle license that works fine but no RFC privileges.
    Thank you in advance.

    (0) 

Leave a Reply