getHTTP with (excel) VBA?
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 With
End 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:
Add a reference to the XML library:
Tools → References → Select
ð¹ Microsoft XML, v6.0
(or the highest version you have listed)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
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 ofHTTP://
wherever possible.
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