0

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.

1

1 Answer 1

1

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
Sign up to request clarification or add additional context in comments.

3 Comments

Doesn't seem to work. Also do I need to set Referer? strUrls is not defined. What's that for?
The error I got basically means the data hasn' been uploaded properly
Shouldn't the header be 'Content-Type': 'text/csv' ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.