VBA – Load Web Page from Excel

Posted on December 21, 2014 by

Sometimes you may wish to load a web page from Excel. Just for fun.
Then you have probably googled it and you realized that the command “ActiveSheet.QueryTables.Add” exists. Then you simply decided to try it but, something did not happen as expected.

At least this was my story from a few hours ago 🙂 Then I realized how it should be done and I have been able to make the following code, publishing the web page at the activesheet. I even had the time to build the following gif, by pressing F8.

Here comes the code, if you do not want to copy it from the gif:

Option Explicit

Sub LoadWebPageInExcel()

    Dim sURL As String
    sURL = "http://www." & InputBox("Please, enter a web site without the www.", "Load Web Page in Excel")

    Application.Goto Range("A1"), True

    Cells.Clear

    With ActiveSheet.QueryTables.Add(Connection:="URL;" & sURL, Destination:=Range("A1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    
    Cells.EntireColumn.AutoFit

End Sub

Option Explicit

Sub LoadWebPageInExcel()

Dim sURL As String

sURL = "http://www." & InputBox("Please, enter a web site without the www.", "Load Web Page in Excel")

Application.Goto Range("A1"), True

Cells.Clear

With ActiveSheet.QueryTables.Add(Connection:="URL;" & sURL, Destination:=Range("A1"))

.BackgroundQuery = True

.TablesOnlyFromHTML = False

.Refresh BackgroundQuery:=False

.SaveData = True

End With

Cells.EntireColumn.AutoFit

End Sub

Enjoy! 🙂

VBA – Load Web Page from Excel

Related posts: