How to upload a pdf file through a POST request using VBA

Question

I'm trying to create a VBA script to upload a .pdf file to Pipedrive, using their API. And although I can create the multipart/form-data required by the API, something goes wrong while encoding any other file other than plain text, when it goes wrong the file gets uploaded but I think its binary content end up wrong making it blank.

This is how the API requires the file to be sent: API Documentation snippet

The full documentation is here: https://developers.pipedrive.com/docs/api/v1/Files#addFile

I'm pretty new to using API, so I'm a little lost here, I'm not entirely sure how I should prepare the file to add it to the form-data.

I'm trying the following script

Sub testFileAPI()
    Const COMPANY_DOMAIN As String = "COMPANY DOMAIN"
    Const AUTH_KEY As String = "9999999999999999999"
    Const BOUNDARY As String = "974767299852498929531610575"
    
    Dim request As Object
    Set request = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    Dim URL As String
    Dim body As String
    Dim filePath As String
    Dim customFileName As String
    Dim dealID As Integer
    
    URL = "https://" & COMPANY_DOMAIN & ".pipedrive.com/api/v1/files/" & "?api_token=" & AUTH_KEY
    dealID = 9822
    
    ' Set the file path and custom filename
    filePath = "\test.pdf"
    customFileName = "test123.pdf"
    
    body = createFormData(BOUNDARY, filePath, customFileName, dealID)
    
    request.Open "POST", URL, False
    request.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & BOUNDARY
    
    request.send body
    
    Debug.Print request.responseText
End Sub

Public Function createFormData(ByVal BOUNDARY As String, ByVal filePath As String, ByVal customFileName As String, ByVal dealID As Integer) As String
    Dim bodyContent As String
    
    ' Read the file as binary data
    Dim fileStream As Object
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Type = 1 ' Binary
    fileStream.Open
    fileStream.LoadFromFile filePath
    Dim fileData() As Byte
    fileData = fileStream.Read
    fileStream.Close
    
    bodyContent = "--" & BOUNDARY & vbCrLf
    bodyContent = bodyContent & "Content-Disposition: form-data; name=""file""; filename=""" & customFileName & """" & vbCrLf
    bodyContent = bodyContent & "Content-Type: application/pdf" & vbCrLf & vbCrLf
    bodyContent = bodyContent & ByteArrayToBinaryString(fileData) & vbCrLf
    bodyContent = bodyContent & "--" & BOUNDARY & vbCrLf
    bodyContent = bodyContent & "Content-Disposition: form-data; name=""deal_id""" & vbCrLf & vbCrLf
    bodyContent = bodyContent & dealID & vbCrLf
    bodyContent = bodyContent & "--" & BOUNDARY & "--"
    
    createFormData = bodyContent
End Function

For clarification, the request response is a success and everything seems to work fine, I can see the file there and open it "normally". However, the file is just blank.

Files uploaded available in the deal page

File opens, but shows nothing

----Fixes and responses----

Tried fixing the typo on aplication/pdf, but this didn't seem to be causing the problem:

 bodyContent = bodyContent & "Content-Type: application/pdf" & vbCrLf &
 vbCrLf

Tried fixing the url from "https://yourCompanyHere.pipedrive.com/api/v1/files/?api_token=yourTokenHere" to "https://yourCompanyHere.pipedrive.com/api/v1/files?api_token=yourTokenHere", but the API seems to accept it both ways;
When it "goes wrong" the API still accepts the request, and sends a success:

{"success":true,"data":{"id":72381,"user_id":7869030,"log_id":null,"add_time":"2023-09-02 17:26:09","update_time":"2023-09-02 17:26:09","file_name":"e70986db-e555-47b5-9e42-5b5edca8b1a9.pdf","file_size":48089,"active_flag":true,"inline_flag":false,"remote_location":"s3","remote_id":"company/9999999/user/7869030/files/e70986db-e555-47b5-9e42-5b5edca8b1a9.pdf","s3_bucket":null,"url":"https://COMPANYDOMAIN.pipedrive.com/api/v1/files/72381/download","name":"test123.pdf","description":null,"deal_id":9822,"lead_id":null,"person_id":19122,"org_id":null,"product_id":null,"activity_id":null,"deal_name":"Sent through Excel","lead_name":null,"person_name":"NAME","org_name":null,"product_name":null,"mail_message_id":null,"mail_template_id":null,"cid":null,"file_type":"pdf"}}

I tried opening both the original file and the uploaded file with notepad to check their contents, and although the "text" content seems to be the same, when I compared their binary content it was different. I realized later that the notepad recognizes the original file as being ANSI, and the new uploaded file as being UTF-8... I'm guessing that's where the problem is. I'm using this function to build the binary string of the file:

Function ByteArrayToBinaryString(ByRef ByteArray() As Byte) As String
    Dim binaryString As String
    Dim I As Long
    For I = LBound(ByteArray) To UBound(ByteArray)
        binaryString = binaryString & Chr(ByteArray(I))
    Next I
    ByteArrayToBinaryString = binaryString
End Function

https://developers.pipedrive.com/tutorials/adding-files-via-pipedrive-api?step=4 has the URL as `https://yourCompanyHere.pipedrive.com/api/v1/files?api_token=yourTokenHere` but you have `https://yourCompanyHere.pipedrive.com/api/v1/files/?api_token=yourTokenHere` — Tim Williams, Sep 02 '23 at 01:32
When it goes wrong what does `Debug.Print request.responseText` give you? — Tim Williams, Sep 02 '23 at 01:33
Similar working version here: https://stackoverflow.com/a/64650121/478884 — Tim Williams, Sep 02 '23 at 22:14
That version worked like a charm, I don't understand exactly why it works, but it does. Thank you — Eric Dutra, Sep 03 '23 at 00:02

score 0 · Answer 1 · answered Sep 03 '23 at 00:00

Found a working version here: https://stackoverflow.com/a/64650121/478884

Full working code below:

Public Sub UploadFile()
    Dim sFormData, bFormData
    Dim d As String, DestURL As String, fileName As String, FilePath As String, FieldName As String, CONTENT As String
    
    Const TOKEN As String = "YOUR_API_TOKEN_HERE"
    Const BOUNDARY As String = "---------------------------0123456789012"
    Const DEALID As String = "9822"
    
    FieldName = "file"
    DestURL = "https://COMPANY_DOMAIN.pipedrive.com/api/v1/files?api_token=" & TOKEN
    fileName = "PDFFILE.pdf"
    CONTENT = "application/pdf"
    
    FilePath = "C:\..." + fileName
  
    Dim File, FILESIZE
    Set ado = CreateObject("ADODB.Stream")
    ado.Type = 1 'binary
    ado.Open
    ado.LoadFromFile FilePath
    ado.Position = 0
    FILESIZE = ado.Size
    File = ado.Read
    ado.Close
  
    Set ado = CreateObject("ADODB.Stream")
    d = "--" + BOUNDARY + vbCrLf
    d = d + "Content-Disposition: form-data; name=""deal_id""" & vbCrLf & vbCrLf
    d = d + DEALID & vbCrLf
    d = d + "--" + BOUNDARY + vbCrLf
    d = d + "Content-Disposition: form-data; name=""" + FieldName + """;"
    d = d + " filename=""" + fileName + """" + vbCrLf
    d = d + "Content-Type: " & CONTENT + vbCrLf + vbCrLf
    ado.Type = 1 'binary
    ado.Open
    ado.Write ToBytes(d)
    ado.Write File
    ado.Write ToBytes(vbCrLf + "--" + BOUNDARY + "--" + vbCrLf)
    ado.Position = 0
    
    With CreateObject("MSXML2.ServerXMLHTTP")
        .Open "POST", DestURL, False
        .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & BOUNDARY
        .send ado.Read
        
        Debug.Print .responseText
    End With
End Sub

Function ToBytes(str As String) As Variant
    Dim ado As Object
    Set ado = CreateObject("ADODB.Stream")
    ado.Open
    ado.Type = 2 ' text
    ado.Charset = "_autodetect"
    ado.WriteText str
    ado.Position = 0
    ado.Type = 1
    ToBytes = ado.Read
    ado.Close
End Function

How to upload a pdf file through a POST request using VBA

1 Answers1