Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
Stefan-Schnell
Active Contributor
7,426
Batch Input is a consolidated method for automatic mass input of data. You can find more information about Batch Input here and here.

Here now an example how to use Batch Input with VBA in Excel and the COM Connector (CCo).

The sub procedure Record stores all activities in an Excel table, look at the image below. It calls the function module BDC_RECORD_TRANSACTION.

The sub procedure Replay reads the activities from the Excel table and executes it via the function module RFC_CALL_TRANSACTION_USING.
'-Begin-----------------------------------------------------------------
'-
'- Visual Basic for Application Excel program to use SAP Batch Input
'- resp. Batch Data Communication (BDC)
'-
'- Author: Stefan Schnell
'-
'-----------------------------------------------------------------------

'-Directives----------------------------------------------------------
Option Explicit

'-Constants-----------------------------------------------------------
Const RFC_OK = 0

'-Sub Record----------------------------------------------------------
'-
'- Records the given transaction code and stores the activities in
'- the given Excel table.
'-
'---------------------------------------------------------------------
Sub Record(TCode As String, TableName As String)

'-Variables-------------------------------------------------------
Dim SAP As CCo.COMNWRFC
Dim hRFC As Long
Dim hFuncDesc As Long
Dim hFunc As Long
Dim rc As Integer
Dim hTable As Long
Dim RowCount As Long
Dim i As Integer
Dim hRow As Long
Dim charBuffer As String

Set SAP = CreateObject("COMNWRFC")
If IsObject(SAP) Then

hRFC = SAP.RfcOpenConnection("ASHOST=NSP, SYSNR=00, " & _
"CLIENT=001, USER=BCUSER, USE_SAPGUI=2")

If hRFC Then

hFuncDesc = SAP.RfcGetFunctionDesc(hRFC, _
"BDC_RECORD_TRANSACTION")
If hFuncDesc Then

hFunc = SAP.RfcCreateFunction(hFuncDesc)
If hFunc Then

rc = SAP.RfcSetChars(hFunc, "TCODE", TCode)
rc = SAP.RfcSetChars(hFunc, "MODE", "A")
rc = SAP.RfcSetChars(hFunc, "UPDATE", "A")
rc = SAP.RfcSetChars(hFunc, "AUTHORITY_CHECK", "X")

If SAP.RfcInvoke(hRFC, hFunc) = RFC_OK Then

rc = SAP.RfcGetTable(hFunc, "DYNPROTAB", hTable)
rc = SAP.RfcGetRowCount(hTable, RowCount)
rc = SAP.RfcMoveToFirstRow(hTable)

Worksheets(TableName).Cells.Clear
For i = 1 To RowCount
hRow = SAP.RfcGetCurrentRow(hTable)
rc = SAP.RfcGetChars(hRow, "PROGRAM", charBuffer, 40)
Worksheets(TableName).Range("A" & CStr(i)) = _
Trim(charBuffer)
rc = SAP.RfcGetChars(hRow, "DYNPRO", charBuffer, 4)
Worksheets(TableName).Range("B" & CStr(i)) = _
"'" & Trim(charBuffer)
rc = SAP.RfcGetChars(hRow, "DYNBEGIN", charBuffer, 1)
Worksheets(TableName).Range("C" & CStr(i)) = _
Trim(charBuffer)
rc = SAP.RfcGetChars(hRow, "FNAM", charBuffer, 132)
Worksheets(TableName).Range("D" & CStr(i)) = _
Trim(charBuffer)
rc = SAP.RfcGetChars(hRow, "FVAL", charBuffer, 132)
Worksheets(TableName).Range("E" & CStr(i)) = _
"'" & Trim(charBuffer)
If i < RowCount Then
rc = SAP.RfcMoveToNextRow(hTable)
End If
Next

End If

rc = SAP.RfcDestroyFunction(hFunc)
End If

End If

rc = SAP.RfcCloseConnection(hRFC)
End If

Set SAP = Nothing
End If

End Sub

'-Sub Replay----------------------------------------------------------
'-
'- Replays the given transaction code with the activities from the
'- given Excel table, in the specified mode.
'-
'- Modes: A = All screens are displayed
'- E = Screens are not displayed, except if an error occurs
'- N = Screens are not displayed
'- P = Same as N, but if a breakpoint is reached the classic
'- debugger is used
'-
'---------------------------------------------------------------------
Sub Replay(TCode As String, TableName As String, Mode As String)

'-Variables-------------------------------------------------------
Dim SAP As CCo.COMNWRFC
Dim hRFC As Long
Dim hFuncDesc As Long
Dim hFunc As Long
Dim rc As Integer
Dim hTable As Long
Dim i As Integer
Dim hRow As Long

Set SAP = CreateObject("COMNWRFC")
If IsObject(SAP) Then

hRFC = SAP.RfcOpenConnection("ASHOST=NSP, SYSNR=00, " & _
"CLIENT=001, USER=BCUSER, USE_SAPGUI=2")
If hRFC Then

hFuncDesc = SAP.RfcGetFunctionDesc(hRFC, _
"RFC_CALL_TRANSACTION_USING")
If hFuncDesc Then

hFunc = SAP.RfcCreateFunction(hFuncDesc)
If hFunc Then

rc = SAP.RfcSetChars(hFunc, "TCODE", TCode)
rc = SAP.RfcSetChars(hFunc, "MODE", Mode)
rc = SAP.RfcGetTable(hFunc, "BT_DATA", hTable)
rc = SAP.RfcDeleteAllRows(hTable)

For i = 1 To Worksheets(TableName).UsedRange.Rows.Count
hRow = SAP.RfcAppendNewRow(hTable)
rc = SAP.RfcSetChars(hRow, "PROGRAM", _
Worksheets(TableName).Range("A" & CStr(i)))
rc = SAP.RfcSetChars(hRow, "DYNPRO", _
Worksheets(TableName).Range("B" & CStr(i)))
rc = SAP.RfcSetChars(hRow, "DYNBEGIN", _
Worksheets(TableName).Range("C" & CStr(i)))
rc = SAP.RfcSetChars(hRow, "FNAM", _
Worksheets(TableName).Range("D" & CStr(i)))
rc = SAP.RfcSetChars(hRow, "FVAL", _
Worksheets(TableName).Range("E" & CStr(i)))
Next

rc = SAP.RfcInvoke(hRFC, hFunc)

rc = SAP.RfcDestroyFunction(hFunc)
End If

End If

rc = SAP.RfcCloseConnection(hRFC)
End If

Set SAP = Nothing
End If

End Sub

'-Main----------------------------------------------------------------
Sub Main()

Record "SE16", "Tabelle1"

Replay "SE16", "Tabelle1", "A"

End Sub

'-End-------------------------------------------------------------------

After the recording the table looks like this:



It is exactly the same as in the Transaction Recorder SHDB:



But now you have the possibility to modify the table easily, e.g. to duplicate entries - Excel-like.
38 Comments
Labels in this area