There exist a lot of descriptions on parameters for BAPIs, but they are not understandable.
I post the solution here, because I myself had many difficulties to achieve a parametrized BAPI call from Excel VBA.
My task was to query data from SAP software. It should be possible (in Excel via a Form) to select
- A date range
- Different states ( e.g. 10, 20, 60)
- Different department codes (e.g. 10, 20, 60)
- Whether to show Z800 projects or not
To access the BAPI, you have to achieve the following:
- Establish a connection to your SAP system instance (there are many examples on the net for this part, so I won’t explicate that, e.g. https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA ). In the example codes you will have something similar to
If Not (connection Is Nothing) Then
debug.print "connection is successful"
- Instantiate the BAPI (there too are many examples on the net for this part, so I will skip that too, e.g. https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA ) In the example codes you will have something similar to
Set functions = sapFunctions(connection)
Set obSapBapi = functions.Add(BapiName)
This assignment is successful, if you see the that the BAPI object contains the BAPI name in debug mode: Successful BAPI assignment If the assignment is not successful, the BAPI object contains "Nothing".
- Call the BAPI with parameters Everywhere you can read “importing” / “exporting”…… and a lot more. But all I wanted to know was, how do I pass the user selection to SAP system? The most common use case is, that a user selects data in an excel sheet and then starts a program, which fetches data from SAP system based on the user selection, and this was mine, too.
The BAPI can have 2 distinguished parameter types (every parameter refers to one explicit field in the SAP view):
- Only 1 value is passed: this is .exporting (e.g. surname = "Smith")
- More than 1 parameter is passed: this is via a table (e.g. surname = "Smith" or "Myer" or "Pitt", order date > 4/1/2019 and < 4/15/2019) Input / Output of a BAPI
Input and Output tables are of the same type. Your SAP department can name you which one is your data table. To get all the tables (and their fields) of a BAPI you can run this code (refers to input as well as output tables):
If obSapBapi.Call <> False Then
'Function Call is Successfull
Dim oTables As Object
Set oTables = obSapBapi.Tables 'Tables collection
Call GetColumnDetails(oTables)
Set oTables = Nothing
Debug.Print "Function Call is Successfull"
End If
The Code of the sub:
Private Sub GetColumnDetails(ByVal obTables As Object)
on error resume next
Dim iLoop As Integer, iColIndx As Integer, iColValuePos As Integer
Dim iTblCnt As Integer, iColCnt As Integer
dim 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
Sheet3.Cells(iValuePosn, 1) = oTable.Name
iValuePosn = iValuePosn + 1
For Each oColumn In oTable.Columns
Sheet3.Cells(iValuePosn, iColValuePos) = oColumn.Name
iColValuePos = iColValuePos + 1
Next oColumn
Set oTable = Nothing
Next
End Sub
Import / Input tables Import tables mostly look like: SAP import/input table
The single lines are assembled as an SQL-WHERE Statement connected with OR. Description of the Input table:
Field Zeile (en: row)
You have to give the row number (i++, starting by 1) !!!
Field SIGN
(Only) possible values:
I: inclusive defined range
E: exclusive defined range
Possible values for Field OPTION (=operator):
- EQ Equal (= Low)
- NE Not equal (<> Low)
- BT Between Low and High, including both (Low <= x <= High)
- NB Outside Low and High (x < Low and x> High)
- CP Contains pattern
- NP Does not contain pattern
- LT Lower than (<Low)
- LE Lower equal (<= Low)
- GT Greater than (> low)
- GE Greater equal (>= low)
Possible values for Field LOW
Depends on the data type of the referring field. Examples:
- [C(2)] => string
- [D(8)] is a date, you should transfer it as string with format “yyyyMMdd”
- [N(2)] => integer
Possible values for Field HIGH
- Is empty except OPTION is BT or NB
Data types
SAP BAPI Data Type / Description / Passed from VBA as
C (<len>) / Character / string
D(8) / Date / Format$(mydate,”yyyyMMdd”)
F(<len>) / float / Double
I(<len>) / Integer / Integer
N(<len>) / Numeric / Integer / long
B / Boolean / String (length 1)
Example: I described my task above. It should be possible to select
- A date range (=> in SAP system a Table named IT_WORKD_RANGE, LOW and HIGH of type date)
- Different states (=> in SAP system a Table named IT_STATUS_RANGE, LOW and HIGH of type CHAR 2, e.g. 10, 20, 60)
- Different departments (=> in SAP system a Table named IT_ZZIDL_RANGE, LOW and HIGH of type NUMC 2, e.g. 10, 20, 60)
- Whether to show Z800 projects or no (=> in SAP system a BOOLEAN named IF_AWART, an exporting parameter, "X" or blank, where blank is default and means FALSE)
