macos - 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

Answer 1

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)