如何将参数从EXCEL VBA传递到SAP BAPI [英] How to pass parameters to SAP BAPI from EXCEL VBA
问题描述
关于BAPI的参数有很多描述，但是它们是无法理解的. 我将解决方案发布在这里，因为我本人在实现EXCEL VBA的参数化BAPI调用方面遇到许多困难.
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.
我的任务是查询SAP.应该有可能(在Excel中通过表单)选择
-日期范围
-不同的状态(例如10、20、60)
-不同的部门代码(例如10、20、60)
-是否显示Z800项目
My task was to query SAP. 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
要访问BAPI，您必须达到以下条件:
To access the BAPI, you have to achieve the following:
- Establish a connection to your SAP 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)
如果您看到BAPI对象在调试模式下包含BAPI名称，则此分配成功完成: 成功分配BAPI 如果分配不成功，则BAPI对象将包含"Nothing".
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".
- 使用参数调用BAPI 在任何地方，您都可以阅读导入"/导出"……等等.但是我只想知道，如何将用户选择传递给SAP? 最常见的用例是，用户在excel工作表中选择数据，然后启动一个程序，该程序根据用户的选择从SAP提取数据，这也是我的事情.
- 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? 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 based on the user selection, and this was mine, too.
BAPI可以具有2种不同的参数类型(每个参数都引用SAP视图中的一个显式字段): 1.仅传递1个值:这是.exporting(例如，姓="Smith") 2.传递了多个参数:这是通过表格进行的(例如，姓氏="Smith"或"Myer"或"Pitt"，订购日期> 4/1/2019并且< 4/15/2019) BAPI的输入/输出
The BAPI can have 2 distinguished parameter types (every param refers to one explicit field in the SAP view): 1. Only 1 value is passed: this is .exporting (e.g. surname = "Smith") 2. 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
输入和输出表具有相同的类型.您的SAP部门可以命名您的数据表. 要获取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
子代码:
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
导入/输入表 导入表的外观大致如下: SAP导入/输入表
Import / Input tables Import tables mostly look like: SAP import/input table
将单行组装为与OR连接的SQL-WHERE语句.输入表的说明:
The single lines are assembled as an SQL-WHERE Statement connected with OR. Description of the Input table:
Field Zeile(zh:行)
您必须提供行号(i ++，从1开始)！！！
Field Zeile (en: row)
You have to give the row number (i++, starting by 1) !!!
栏位签名
(仅)可能的值:
I:包含范围在内
E:专属定义范围
Field SIGN
(Only) possible values:
I: inclusive defined range
E: exclusive defined range
字段OPTION的可能值(=运算符):
-EQ等于(=低)
-NE不等于(<>低)
-BT在低和高之间，包括两者(低< = x< =高)
-NB在低和高之外(x <低和x>高)
-CP包含图案
-NP不包含图案
-LT低于(
-LE较低等于(< =低)
-GT大于(>低)
-GE大于等于(> =低)
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 (
- LE Lower equal (<= Low)
- GT Greater than (> low)
- GE Greater equal (>= low)
字段LOW的可能值
取决于引用字段的数据类型.范例:
Possible values for Field LOW
Depends on the data type of the referring field. Examples:
- [C(2)] =>字符串
- [D(8)]是一个日期，您应该将其作为字符串传输，格式为"yyyyMMdd"
- [N(2)] =>整数
字段高"的可能值
- 为空，但OPTION为BT或NB
数据类型
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
- 日期范围(在SAP中，==>日期类型为IT_WORKD_RANGE，LOW和HIGH的表)
- 不同状态(在SAP中==>名为IT_STATUS_RANGE，CHAR 2类型的LOW和HIGH的表)
- 不同部门(在SAP中==>名为IT_ZZIDL_RANGE，类型为NUMC 2的LOW和HIGH的表)
- 是否显示Z800项目(否)(在SAP中，= IF> BOOLEAN名为IF_AWART，导出参数为"X"或空白，其中空白为默认值，表示FALSE)
解决方案
单击工作表中的按钮后，将显示一个表单.输入上面所有可能的值，然后将其写入工作表"Connection"，B9:B13
After Click on a Button in a sheet a form is shown. All of the possible values above are input and then written to sheet "Connection", B9:B13
B9: 20190401
B10: 20190701
B11: 10;20;40
B12: X
B13: 05;08;11
和代码:
Function ReadCatsData()
On Error GoTo ReadCatsDataError
Dim MyWB As Workbook
Dim MyWS As Worksheet
Dim connection As Object
Dim SAP_System As String
Dim WinUser As String
Dim functions As Object
Dim ErrText As String
Dim ErrTitel As String
Dim BapiName As String
Dim DatumVon As String
Dim DatumBis As String
Dim Status As String
Dim Z8 As String
Dim ILC As String
Dim Result() As String
Dim a As Integer
Dim i As Integer
Dim obSapBapi As Object
'Basiswerte setzen:
ErrTitel = "Monitoring times"
WinUser = UCase(Environ$("Username"))
SAP_System = ActiveWorkbook.Sheets("Connection").Cells(2, 1)
BapiName = "Z_BAPI_CATS_MON_GET"
Set MyWB = ActiveWorkbook
Set MyWS = MyWB.Worksheets("SapDaten")
Debug.Print SAP_System, WinUser, BapiName
Set connection = sapConnectionLogon(SAP_System, WinUser) ' "SGI"
If Not (connection Is Nothing) Then
Set functions = sapFunctions(connection)
Set obSapBapi = functions.Add(BapiName)
If Not (obSapBapi Is Nothing) Then
DatumVon = Sheets("Connection").Cells(9, 2).Value
'DatumVon = "20190401"
DatumBis = Sheets("Connection").Cells(10, 2).Value
'DatumBis = "20190418"
Status = Sheets("Connection").Cells(11, 2).Value
'Status = "10;20;30"
Z8 = Sheets("Connection").Cells(12, 2).Value
ILC = Sheets("Connection").Cells(13, 2).Value
'ILC = "05;08"
Debug.Print DatumVon, DatumBis, Status, Z8, ILC
'Declare the Table Parameters => Uebergabewerte
' Date table (1)
Dim vbIT_WORKD_RANGE As Object
Set vbIT_WORKD_RANGE = obSapBapi.Tables("IT_WORKD_RANGE")
vbIT_WORKD_RANGE.Rows.Add
vbIT_WORKD_RANGE(1, "SIGN") = "I"
vbIT_WORKD_RANGE(1, "OPTION") = "BT"
vbIT_WORKD_RANGE(1, "LOW") = DatumVon
vbIT_WORKD_RANGE(1, "HIGH") = DatumBis
' Status (2)
If Status <> "" Then
Dim vbIT_STATUS_RANGE As Object
Set vbIT_STATUS_RANGE = obSapBapi.Tables("IT_STATUS_RANGE")
Result = Split(Status, ";")
For i = LBound(Result()) To UBound(Result())
vbIT_STATUS_RANGE.Rows.Add
vbIT_STATUS_RANGE(vbIT_STATUS_RANGE.Rows.Count, _
"SIGN") = "I"
vbIT_STATUS_RANGE(vbIT_STATUS_RANGE.Rows.Count, _
"OPTION") = "EQ"
vbIT_STATUS_RANGE(vbIT_STATUS_RANGE.Rows.Count, _
"LOW") = Result(i)
Debug.Print i, Result(i)
Next i
End If
Erase Result
' Industry_line (3)
If ILC <> "" Then
Dim vbIT_ZZIDL_RANGE As Object
Set vbIT_ZZIDL_RANGE = obSapBapi.Tables("IT_ZZIDL_RANGE")
Result = Split(ILC, ";")
For i = LBound(Result()) To UBound(Result())
vbIT_ZZIDL_RANGE.Rows.Add
vbIT_ZZIDL_RANGE(vbIT_ZZIDL_RANGE.Rows.Count, _
"SIGN") = "I"
vbIT_ZZIDL_RANGE(vbIT_ZZIDL_RANGE.Rows.Count, _
"OPTION") = "EQ"
vbIT_ZZIDL_RANGE(vbIT_ZZIDL_RANGE.Rows.Count, _
"LOW") = Val(Result(i))
Debug.Print i, Result(i)
Next i
End If
'Declare the Export Parameter
' show Z800 projects (4)
obSapBapi.exports("IF_AWART") = Z8
'Call the function. get error in Bapi via obSapBapi.Exception
If obSapBapi.Call = False Then
ErrText = "Established connection with SAP system " & _
SAP_System & vbCrLf
ErrText = ErrText & "BAPI " & BapiName & " exists. " & vbCrLf
ErrText = ErrText & "BAPI could not be called. " & vbCrLf
ErrText = ErrText & "SAP delivers the following message: " & _
obSapBapi.Exception & vbCrLf
ErrText = ErrText & "Data cannot be fetched from SAP." & vbCrLf
a = MsgBox(ErrText, vbOKOnly + vbCritical, ErrTitel)
Else
'Function Call is Successfull
Dim obSAPTblData As Object
Set obSAPTblData = obSapBapi.Tables("ET_Data")
Dim SheetRowPos As Integer
Dim iRowCnt As Integer
Dim iRowIndx As Integer
Dim iColCnt As Integer
Dim iColIndx As Integer
iColCnt = obSAPTblData.ColumnCount
iRowCnt = obSAPTblData.RowCount
SheetRowPos = 1 'init
For iRowIndx = 1 To iRowCnt
SheetRowPos = SheetRowPos + 1 'start at row 2
For iColIndx = 1 To iColCnt
MyWS.Cells(SheetRowPos, _
iColIndx) = obSAPTblData.Value(iRowIndx, iColIndx)
Next
Next
'Debug.Print "Col.: " & iColCnt & ", Rows: " & iRowCnt
End If
Set vbIT_WORKD_RANGE = Nothing
Set vbIT_STATUS_RANGE = Nothing
Set vbIT_ZZIDL_RANGE = Nothing
functions.Remove (BapiName)
Set obSapBapi = Nothing
Else
ErrText = "Established connection with SAP system " & _
SAP_System & vbCrLf
ErrText = ErrText & "BAPI " & BapiName & _
" was not found (could not instantiate object)." & vbCrLf
ErrText = ErrText & "Data cannot be fetched from SAP." & vbCrLf
a = MsgBox(ErrText, vbOKOnly + vbCritical, ErrTitel)
End If
connection.LogOff
Set connection = Nothing
Set functions = Nothing
Else
ErrText = "Could not establish connection with SAP system." & vbCrLf
ErrText = ErrText & "User: " & WinUser
ErrText = ErrText & ", SAP System: " & SAP_System & vbCrLf
ErrText = ErrText & "Data cannot be fetched from SAP." & vbCrLf
a = MsgBox(ErrText, vbOKOnly + vbCritical, ErrTitel)
End If
ReadCatsDataExit:
Set MyWS = Nothing
Set MyWB = Nothing
Exit Function
ReadCatsDataError:
Resume ReadCatsDataExit
End Function
推荐答案
您提到的外部文档中未解释的某些问题是，在7.70之前的SAP GUI版本(当前为beta，尚未公开)中，SAP GUI脚本OCX库是32位.许多人使用MS Office 64位，因此您必须按照
Something not explained in the external document you mention, is that in versions of SAP GUI before 7.70 (currently beta, not public yet) the SAP GUI Scripting OCX libraries are 32 bits. Many people use MS Office 64 bits, so you must change the Windows Registry of a few SAP GUI entries as described there.
如果调用涉及称为 BAPIs 的特殊RFC功能模块，则要创建，更新或删除数据，您需要分别根据显示的成功或失败来调用BAPI_TRANSACTION_COMMIT或BAPI_TRANSACTION_COMMIT参数RESULT的消息(如果存在类型为E，A或X的消息).请注意，objRfcFunc.Call = False表示系统错误.
If the call concerns special RFC function modules called BAPIs, to create, update or delete data, you need to call BAPI_TRANSACTION_COMMIT or BAPI_TRANSACTION_COMMIT based on respectively the success or the failure indicated in the messages of the parameter RESULT (if presence of a message of type E, A or X). Note that objRfcFunc.Call = False means a system error.
这篇关于如何将参数从EXCEL VBA传递到SAP BAPI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
