getHTTP with (Excel) VBA?

Question

I'm searching for a query for an Excel VBA macro to get a web page's HTML source code as a string.

I've found some source with getHTTPrequest but I can't register .net framework system.dll or link it.

Thanks!

Solution 1

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

Solution 2

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:

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.

Comments

[User1974]

This answer has helped me out a lot. Thanks! I've referenced the Late Bound solution in a follow-up question here: Return server error from function as string?.