I am trying to submit base64 string converted file to a rest API using Excel VBA / macro. The file upload is working fine for less than 5kb files (image and pdf). There is no restriction on the server side.
You can see the script for file upload below
Sub httpPost()
Dim xmlhttp
Dim adoStream
Dim image1, image2, pdfFile As String
Set sh = ActiveWorkbook.Worksheets("Data")
image1 = WorksheetFunction.EncodeURL("data:image/gif;base64," & EncodeFile(ActiveWorkbook.path & "\1.gif"))
image2 = WorksheetFunction.EncodeURL("data:image/gif;base64," & EncodeFile(ActiveWorkbook.path & "\2.gif"))
pdfFile = WorksheetFunction.EncodeURL("data:application/pdf;base64," & EncodeFile(sh.Cells(i, 31).Value))
Dim argumentString
argumentString = "poster_name=" & sh.Cells(i, 3).Value & _
"&message_1=" & sh.Cells(i, 4).Value & _
"&message_2=" & sh.Cells(i, 5).Value & _
"&url_website=" & sh.Cells(i, 9).Value & _
"&email=" & sh.Cells(i, 23).Value & _
"&image_chart_1=" & image1 & _
"&image_chart_2=" & image2 & _
"&pdf_doc=" & pdfFile
Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
xmlhttp.Open "POST", "http://example.com/api", False
xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
xmlhttp.Send argumentString
Set xmlhttp = Nothing
End Sub
And the base64 conversion function is
Private Function EncodeFile(ByVal path As String) As String
Const adTypeBinary = 1 ' Binary file is encoded
' Variables for encoding
Dim objXML
Dim objDocElem
' Variable for reading binary picture
Dim objStream
' Open data stream from picture
Set objStream = CreateObject("ADODB.Stream")
objStream.Type = adTypeBinary
objStream.Open
objStream.LoadFromFile (path)
' Create XML Document object and root node
' that will contain the data
Set objXML = CreateObject("MSXml2.DOMDocument")
Set objDocElem = objXML.createElement("Base64Data")
objDocElem.DataType = "bin.base64"
' Set binary value
objDocElem.nodeTypedValue = objStream.Read()
' Get base64 value
EncodeFile = objDocElem.Text
' Clean all
Set objXML = Nothing
Set objDocElem = Nothing
Set objStream = Nothing
End Function
I have tried to find out the documentation about MSXML2.XMLHTTP.6.0 and MS Excel. But I didn't find any field size restriction.