How To Run Any BAPI From VBA

In SAP, it is possible to write functions that can be called remotely. A special function with the ability to be called from outside of SAP is commonly referred to as a BAPI or "Business Application Programming Interface".

A connection to an SAP server is required to perform a function call from VBA. This sample code to create a connection to SAP was written for Excel and creates a temporary tab with instruction for the user to sign into SAP. The popup login screen for SAP often appears on another screen from the Excel spreadsheet, so this prompts the user to look for it.

Function GetR3(ByRef R3 As Object) As Boolean
    'Dim wsTemp As Worksheet
    'Set wsTemp = CreateTab("Attention")
    'wsTemp.Range("A1").Value = "Please sign into SAP."
    'wsTemp.Range("A1").Font.Size = 33
    
    Set R3 = CreateObject("SAP.Functions")
    '*************************************
    '  Put in your own credentials here.
    '*************************************
    R3.Connection.System = "P01"
    R3.Connection.SystemNumber = "00"
    R3.Connection.CLIENT = "100"
    R3.Connection.User = ""  ' Put your User ID here (optional).
    R3.Connection.Password = "" 'Put your password here (optional).
    R3.Connection.LANGUAGE = "EN"
    R3.Connection.ApplicationServer = "p01server.domain.ext"
       
    If R3.Connection.logon(0, True) <> True Then
       If R3.Connection.logon(0, False) <> True Then
            GetR3 = False
            Exit Function
       End If
    End If
    GetR3 = True
    'a = NukeTab("Attention")
End Function

Now a subroutine to call this function and populate an object with the SAP connection is called. It simply declares the connection as a generic object.

Sub GetSalesTexts()
    Dim R3 As Object, dStart As Date, nCountMARC As Long
    Dim nCurrent As Long, nCurrentRow As Long, nCurrentText As Long, nCountMVKE As Long
    Dim nCurrentRow2 As Long, nTextCount As Long
    Dim wsMVKE As Worksheet, wsTemp As Worksheet

    
    If Not GetR3(R3) Then
        'Add a popup here if desired.
        Exit Sub
    End If
    
    For nCurrent = 2 To 1048576
        'Most code to handle each row of the worksheet omitted for brevity . . .
        retval = GetMaterialTexts(R3, strMATNR, strVKORG, strVTWEG)  
    Next
    Sheets("LongTexts").Activate  'Probably not necessary as the other tabs are deleted.
    
    retval = DoEvents
    
    R3.Connection.logoff
    Set R3 = Nothing
    Columns("B:Z").AutoFit

End Sub

Like most functions, a BAPI has parameters that can be populated to pass information into the function. These parameters to the SAP function--along with the return values--are first declared as objects.

Function GetMaterialTexts(ByRef R3, strMATNR As String, strVKORG As String, strVTWEG As String) As Long

    Dim MyFunc, App As Object, Result As Boolean
    
    'Set up the variables.
    Dim MATERIAL As Object
    Dim SALESORGANISATION As Object
    Dim DISTRIBUTIONCHANNEL As Object
    
    Dim MATERIALTEXT As Object  'Sales Texts...
    
    Dim BAPIRET2 As Object 'Return is a reserved word...

Notice that the R3 variable is passed into this function where it can be used to set the MyFunc to an added RFC using the R3.Add method; the string parameter of the method is the BAPI to be called. Once the MyFunc object is set, the values to be passed into the BAPI can be populated using the MyFunc.exports method.

    Set MyFunc = R3.Add("BAPI_MATERIAL_GETALL")
    
    'Set up the values to be exported to the BAPI...
    Set MATERIAL = MyFunc.exports("MATERIAL")
    Set SALESORGANISATION = MyFunc.exports("SALESORGANISATION")
    Set DISTRIBUTIONCHANNEL = MyFunc.exports("DISTRIBUTIONCHANNEL")
    
    'Set up the variable to be imported from the BAPI...
    MATERIAL.Value = strMATNR
    SALESORGANISATION.Value = strVKORG
    DISTRIBUTIONCHANNEL.Value = strVTWEG

Notice how these ".exports" parameters in the MyFunc objects are linked to the input parameters of the BAPI as seen in SE37. The return values are in the form of internal tables; the BAPIRET2 and MATERIALTEXT objects are set after the function is called.

This code is called after the BAPI to tease the returned values from the MATERIALTEXT object. The values are passed back as an internal table so it is required to get the name of each Column and then use the Column names to get the values from each row.

    nRowCount = MATERIALTEXT.Rows.Count
    
    If nRowCount > 0 Then
        'There is some work to do.
        Set wsOutput = GetTab("Temp") 'Okay to use this sheet.
        
        'Set up the headers in the output sheet.
        wsOutput.Range("F1").Value = "MATNR"
        wsOutput.Range("G1").Value = "VKORG"
        wsOutput.Range("H1").Value = "VTWEG"
        nColumns = 0
        For Each objROW In MATERIALTEXT.Columns
            retval = DoEvents
            'Put the name of the Column in the first row of the output sheet.
            wsOutput.Range(Chr(73 + nColumns) & "1").Value = objROW.NAME
            nColumns = nColumns + 1
        Next
        
        'Find the first available row.
        For nCurrentRow = 2 To 1048576
            If wsOutput.Range("F" & nCurrentRow) = "" Then
                Exit For  'Next available row.
            End If
        Next
    
        For Each objROW In MATERIALTEXT.Rows
            If objROW("APPLOBJECT") = "MVKE" Then 'Sales Text!
                wsOutput.Range("F" & nCurrentRow & ":Q" & nCurrentRow).NumberFormat = "@" 'Text
                For nCurrentHeader = 0 To nColumns - 1
                    'wsOutput.Range(Chr(73 + nCurrentHeader) & nCurrentRow).NumberFormat = "@" 'Text
                    wsOutput.Range(Chr(73 + nCurrentHeader) & nCurrentRow).Value = objROW(wsOutput.Range(Chr(73 + nCurrentHeader) & "1").Value)  'Pass in the field name.
                Next
                wsOutput.Range("F" & nCurrentRow).Value = strMATNR
                wsOutput.Range("G" & nCurrentRow).Value = strVKORG
                wsOutput.Range("H" & nCurrentRow).Value = strVTWEG
                nCurrentRow = nCurrentRow + 1 'Move to the next line.
            End If
        Next
        
        
        GetMaterialTexts = nRowCount
        
    End If

Finally, the tables are cleared and each object is set to Nothing. The library retains these returned tables in memory if they are not cleared using the .Rows.RemoveAll method before setting the object to Nothing.

    'Clear out all of the values from memory.  If not then they're here the next time we call the function.
    MyFunc.Tables("RETURN").Rows.RemoveAll
    MyFunc.Tables("MATERIALTEXT").Rows.RemoveAll
    
    'Clean up the memory.
    Set BAPIRET2 = Nothing
    Set MATERIAL = Nothing
    Set SALESORGANISATION = Nothing
    Set DISTRIBUTIONCHANNEL = Nothing
    Set MATERIALTEXT = Nothing
    Set wsOutput = Nothing

Examples

Here are some other examples of RFCs being used to affect change or extract data from SAP.

• Import tables directly into Access from SAP using RFCs
• Read Table Data from SAP into Excel Using RFCs
• Mirror an SAP Server Onto a Local Computer Using VBA, RFCs and SQL Server Express
• Update Master Data with Excel by Calling RFCs

Here is the sample program used above. Naturally, it is provided without warranty, expressed or implied, so please use caution.
_MaterialMasterSalesAreaExtractRFC.xlsm


Know your parameters

SAP maintains a list of Parameters for every function in the FUPARAREF table. The PARAMTYPE field tells the programmer if the field is one of these three types.

• I -- Input parameter. These are used to pass values into the function as strings, numbers or as structures with multiple pieces of data.
• E -- Export parameter. These are used to pass values out of the function as strings, numbers or as structures with multiple pieces of data.
• T -- Table Parameter. These parameters are used to pass multiple values in a structured table. The structure of the table can be derived in the VBA debugger by looking at the Columns and Rows properties of the object or by interrogating the code in SE37.

The need for permissions

It is important to remember that the user account connecting to SAP must have access to call RFCs. Learn more about creating an RFC user in this article.