在OSX Excel中使用VBA的HTTP获取请求 [英] HTTP Get Request Using VBA in OSX Excel

问题描述

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

I'm writing a very simple macro that needs to make an HTTP GET request to a server and the response is not important (it initiates a process on the server). The HTTP GET does NOT require authentication.

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

I'm using the following code to do this "successfully" (the server logs indicated the request made it to the server, but the server is running 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

但是我从服务器获得以下响应:

But I get back the following response from the server:

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

我可以看到服务器正在返回HTTP 406，在进行了一些研究之后，由于GET请求未发送正确的Content-Type标头.

I can see the server is return an HTTP 406 which, after some research is occurring because the GET request is not sending the correct Content-Type header.

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

So my question is - how do tell ActiveSheet.QueryTables.Add to set the header, or how do I modify my NGINX config to support this specific GET CALL

推荐答案

此处是支持OSX和Windows的一段代码.我想赞一下这本书的作者，因为我当然不是从零开始写这本书的，但是我却一无所获:

Here is a piece of code that supports both OSX and Windows. I would credit the authors of this because I certainly didnt write this from scratch but I have lost track of where it all came from:

#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

这篇关于在OSX Excel中使用VBA的HTTP获取请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！