Upload data into SAP S/4HANA Cloud CBO from MS Exc... - SAP Community

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