How to convert @data.file curl command to VBA?

Question

I have a curl command that uploads some data to an API and returns the results

curl -X POST -H 'Content-Type: text/csv' --data-binary @data/data.csv https://some.url.com/invocations > data/churn_scored.jsonl

However, I can't figure out how to do that using VBA after reading the documentation (https://learn.microsoft.com/en-au/windows/desktop/WinHttp/winhttprequest).

In particular, it would be great if I can treat a range of cells in Excel as if it's a CSV and upload that.

Another post (Equivalent cURL in VBA?) shows how to curl an endpoint but doesn't show how to upload data using the @ command, which is the central point of this question.

Possible duplicate of [Equivalent cURL in VBA?](https://stackoverflow.com/questions/17063550/equivalent-curl-in-vba) — L8n, Jun 25 '19 at 05:56

Dmitrij Holkin · Answer 1 · 2019-06-25T07:43:30.017

Try this

Public saveName as string

Function SaveSheetAsWorkbook()
    Dim NewWb As Workbook
    Dim ws As Worksheet
        saveName = "c:\1.csv"
        Set ws = wbT.Worksheets("Sheet1")
        Set NewWb = Workbooks.Add
        With NewWb
            ws.Copy Before:=.Worksheets(1)
            If .Worksheets.Count > 1 Then .Worksheets(2).Delete
            .SaveAs saveName, xlCSV
            .Close
        End With
End Function

Function AuthSite()
    Dim WinHttpReq, oStream
    Set WinHttpReq = CreateObject("WINHTTP.WinHTTPRequest.5.1")
    WinHttpReq.Open "POST", "https://some.url.com/invocations", False
        WinHttpReq.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0"
        WinHttpReq.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
        WinHttpReq.setRequestHeader "Accept-Language", "en-US;q=0.5"
        'WinHttpReq.setRequestHeader "Accept-Encoding", "gzip, deflate, br"
        WinHttpReq.setRequestHeader "Referer", strUrls
        'WinHttpReq.setRequestHeader "Cookie", cookie
        WinHttpReq.setRequestHeader "Connection", "keep-alive"
        WinHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    WinHttpReq.send saveName
    Set AuthSite = WinHttpReq
End Function

or use multipart form

Public Boundary as string

Function UploadFile(ByVal filePath As String, ByVal FileName As String)
  Dim sFormData As String
  Randomize
  Boundary = "---------------------------" & Int(999999999999999# * Rnd)
  sFormData = GetFile(filePath & "\" & FileName)
    D = "--" + Boundary + vbCrLf
        '------1----------
        strF = "_method"
        strFval = "POST"
            D = D + "Content-Disposition: form-data; name=""" + strF + """;" + vbCrLf + vbCrLf
            D = D + strFval
            D = D + vbCrLf + "--" + Boundary + vbCrLf

    'Build source form with file contents
    FieldName = "yourFieldName"
        D = D + "Content-Disposition: form-data; name=""" + FieldName + """;"
        D = D + " filename=""" + FileName + """" + vbCrLf
        D = D + "Content-Type: application" + vbCrLf + vbCrLf
        D = D + sFormData
        D = D + vbCrLf + "--" + Boundary + "--" + vbCrLf

  'Post the data To the destination URL

  UploadFile = D

End Function


'read binary file As a string value
Function GetFile(ByVal FileName As String) As String
    Dim FileContents() As Byte, FileNumber As Integer
    ReDim FileContents(FileLen(FileName) - 1)
    FileNumber = FreeFile
    Open FileName For Binary As FileNumber
        Get FileNumber, , FileContents
    Close FileNumber
    GetFile = StrConv(FileContents, vbUnicode)
End Function

Function AuthSite2()
    Dim WinHttpReq, oStream
    Set WinHttpReq = CreateObject("WINHTTP.WinHTTPRequest.5.1")
    strPost = UploadFile(fPath, fName)
    ReDim bFormData(Len(strPost) - 1)
    bFormData = StrConv(strPost, vbFromUnicode)

    WinHttpReq.Open "POST", "https://some.url.com/invocations", False
        WinHttpReq.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0"
        WinHttpReq.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
        WinHttpReq.setRequestHeader "Accept-Language", "en-US;q=0.5"
        'WinHttpReq.setRequestHeader "Accept-Encoding", "gzip, deflate, br"
        WinHttpReq.setRequestHeader "Referer", "https://some.url.com/invocations"
        WinHttpReq.setRequestHeader "Connection", "keep-alive"
        WinHttpReq.setRequestHeader "Content-Type", "multipart/form-data; boundary=" + Boundary + vbCrLf
    WinHttpReq.send bFormData
    Set AuthSite2 = WinHttpReq
End Function

Doesn't seem to work. Also do I need to set Referer? strUrls is not defined. What's that for? — xiaodai, Jun 25 '19 at 06:56
The error I got basically means the data hasn' been uploaded properly — xiaodai, Jun 25 '19 at 06:57

How to convert @data.file curl command to VBA?

1 Answers1