Excel VBA: Copy First Table in Web Page

Let's say I navigate to a webpage that contains text and some tables with cool info in it. What VBA would I need that will copy the data from the first table into a Sheet titled: "Sheet1"?

Example: The following Wikipedia link contains a table of the largest known stars: https://en.wikipedia.org/wiki/List_of_largest_stars
What VBA would I use to get that table into Excel?
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You may try something like this....

Sub ImportWebData()
Dim ws As Worksheet
Dim qt As QueryTable
Dim URL As String

URL = "https://en.wikipedia.org/wiki/List_of_largest_stars"

Set ws = Sheets("Sheet1")


Set qt = ws.QueryTables.Add( _
        Connection:="URL;" & URL, _
With qt
    .RefreshOnFileOpen = True
    .Name = "WebData"
    .WebFormatting = xlWebFormattingRTF
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "2"
    .Refresh BackgroundQuery:=False
End With
End Sub

Sub DeleteExistingConnections()
Dim cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
Next cn
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
You've just asked a very similar question which I have posted some suggestions to here

Please do not ask multiple questions on the same topic.
Roy CoxGroup Finance ManagerCommented:
This was imported manually using From Web on the Data tab. Took less than a minute
ouestqueAuthor Commented:
Thanks guys!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.