PDA

View Full Version : Using XMLHTTP to upload a file to API



Wudsy
06-15-2015, 05:14 PM
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", [ur_l], False, [username], [password]
Req.setRequestHeader "Content-Type", "text/xml"
Req.send ("QuoteRetrieveAttachmentKey" & _
"" & [stuff here] & "")

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", [ur_l], 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 & _

[DATA NEEDS TO GO HERE] & _

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!

Wudsy
06-22-2015, 09:37 PM
Found an answer to this question that helped me a lot.
Find the answer here (http://www.mrexcel.com/forum/excel-questions/861695-using-xmlhttp-upload-file-api.html#post4192153) and my findings below it to make it work.

Thank you again for your help, feel free to contact me if you are having a similar issue.
Can't promise Ill be much help though but I will try.

snb
06-23-2015, 12:18 AM
Post some more posts in this thread and after 5 you will be able to enter a link.

Wudsy
06-23-2015, 12:23 AM
Thank you - post 3

Wudsy
06-23-2015, 12:23 AM
Post 4

Wudsy
06-23-2015, 12:24 AM
Post 5 - Thank you again :)

Wudsy
06-23-2015, 12:26 AM
Still says I can't - Post 6

Wudsy
06-23-2015, 12:27 AM
Will try to 10 then stop spam. Post 7

Wudsy
06-23-2015, 12:28 AM
Post 8

Wudsy
06-23-2015, 12:31 AM
Post 9

Wudsy
06-23-2015, 12:32 AM
Lucky post number 10! - Seems it was 10 :)

snb
06-23-2015, 12:36 AM
Your link won't get me anywhere ?

Wudsy
06-23-2015, 12:41 AM
Works for me.
http://www.mrexcel.com/forum/excel-questions/861695-using-xmlhttp-upload-file-api.html#post4192153