Using XMLHTTP to upload a file to API

[Wudsy]

I am developing a tool to assist my sales team in the quotation process.

Majority of our quotation is done in Excel, as part of when the quote is generated it saves the quote as a PDF and I would like it to automatically upload all the quote data and a copy of the PDF to our CRM package using it's API.

I have done this using a VBA script and have managed to get everything to work except the file upload (so it creates a new quote, fills in the customer, items etc).

The upload is done in two parts, first I must retrieve an attachment key, secondly I use that key to upload the file.

The code I am using is as follows, the [ ]'s are where I've removed stuff:

Public Function UploadQuote()

    Dim Req As New XMLHTTP

    ' retrieve attachment key    
    Req.Open "POST", [url], False, [username], [password]
    Req.setRequestHeader "Content-Type", "text/xml"
    Req.send ("<methodCall><methodName>QuoteRetrieveAttachmentKey</methodName>" & _
    "<params><param>" & [stuff here] & "</param></params></methodCall>")
    
    Dim AttachmentKey As String
    AttachmentKey = Req.responseXML.ChildNodes.Item(1).[few more].Text
      
    Dim FileName As String
    FileName = "C:\pdf-test.pdf" 'this is for my testing purposes
    
    ' upload the file  
    Req.Open "POST", [url], False, [username], [password]
    Req.setRequestHeader "Content-Type", "multipart/form-data; boundary=[boundary]"
    Req.send ("--[boundary]" & vbCrLf & _
    "Content-Disposition: form-data; name=""File""; filename=""" & FileName & """" & vbCrLf & _
    "Content-Type: application/pdf" & _
    vbCrLf & vbCrLf & _

    [B][COLOR="#FF0000"][DATA NEEDS TO GO HERE][/COLOR][/B] & _

    vbCrLf & "[boundary]" & vbCrLf & _
    "Content-Disposition: form-data; name=""AttachmentKey""" & _
    vbCrLf & vbCrLf & AttachmentKey & vbCrLf & "[boundary]--")
    
    MsgBox Req.responseText
    
 End Function

Using plain text I can upload a .txt file and that works fine (get success message and can access the file from the CRM).

However I want to be uploading PDF files.

When I try any of the methods I have been able to find online for converting the data, the upload 'is successful' yet the data when I access the file from the CRM package is corrupt (and a lot larger than the original file (20kbs become around 80kbs)).

Using Postman I am able to upload the file so I know the API and process works. (If anyone knows what Postman does and convert that to VBA!)

How / what am I meant to do to the file to get it into a form I can send the data via XMLHTTP??

I have no idea what I am doing and everything I have managed to get working so far is already due to people online so thank you in advanced!

 

[John_w]

You need a function which reads a binary file into a byte array or string. There are several ways of doing it, including pure VBA (no external libraries needed), but try the GetFile function at Automatic file upload using IE+ADO without user interaction - VBSscript, Also, it looks like your end boundary is wrong; it should be "--[boundary]--".

Cross-posted http://stackoverflow.com/questions/30692302/using-xmlhttp-to-upload-a-file-to-api-with-vba-in-excel. When posting the same question on multiple forums always provide links to every other post. Why? Please read http://www.excelguru.ca/content.php?184.

 

[Wudsy]

First of all John - thank you so so much! I really mean it that I wouldn't have got anywhere with out the help of people online.
Also - lesson learnt. Will make sure to follow the cross post rules in future. Will also update other posts with my findings here.

For anyone else with a similar problem.

Someone will probably need to correct me on a few things here but this is what I believe:

The issue I had is that I was sending the data as a string.
I was using the following method to read the file data as a byte array:

    Dim FileName As String    FileName = 'filename goes here
    Dim FileContents() As Byte, FileNumber As Integer
    ReDim FileContents(FileLen(FileName) - 1)
    FileNumber = FreeFile
    Open FileName For Binary As FileNumber
    Get FileNumber, , FileContents
    Close FileNumber
    Dim sPostData As String
    sPostData = StrConv(FileContents, vbUnicode)

Now I don't believe there is any issue with the above method.

However when posted in my Req.Send(lots of stuff here) it was encoding the characters found in my sPostData as UTF-8.
This meant any byte of value 0 - 127 was fine, however anything greater was encoded in two bytes (reason why here). As the server was just looking as binary stream, this made no sense and resulted in larger (corrupt) files. (There were also a few bytes that it wouldnt accept)

The key part I used from what John shared was the following:

[COLOR=green]'Build multipart/form-data document with file contents [/COLOR][COLOR=blue]And[/COLOR][COLOR=green] header info[/COLOR]Function BuildFormData(FileContents, Boundary, FileName, FieldName)
  [COLOR=blue]Dim[/COLOR] FormData, Pre, Po
  [COLOR=blue]Const[/COLOR] ContentType = [COLOR=brown]"application/upload"[/COLOR]
  
  [COLOR=green]'The two parts around file contents [COLOR=blue]In[/COLOR] the multipart-form data.
[/COLOR]  Pre = [COLOR=brown]"--"[/COLOR] + Boundary + [COLOR=blue]vbCrLf[/COLOR] + mpFields(FieldName, FileName, ContentType)
  Po = [COLOR=blue]vbCrLf[/COLOR] + [COLOR=brown]"--"[/COLOR] + Boundary + [COLOR=brown]"--"[/COLOR] + [COLOR=blue]vbCrLf[/COLOR]
  
  [COLOR=green]'Build form data using recordset binary field
[/COLOR]  [COLOR=blue]Const[/COLOR] adLongVarBinary = 205
  [COLOR=blue]Dim[/COLOR] RS: [COLOR=blue]Set[/COLOR] RS = [COLOR=darkblue]CreateObject[/COLOR]([COLOR=brown]"ADODB.Recordset"[/COLOR])
  RS.Fields.Append [COLOR=brown]"b"[/COLOR], adLongVarBinary, [COLOR=blue]Len[/COLOR](Pre) + [COLOR=blue]LenB[/COLOR](FileContents) + [COLOR=blue]Len[/COLOR](Po)
  RS.Open
  RS.AddNew
    [COLOR=blue]Dim[/COLOR] LenData
    [COLOR=green]'Convert Pre string value [COLOR=blue]To[/COLOR] a binary data
[/COLOR]    LenData = [COLOR=blue]Len[/COLOR](Pre)
    RS([COLOR=brown]"b"[/COLOR]).AppendChunk (StringToMB(Pre) & [COLOR=blue]ChrB[/COLOR](0))
    Pre = RS([COLOR=brown]"b"[/COLOR]).GetChunk(LenData)
    RS([COLOR=brown]"b"[/COLOR]) = [COLOR=brown]""[/COLOR]
    
    [COLOR=green]'Convert Po string value [COLOR=blue]To[/COLOR] a binary data
[/COLOR]    LenData = [COLOR=blue]Len[/COLOR](Po)
    RS([COLOR=brown]"b"[/COLOR]).AppendChunk (StringToMB(Po) & [COLOR=blue]ChrB[/COLOR](0))
    Po = RS([COLOR=brown]"b"[/COLOR]).GetChunk(LenData)
    RS([COLOR=brown]"b"[/COLOR]) = [COLOR=brown]""[/COLOR]
    
    [COLOR=green]'Join Pre + FileContents + Po binary data
[/COLOR]    RS([COLOR=brown]"b"[/COLOR]).AppendChunk (Pre)
    RS([COLOR=brown]"b"[/COLOR]).AppendChunk (FileContents)
    RS([COLOR=brown]"b"[/COLOR]).AppendChunk (Po)
  RS.Update
  FormData = RS([COLOR=brown]"b"[/COLOR])
  RS.Close
  BuildFormData = FormData [COLOR=blue]End Function[/COLOR]

And

[COLOR=green]'Converts OLE string [/COLOR][COLOR=blue]To[/COLOR][COLOR=green] multibyte string[/COLOR]Function StringToMB(S)
  [COLOR=blue]Dim[/COLOR] I, B
  [COLOR=blue]For[/COLOR] I = 1 [COLOR=blue]To[/COLOR] [COLOR=blue]Len[/COLOR](S)
    B = B & [COLOR=blue]ChrB[/COLOR]([COLOR=blue]Asc[/COLOR]([COLOR=blue]Mid[/COLOR](S, I, 1)))
  [COLOR=blue]Next[/COLOR]
  StringToMB = B [COLOR=blue]End Function[/COLOR]

All credit goes here!


Now I can't actually tell you exactly what this does, but it presents the whole request as a byte array including the stuff before and after the file data, hence when it sends the stream, there is no encoding.

I use the function:

Req.send (FormData)

And it works!


Thank you again John. Wish I was able to buy you lunch.