getHTTP with (Excel) VBA?

Close enough: How can I send an HTTP POST request to a server from Excel using VBA? — It's even for Excel ;-)

Just use a GET request instead:

objHTTP.Open "GET", URL, False

MSDN: Using the WinHttpRequest COM Object - Retrieving Data Using Visual Basic

Here's a compact function that returns the response text of almost any specified URL, such as HTTP source code for a web page, or response text from JSON API's.

Late Bound: _{^{(No references required)}}

Public Function getHTTP(ByVal url As String) As String  With CreateObject("MSXML2.ServerXMLHTTP.6.0")     .Open "GET", url, False: .Send    getHTTP = StrConv(.responseBody, vbUnicode)  End WithEnd Function

Early Bound:

If you need to call the function repeatedly (in a loop, for example) you might be better off to use it as an early-bound function:

1. Add a reference to the XML library:

   Tools → References → Select 🗹 Microsoft XML, v6.0 (or the highest version you have listed)

2. Declare a module-level variable: (place this line at the top of a module)
   ^{(Shortcut to create new VBA module: Alt + F11 → I → M)}

   Dim msXML As XMLHTTP60

3. Within the function, only create the XML object if the variable is not already set:

    Public Function getHTTP(ByVal url As String) As String   If msXML Is Nothing Then Set msXML = New XMLHTTP60   With msXML     .Open "GET", url, False: .Send     getHTTP = StrConv(.responseBody, vbUnicode)   End With End Function

Example Usage: _{^{(for either method)}}

To get the HTML source code for this page:

Debug.Print getHTTP("https://stackoverflow.com/q/817602")

🎗️ NOTE!

Many applications (including MS Office) will now return an error if you attempt to connect to an insecure url with methods like this, or others (ie., Excel's WEBSERVICE).

To avoid this issue, use HTTPS:// instead of HTTP:// wherever possible.