HTTP Get Request Using VBA in OSX Excel

问题:

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.

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.

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.

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

回答1:

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

回答2:

This wasn't working for me on Mac Excel 15.4 either. I found a helpful version on VBA-Tools Github Here:

https://github.com/VBA-tools/VBA-Web/issues/248

Here's the version that worked for me:

Private Declare PtrSafe Function web_popen Lib "libc.dylib" Alias "popen" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function web_pclose Lib "libc.dylib" Alias "pclose" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function web_fread Lib "libc.dylib" Alias "fread" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function web_feof Lib "libc.dylib" Alias "feof" (ByVal file As LongPtr) As LongPtr

Public Function executeInShell(web_Command As String) As String

    Dim web_File As LongPtr
    Dim web_Chunk As String
    Dim web_Read As Long

    On Error GoTo web_Cleanup

    web_File = web_popen(web_Command, "r")

    If web_File = 0 Then
        Exit Function
    End If

    Do While web_feof(web_File) = 0
        web_Chunk = VBA.Space$(50)
        web_Read = web_fread(web_Chunk, 1, Len(web_Chunk) - 1, web_File)
        If web_Read > 0 Then
            web_Chunk = VBA.Left$(web_Chunk, web_Read)
            executeInShell = executeInShell & web_Chunk
        End If
    Loop

web_Cleanup:

    web_pclose (web_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 = executeInShell(sCmd)
    getHTTP = sResult
End Function