macos - 在 OSX Excel 中使用 VBA 获取 HTTP 请求

Question

我正在编写一个非常简单的宏，它需要向服务器发出 HTTP GET 请求并且响应并不重要(它在服务器上启动一个进程)。 HTTP GET 不需要身份验证。

我正在使用以下代码“成功”地执行此操作(服务器日志表明请求已发送到服务器，但服务器正在运行 HTTP 406):

Function callAPI(Url As String)
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & Url, Destination:=Range("D15"))
    .PostText = ""
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With
End Function

但我从服务器收到以下响应:

Unable to open http://someurl.com Cannot locate the Internet server or proxy server.

我可以看到服务器返回一个 HTTP 406，经过一些研究，因为 GET 请求没有发送正确的 Content-Type标题。

所以我的问题是 - 如何告诉 ActiveSheet.QueryTables.Add设置标题，或者我如何修改我的 NGINX 配置以支持这个特定的 GET CALL

Answer 1

这是一段同时支持 OSX 和 Windows 的代码。我会相信这本书的作者，因为我当然不是从头开始写的，但我已经忘记了这一切的来源:

#If Win32 Then

Function getHTTP(URL As String, sQuery As String) As String
    Dim strResult As String
    Dim objHTTP As Object
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    objHTTP.Open "GET", URL & "?" & sQuery, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send("")
    strResult = objHTTP.responseText
    getHTTP = strResult
End Function


#Else


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 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 getHTTP(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
    getHTTP = sResult
End Function

#End If