Enterprise Resource Planning Blogs by SAP
Get insights and updates about cloud ERP and RISE with SAP, SAP S/4HANA and SAP S/4HANA Cloud, and more enterprise management capabilities with SAP blog posts.
cancel
Showing results for 
Search instead for 
Did you mean: 
MartyMcCormick
Product and Topic Expert
Product and Topic Expert
8,131
In this blog, we'll build on the $batch OData feature introduced in the first blog in order to load data into a SAP S/4HANA Custom Business Object (CBO) from Microsoft Excel using a macro written in Visual Basic for Applications (VBA).  The same CBO from the previous blog will be used in this blog to upload the data.

This scenario would allow a user to create a spreadsheet of data for a CBO with the required fields and then click a button to trigger a macro which would upload and create the records in S/4HC using VBA.

Essentially, we will use VBA to create the same payload as we used in Postman in the first blog.  There are many ways to accomplish this task in VBA and this is one example.

The macro enabled file can be set up as follows:

Metadata Tab


On this tab, capture the technical fields that will be used to call the CBO via OData.  Namely, the URL, CBO Name, and Service User.

I also set up logic to allow the user to input the number of records that you want to process in one OData call (i.e. number of records per batch).  SAP recommends no more than 50 per batch.  In the example below, if I had 38 fields, there would be 4 update calls made to the CBO (10,10,10,8).


CBO Data Tab


This tab contains the field names (matching exactly to how they appear in the OData metadata including case sensitivity) and the actual data.



 

VBA Code


The next step is to write the VBA code to form the $batch requests.

There are three main pieces to the VBA.

The UploadDataToCBO function is the main function.  It determines the number of batches, makes calls to generate the batch payload using function CBOPayload and then calls the PostCBOData to submit the batch request to the S/4HANA Cloud system.

UploadDataToCBO Function


Function determines number fields, how many rows, whether to use multiple batches, asks user for the service user password, makes call for batch payload and ultimately calls the OData CBO API to post the data.
Sub UploadDataToCBO()
'
' UploadDataToCBO Macro


'
ClearResults

containsError = False
Dim resultRow As Integer
resultRow = 2
' Get the Number of Columns to set up the field names into a string array
Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

Dim fieldNames() As String
ReDim fieldNames(1 To lastColumn) As String

For d = 1 To lastColumn Step 1
fieldNames(d) = ActiveWorkbook.Sheets(SHEET_TITLE_CBO).Cells(1, d).Value

Next d
Dim CBOName As String
CBOName = ActiveWorkbook.Sheets(SHEET_TITLE_META).Cells(3, 2).Value

Dim numOfRows As Integer
numOfRows = ActiveWorkbook.Worksheets(SHEET_TITLE_CBO).Cells(Worksheets(SHEET_TITLE_CBO).Rows.Count, "A").End(xlUp).Row - 1

Dim cboCount As Integer
Dim batchAmount As Integer
Dim batchCallNum As Integer
Dim response As String

batchCallNum = 1
batchAmount = Sheets(SHEET_TITLE_META).Cells(5, 2).Value

'set user data
Dim strPass As String
Dim strUser As String
' Set up Data to call
strUsr = ActiveWorkbook.Sheets(SHEET_TITLE_META).Cells(4, 2).Value


strPass = InputBox("Enter Password for user " & strUsr)
strURL = Sheets(SHEET_TITLE_META).Cells(2, 2).Value & "/$batch"

Dim arr
Dim doneProcCBO As Boolean
Dim startRow As Integer
Dim endRow As Integer
Dim currRow As Integer
Dim totalBatches As Integer

currRow = 2
startRow = 2
doneProcCBO = False
totalBatches = numOfRows / batchAmount

If numOfRows Mod batchAmount > 0 Then
totalBatches = totalBatches + 1
End If

If MsgBox("Application will now process " & numOfRows & " records in " & totalBatches & " total batches." & vbCrLf & "Please be patient and check the status bar for progress." & vbCrLf & "Click Yes to proceed.", vbYesNo) = vbYes Then

If numOfRows > batchAmount Then
endRow = batchAmount + startRow - 1
Do
Application.StatusBar = "Processing: Batch " & batchCallNum & " - " & Format(batchCallNum / totalBatches, "Percent")
payload = CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn)
If debugMode = True Then
ActiveWorkbook.Worksheets(SHEET_TITLE_PAYLOAD).Shapes("Textbox " & batchCallNum).TextFrame.Characters.Text = payload
End If

response = PostCBOData(strURL, payload, strUsr, strPass, batchCallNum)


batchCallNum = batchCallNum + 1
payload = vbNullString
If currRow > numOfRows Then
doneProcCBO = True
End If
startRow = currRow
endRow = endRow + batchAmount


Loop While doneProcCBO = False


Else
' send all rows in the payload

endRow = numOfRows + 1
'generate Payload
payload = CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn)
response = PostCBOData(strURL, payload, strUsr, strPass, batchCallNum)


End If
Application.StatusBar = False
' PostCBOData(strURL,payload,strPass,batchCallNum,resultRow)
Else
'user clicked No button
End If

End Sub

CBOPayload Function


This function generates the batch payload.
Function CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn) As String

Dim payload As String
payload = "--batch_mybatch" & vbCrLf & "Content-Type: multipart/mixed; boundary=changeset_mychangeset1"


For e = currRow To endRow
If currRow <= numOfRows + 1 Then
payload = payload & vbCrLf & vbCrLf & "--changeset_mychangeset1" & vbCrLf & "Content-Type: application/http" & vbCrLf & "Content -Transfer - Encoding: binary" & vbCrLf & vbCrLf
payload = payload & "POST " & CBOName & " HTTP/1.1" & vbCrLf & "Content-Type: application/json" & vbCrLf & vbCrLf & "{" & vbCrLf

For f = 1 To lastColumn Step 1
payload = payload & Chr(34) & fieldNames(f) & Chr(34) & ": " & Chr(34) & ActiveWorkbook.Sheets(SHEET_TITLE_CBO).Cells(e, f).Value & Chr(34)

If f = lastColumn Then
payload = payload & vbCrLf & "}" & vbCrLf
Else
payload = payload & "," & vbCrLf
End If
Next f
End If

currRow = currRow + 1

Next e
payload = payload & vbCrLf & vbCrLf & vbCrLf & "--changeset_mychangeset1--" & vbCrLf & vbCrLf & vbCrLf & "--batch_mybatch--" & vbCrLf

CBOPayload = payload


End Function

PostCBOData Function


In this function, notice the first call to fetch the x-csrf-token before posting the data followed by the POST to update the CBO.
Function PostCBOData(strURL, strPostData, strUser, strPass, batchCallNum) As String

Set objWinHttp = CreateObject("Msxml2.XMLHTTP.6.0")
objWinHttp.Open strMethod, strURL, False, strUser, strPass
objWinHttp.SetRequestHeader "x-csrf-token", "Fetch"
objWinHttp.SetRequestHeader "Cache-Co­ntrol", "no-cache,max-age=0"
objWinHttp.SetRequestHeader "pragma", "no-cache"
objWinHttp.send

strToken = objWinHttp.getResponseHeader("x-csrf-token")

If Len(strToken) = 0 Then

MsgBox "Error: Most likely the password is not correct."
containsError = True
Else
objWinHttp.Open "POST", strURL, False, strUser, strPass
objWinHttp.SetRequestHeader "x-csrf-token", strToken
objWinHttp.SetRequestHeader "Content-Type", "multipart/mixed; boundary=batch_mybatch"
objWinHttp.send (strPostData)

PostCBOData = objWinHttp.ResponseText

If debugMode = True Then
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(1, 15).Value = PostCBOData
End If
'ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(1, 15 + batchCallNum).Value = PostCBOData
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 1).Value = "Batch " & batchCallNum


If InStr(PostCBOData, "HTTP/1.1 400 Bad Request") > 0 Then
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 2).Value = "ERROR"
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 3).Value = PostCBOData
Else
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 2).Value = "SUCCESS"

End If
Exit Function
badPassword: MsgBox "The password is not correct."


End Function

 

Execution and Results


Now it's time to execute the code.  I created a control sheet in a different tab with buttons to upload data and/or reset data.  Clicking on the "Upload New Data" button will call the UploadDataToCBO function.



 

Enter service password.



Confirm one last time that you want to upload the data



And then on the results tab we see the output of each batch.  If there was an error, the payload is logged in Column C and you can see what the problem was with the batch (for example, duplicate key).



And we can see the data in S/4HC



I hope you found this blog helpful.

Best Regards,
Marty
22 Comments