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:
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
With ActiveSheet.QueryTables.Add(Connection:="URL;" & sURL, Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.SaveData = True