Excel VBA Post File and Json body to Azure Function

Question

I am tasked with a problem of using a Macro Enabled Excel file that needs to collect json and upload them to an ftp location.

Yesterday trying a number of things with FTP, found that this wasnt going to be a smooth ride for various reasons. Biggest one is that some people dont have rights to install on their machines and some things need to be plugged in.

Therefore I opted to use an Azure Function that will receive the file and a JSON object as part of a multipart form.

Testing in Postman worked a charm, however, when making the request from VBA I am having some issues.

Please see the code below and let me know where I have gone wrong.

Sub SendPOSTRequestWithFileAndBody(ByVal filePath As String, remoteFileLocation As String)

    ' Set request URL, file path, and JSON body
    Dim url As String
    url = "https://xxxx.azurewebsites.net/api/UploadToFtp"
    Dim jsonBody As String
    Dim jsonPayload As String
    jsonPayload = "{""remoteFileLocation"" : """ + remoteFileLocation + """}"
    jsonBody = "{""body"" : """ + jsonPayload + """}"
    
    
    ' Create XMLHTTP object and set properties
    Dim httpRequest As Object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "POST", url, False
       
    httpRequest.setRequestHeader "Content-Type", "multipart/form-data; boundary=---------------------------7d82751e2bc0858"
    
    ' Set payload (file and body)
    Dim payload As String
    payload = "-----------------------------7d82751e2bc0858" & vbCrLf
    payload = payload & "Content-Disposition: form-data; name=""file""; filename=""" & filePath & """" & vbCrLf
    payload = payload & "Content-Type: application/octet-stream" & vbCrLf & vbCrLf
    payload = payload & Stream_File(filePath) & vbCrLf
    payload = payload & "-----------------------------7d82751e2bc0858" & vbCrLf
    payload = payload & "Content-Disposition: form-data; name=""body""" & vbCrLf
    payload = payload & "Content-Type: application/json" & vbCrLf & vbCrLf
    payload = payload & jsonBody & vbCrLf
    payload = payload & "-----------------------------7d82751e2bc0858--" & vbCrLf
    
    
    ' Send request and get response
    httpRequest.send payload
    Dim responseText As String
    responseText = httpRequest.responseText
    
End Sub

Function Stream_File(ByVal sFile As String) As String

    ' Read file into a binary stream and return as string
    Dim binStream As Object
    Set binStream = CreateObject("ADODB.Stream")
    binStream.Type = 1 ' Binary
    binStream.Open
    binStream.LoadFromFile sFile
    Stream_File = binStream.Read(binStream.Size)
    binStream.Close

End Function

When it hits the function from VBA I have no form object to check as per this example here

var ftpSettings = JsonConvert.DeserializeObject<FtpSettings>(req.Form.FirstOrDefault().Value);

Which results in the function falling over.

Thanks in advance of support

Excel VBA Post File and Json body to Azure Function

0 Answers0