Using Macros to Upload Lots of Files

Wudsy

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

Majority of our quotation is done in Excel, equally part of when the quote is generated it saves the quote as a PDF and I would similar it to automatically upload all the quote information and a re-create of the PDF to our CRM packet using it'due south API.

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

The upload is done in two parts, kickoff I must retrieve an attachment central, secondly I utilize that primal to upload the file.

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

Code:

                              Public Office UploadQuote()      Dim Req As New XMLHTTP      ' retrieve attachment primal         Req.Open up "Mail", [url], False, [username], [password]     Req.setRequestHeader "Content-Type", "text/xml"     Req.send ("<methodCall><methodName>QuoteRetrieveAttachmentKey</methodName>" & _     "<params><param>" & [stuff hither] & "</param></params></methodCall>")          Dim AttachmentKey As Cord     AttachmentKey = Req.responseXML.ChildNodes.Item(1).[few more].Text            Dim FileName Equally Cord     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/class-data; boundary=[boundary]"     Req.send ("--[purlieus]" & vbCrLf & _     "Content-Disposition: form-data; proper noun=""File""; filename=""" & FileName & """" & vbCrLf & _     "Content-Type: application/pdf" & _     vbCrLf & vbCrLf & _      [B][Colour="#FF0000"][Information NEEDS TO GO HERE][/COLOR][/B] & _      vbCrLf & "[purlieus]" & vbCrLf & _     "Content-Disposition: form-data; name=""AttachmentKey""" & _     vbCrLf & vbCrLf & AttachmentKey & vbCrLf & "[boundary]--")          MsgBox Req.responseText       End Role

Using plain text I can upload a .txt file and that works fine (go success bulletin 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' withal the data when I access the file from the CRM bundle is corrupt (and a lot larger than the original file (20kbs go 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 catechumen that to VBA!)

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

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

Wudsy

Starting time of all John - thank you and so and then much! I actually mean information technology that I wouldn't have got anywhere with out the help of people online.
Besides - lesson learnt. Volition make certain to follow the cross post rules in time to come. Will also update other posts with my findings here.

For anyone else with a similar problem.

Someone volition probably need to correct me on a few things hither only this is what I believe:

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

Lawmaking:

                                                              Dim FileName As String    FileName = 'filename goes here     Dim FileContents() Equally Byte, FileNumber Every bit Integer     ReDim FileContents(FileLen(FileName) - ane)     FileNumber = FreeFile     Open FileName For Binary As FileNumber     Go FileNumber, , FileContents     Shut FileNumber     Dim sPostData Every bit Cord     sPostData = StrConv(FileContents, vbUnicode)

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

However when posted in my Req.Send(lots of stuff here) information technology 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 hither). As the server was only looking every bit binary stream, this made no sense and resulted in larger (corrupt) files. (There were as well a few bytes that it wouldnt accept)

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

Code:

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

And

Lawmaking:

                              [COLOR=green]'Converts OLE string [/COLOR][Colour=blue]To[/Colour][COLOR=dark-green] multibyte string[/Colour]Function StringToMB(S)   [Colour=blue]Dim[/COLOR] I, B   [COLOR=blue]For[/COLOR] I = 1 [Color=blueish]To[/COLOR] [COLOR=blue]Len[/COLOR](S)     B = B & [Colour=bluish]ChrB[/Colour]([COLOR=blue]Asc[/Color]([COLOR=bluish]Mid[/Color](S, I, 1)))   [COLOR=blue]Adjacent[/Color]   StringToMB = B [Colour=blue]Cease Office[/COLOR]

All credit goes here!

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

I use the function:

And it works!

Cheers once more John. Wish I was able to buy you lunch.

Using Macros to Upload Lots of Files

Using XMLHTTP to upload a file to API

0 Response to "Using Macros to Upload Lots of Files"

Postar um comentário

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel