Unable to submit large base64 fields using VBA

Viewed 60

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.

0 Answers
Related