Skip to Content
Author's profile photo Marty McCormick

Upload data into SAP S/4HANA Cloud CBO from MS Excel

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

Assigned Tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Glen Leslie
      Glen Leslie

      very cool!

      Author's profile photo Hemsagar Elugu
      Hemsagar Elugu

      Thanks for this

      Author's profile photo teja aravind
      teja aravind

      thanks for valuable information buddy really loved ur stuff

      https://dynamoinfotech.com/sap-s4hana-cloud/

      Author's profile photo Harish Choudhary
      Harish Choudhary

       

      Awesome stuff put together Marty

      Author's profile photo Pascal Renet
      Pascal Renet

      Thumbs up !

      Author's profile photo Murtada Alkinani
      Murtada Alkinani

      Great Blog Marty!

      Author's profile photo Raju Mammula
      Raju Mammula

      Hello Marty,

       

      I am unable to fetch x-csrf-token in the response, please guide me .

       

       

      Raju Mammula

      Author's profile photo Marty McCormick
      Marty McCormick
      Blog Post Author

      Hi Raju

      I updated the calling method, I think an Excel patched changed the way we have to get the x-csrf-token.  Can you try with the Msxml2.XMLHTTP.6.0 object as updated in the blog and let me know?  Several statements change in that section...

      Thanks,
      Marty

      Author's profile photo Jorge Cardós Villanueva
      Jorge Cardós Villanueva

      Hi Marty McCormick

      First of all, thank you very much for sharing this information with us!

      I have try with the Msxml2.XMLHTTP.6.0 object, but I am not able to get the token in the response.

      Do you have any idea why this is happening?

      Thank you very much in advance for your comments,

      Best regards,

      Jorge

       

       

      Author's profile photo Rosaria Di Paola
      Rosaria Di Paola

      Hi Jorge. Were you able to get the token in the response?

       

      Regards
      Rosaria

      Author's profile photo Zhengliang Li
      Zhengliang Li

      Hi Marty,

       

      I also cannot get the token value, could you please give some ideas for me.

      Thanks.

      Best Regards,

      Ranson

      Author's profile photo Naresh Jangu
      Naresh Jangu

      Hello Marty ,

       

      Good article …would it be possible to upload the data through Excel to the standard Objects ( let’s say Purchase requistion) using ODATA API? ( It has the dedicated Sevice URL API)

       

       

      RegardS

      Naresh J

      Author's profile photo Marty McCormick
      Marty McCormick
      Blog Post Author

      Hi Naresh

       

      Yes, any OData API could follow this approach.

      Thanks,
      Marty

      Author's profile photo Zhengliang Li
      Zhengliang Li

      Hello Marty,

       

      I also cannot get token, could you please help to give some suggestions for me.

      Thanks.

      Best Regards,

      Ranson

       

       

      Author's profile photo Raju Mammula
      Raju Mammula

      Hello Marty ,

       

      How do we handle date to be uploaded ?

      If i provide the date in any formats like "2020-08-12 " or "2020/08/12" it is throwing conversion error

      If i provide the date in EDM format "2020-08-12T00:00:00" or /13456745676/" it is throwing some offset error stating it has Invalid value .

       

      Can you help if you have any idea on this .

       

      Regards

      Raju Mammula

      Author's profile photo Johannes Bacher
      Johannes Bacher

      Ecxellent post and so helpful! Thank you a lot!

      Johannes

      Author's profile photo Rosaria Di Paola
      Rosaria Di Paola

      Hi Marty,

       

      I tried with the Msxml2.XMLHTTP.6.0 object, but I am not able to get the token in the response.

       

      Regards

      Rosaria

      Author's profile photo Ricardo Galicia
      Ricardo Galicia

      Hello Marty,

       

      Very Helpful and Excellent post.

      Is there any way that we can update existing entries in CBO using MS Excel? Via PUT Method?

       

      Thanks,

      Ricardo

       

      Author's profile photo Marty McCormick
      Marty McCormick
      Blog Post Author

      Hi Ricardo

       

      Yes, same logic could be used with PATCH method to update existing entities using the UUID for the record.

      Thanks,

      Marty

      Author's profile photo Ricardo Galicia
      Ricardo Galicia

      Thanks Marty,

      Appreciated your response.

      Changing 'POST' to 'PATCH' seems not working. How is UUID used to update rows? Is it possible to use the Key fields?

      Thanks,

      Ricardo

      Author's profile photo Naresh Reddy
      Naresh Reddy

      Hi Marty,

       

      Thanks for your effort and detailed explanation...!

      Can you please share the excel file for us to refer and create our own as per our requirement?

       

      Thank you in advance.