如何从 Excel VBA for Mac 发出 HTTP GET
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15981960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I issue an HTTP GET from Excel VBA for Mac
提问by John Stephens
I need to issue an HTTP Get with a query string to a web service from Excel for Mac 2011. I've seen the answers for using QueryTables (How can I send an HTTP POST request to a server from Excel using VBA?) but they use the POST method, not a GET method. I also see that it's easy from a Windows machine, but I'm stuck on a Mac.
我需要从 Excel for Mac 2011 向 Web 服务发出带有查询字符串的 HTTP Get。我已经看到了使用 QueryTables 的答案（如何使用 VBA 从 Excel 向服务器发送 HTTP POST 请求？）但它们使用 POST 方法，而不是 GET 方法。我也看到在 Windows 机器上很容易，但我被困在 Mac 上。
Any suggestions, or is it hopeless?
有什么建议，还是没有希望？
回答by John Stephens
Doing further research, I came across Robert Knight's comment on this question VBA Shell function in Office 2011 for Macand built an HTTPGet function using his execShell function to call curl. I've tested this on a Mac running Mac OS X 10.8.3 (Mountain Lion) with Excel for Mac 2011. Here is the VBA code:
做进一步的研究，我在 Office 2011 for Mac 中看到了 Robert Knight 对这个问题VBA Shell 函数的评论，并使用他的 execShell 函数构建了一个 HTTPGet 函数来调用 curl。我已经在运行 Mac OS X 10.8.3 (Mountain Lion) 和 Excel for Mac 2011 的 Mac 上对此进行了测试。这是 VBA 代码：
Option Explicit
' execShell() function courtesy of Robert Knight via StackOverflow
' https://stackoverflow.com/questions/6136798/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
Function HTTPGet(sUrl As String, sQuery As String) As String
Dim sCmd As String
Dim sResult As String
Dim lExitCode As Long
sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sResult = execShell(sCmd, lExitCode)
' ToDo check lExitCode
HTTPGet = sResult
End Function
To use this, copy the code above, open the VBA editor in Excel for Mac 2011. If you don't have a module, click Insert->Module. Paste the code into the module file. Leave the VBA editor (clover-Q).
要使用它，请复制上面的代码，在 Excel for Mac 2011 中打开 VBA 编辑器。如果您没有模块，请单击“插入”->“模块”。将代码粘贴到模块文件中。离开 VBA 编辑器 (clover-Q)。
Here's a specific example using a weather forecast web service (http://openweathermap.org/wiki/API/JSON_API)
这是使用天气预报 Web 服务的具体示例 ( http://openweathermap.org/wiki/API/JSON_API)
Cell A1 will be reserved for the name of the city.
单元格 A1 将保留用于城市名称。
In cell A2, enter the URL string: http://api.openweathermap.org/data/2.1/forecast/city
在单元格 A2 中，输入 URL 字符串： http://api.openweathermap.org/data/2.1/forecast/city
In cell A3 which will build the query string, enter: ="q=" & A1
在将构建查询字符串的单元格 A3 中，输入： ="q=" & A1
In cell A4, enter: =HTTPGet(A2, A3)
在单元格 A4 中，输入： =HTTPGet(A2, A3)
Now, type a city name in cell A1, for example London, cell A4 will show you the JSON response containing the weather forecast for London. Change the value in A1 from Londonto Moscow-- A4 will change to the JSON-formatted forecast for Moscow.
现在，在单元格 A1 中输入城市名称，例如London，单元格 A4 将显示包含伦敦天气预报的 JSON 响应。将 A1 中的值从 更改London为Moscow-- A4 将更改为莫斯科的 JSON 格式的预测。
Obviously, using VBA, you could parse and reformat the JSON data and place it where needed in your worksheet.
显然，使用 VBA，您可以解析和重新格式化 JSON 数据并将其放置在工作表中需要的位置。
No claims for performance or scalability, but for a simple one-shot access to a web service from Excel for Mac 2011, this seems to do the trick and met the need for which I posted my original question. YMMV!
没有对性能或可扩展性的要求，但对于从 Excel for Mac 2011 对 Web 服务的简单一次性访问，这似乎可以解决问题并满足我发布原始问题的需求。天啊！
回答by Scott Dudley
The answer above from John Stephens is fantastic (please upvote it!), but it no longer worked for me in the more recent Excel:mac 2016, with an error that the code needs to be updated for use on 64-bit systems.
上面来自 John Stephens 的答案很棒（请点赞！），但它在最近的 Excel:mac 2016 中不再适用于我，错误是代码需要更新才能在 64 位系统上使用。
Taking some tips from an issue I found in a related repository, I was able to adjust the data types in John's script to work correctly in Excel:mac 2016:
从我在相关存储库中发现的问题中获取一些提示，我能够调整 John 脚本中的数据类型以在 Excel:mac 2016 中正常工作：
Option Explicit
' execShell() function courtesy of Robert Knight via StackOverflow
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac
Private Declare PtrSafe Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function pclose Lib "libc.dylib" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function feof Lib "libc.dylib" (ByVal file As LongPtr) As LongPtr
Function execShell(command As String, Optional ByRef exitCode As Long) As String
Dim file As LongPtr
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
Function HTTPGet(sUrl As String, sQuery As String) As String
Dim sCmd As String
Dim sResult As String
Dim lExitCode As Long
sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sResult = execShell(sCmd, lExitCode)
' ToDo check lExitCode
HTTPGet = sResult
End Function
回答by mwag
Another option (update accordingly if your curl is not located in /opt/local/bin/curl):
另一个选项（如果您的 curl 不在 /opt/local/bin/curl 中，则相应地更新）：
VBA:
VBA：
Public Function getUrlContents(url) As String
Dim command As String
command = "do shell script ""/path_to/getUrl.sh " + url + """"
getUrlContents = VBA.MacScript(command)
End Function
/path_to/getUrl.sh:
/path_to/getUrl.sh:
#!/bin/sh
if [ -z "" ]
then
echo "missing url argument"
else
/opt/local/bin/curl ""
fi
Note that you will have to ensure that getUrl.sh is executable:
请注意，您必须确保 getUrl.sh 是可执行的：
chmod u+x getUrl.sh
