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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Subodh Tiwari (Neeraj)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ouestqueAuthor Commented:
Thanks guys!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.