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?
ouestqueAsked:
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")
ws.Cells.Clear

DeleteExistingConnections

Set qt = ws.QueryTables.Add( _
        Connection:="URL;" & URL, _
        Destination:=ws.Range("A1"))
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
    cn.Delete
Next cn
End Sub

Open in new window

0
 
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.
0
 
Roy CoxGroup Finance ManagerCommented:
This was imported manually using From Web on the Data tab. Took less than a minute
STARS.xlsx
0
 
ouestqueAuthor Commented:
Thanks guys!!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
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.