从Mac的VBA Excel以HTTP Post发送数据 [英] Send data in HTTP Post from VBA Excel for Mac

问题描述

因此，我需要使用VBA在Mac版Excel中完成一项任务:

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

按一下按钮，我需要从Excel中的工作表中读取数据，解析值并将其导出到Web服务器，该服务器读取数据并将其写入服务器上的文件中.在Windows上，使用MSXML2.ServerXMLHTTP以及所有工具，一切都进行得很顺利，但在Mac上则不可能(这种对象是ActiveX的一部分).

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

我也尝试通过curl命令发送数据，但是我似乎找不到从VBA中正确执行数据的方法.

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.

添加了我失败的尝试

推荐答案

好，所以我进行了进一步的研究，终于使用cURL找到了行之有效的方法.为了做到这一点，我们必须制作一个这样的函数(这是我在这里找到的一个函数

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

基本上，此功能使我们能够在Mac上调用Shell命令，而不必经历将Mac命令转换为Windows格式的所有麻烦.

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.

完成此操作后，以第一个参数为命令行(cURL格式)调用该函数，第二个参数为系统返回的退出代码的引用(而不是cURL请求！).然后，cURL命令将返回http响应.所以类似的东西会起作用:

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)

注意我如何开始我的cURL请求.您必须编写cURL的路径(在本例中为"usr/bin/curl")，以便系统查找cURL的位置，否则它将不起作用(它可能起作用，但是通过这种方式，我们确保它将始终起作用)工作)

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)

跨平台兼容性

请注意，如果此解决方案在Windows上不起作用，因为它没有"libc.dylib"库，因此您必须检查操作系统并实现在Windows上实现该方法的方法(如问题中所写)

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)

这篇关于从Mac的VBA Excel以HTTP Post发送数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！