Send data in HTTP Post from VBA Excel for Mac

Question

So I have a task to do in Excel for Mac, using VBA :

At the press of a button, I need to read data from a Worksheet in Excel, parse the values and export them to a web server, which reads the data and write it in a file on the server. On Windows, everything went smoothly, using MSXML2.ServerXMLHTTP and all, but on Mac it is not possible to do so (This kind of object is part of ActiveX).

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
objHttp.SetOption 2, objHttp.GetOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
objHttp.Open "POST", myURL, False
objHttp.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHttp.SetRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHttp.SetRequestHeader "Authorization", "Basic " & Base64Encode(Username & ":" & Password)
objHttp.Send (strOutput)

From this thread : How can I send an HTTP POST request to a server from Excel using VBA?, I tried using Query Tables without any success (I always got the "server not found" error, even though I triple checked the address and all.)

With ActiveSheet.QueryTables.Add(Connection:="URL;https://myurl.com/index.php", Destination:=Range("K1"))
    .PostText = strOutput
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
End With

I also tried to send the data through a curl command, but I can't seem to find how to execute it correctly from within VBA.

 sCmd = "curl " & _
        "-u " & Username & ":" & Password & _
        " -d " & Chr(34) & data & Chr(34) & _
        " " & url

So if anyone has an idea on how I should do that task, I would be really grateful. Thanks a lot.

EDIT : Added my failed attempts

@pnuts Sorry, I didn't thought that adding these attempts were relevant, since they are exactly like the linked question — RaphBlanchet, Nov 27 '15 at 19:05

score 0 · Accepted Answer · edited May 23 '17 at 12:23

Ok, so I've made further research, and I finally found something that's working, using cURL. In order to make it, we have to make a function like this (this is a function I found here VBA Shell function in Office 2011 for Mac) :

Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Basically, this function allows us to call shell commands on Mac, without having to go through all the struggle of converting Mac commands to Windows format.

Once this is done, call this function with the first parameter being the command line (in cURL format), and the second a reference to the exit code returned by the system (and not the cURL request!). The cURL command will then return the http response. So something like this will work :

Dim command As String
command = "usr/bin/curl http://www.myserver.com -d "data" -X POST ..."

Dim exitCode As Long
Dim result As String

result = execShell(command, exitCode)

Notice how I'm starting my cURL request. You HAVE to write the path to cURL (in this case "usr/bin/curl") in order for the system to find cURL's location, otherwise it won't work (it may work, but this way we make sure it will always work)

Cross-platform compatibility

Please note that this solution however won't work if it's on windows, as it doesn't have "libc.dylib" librairies, so you'll have to check the Operating system and implement a way to do it on Windows (as written in the question)

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
objHttp.SetOption 2, objHttp.GetOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
objHttp.Open "POST", myURL, False
objHttp.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHttp.SetRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHttp.SetRequestHeader "Authorization", "Basic " & Base64Encode(Username & ":" & Password)
objHttp.Send (strOutput)

i got error on private function, any idea to solve? – questionasker Apr 02 '20 at 01:17 — questionasker, Apr 02 '20 at 01:17
Try 'Private Declare PtrSafe Function' instead – alphabet5 Feb 05 '21 at 00:00 — alphabet5, Feb 05 '21 at 00:00

score -1 · Answer 2 · answered Jul 26 '17 at 06:17

For higher version than Mac Excel 2011, I recommend this.

Let's say we call 'http://example.com?q=hello&t=now' by POST action.

Private Declare PtrSafe Function system Lib "libc.dylib" (ByVal command As String) As Long

Function getHTTPPost(sUrl As String, sQuery As String) As Long

    sCmd = "curl -v -X POST -d '" & sQuery & "' " & sUrl
    exitCode = system(sCmd)

End Sub

Then, we can use follow:

ans = getHTTPPost("http://example.com", "q=hello&t=now")

But, we'd like to consider that we can't get the response string by this code.

Just hope this help you with your concern of HTTP Post Request in Mac VBA of Office 2016.

Send data in HTTP Post from VBA Excel for Mac

2 Answers2

Linked