Import Web Tables

[ian bartlett]

I recieved this code from Tim Williams and at the time I thought it did what
I wanted, but now I've noticed that its not importing all the web pages,
when you watch the import you can see it start to load say page 7 then it
quickly changes to page 8.

Any ideas ??

Thanks in advance

Bart


'***********************************
Sub playoffs()

Dim IE As Object, allCells, r, c, x, rw As Long
Dim row, numPage
rw = 2


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


For numPage = 1 To 12

**All 1 line ** IE.Navigate
"http://www.nhl.com/nhlstats/app?service=page&page=" & _
"Stats&fetchKey=20083ALLSASAll&viewName=summary&sort=points&pg="
& numPage

Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop

Set r = Nothing
Set allCells = IE.document.getElementsByTagName("td")

For Each c In allCells
If c.innerText Like "Play*" Then
c.Style.Border = "1px solid #FF0000"
Set r = c.ParentElement.ParentElement.ParentElement 'containing
Table

c.Style.Border = "1px solid #0000FF"
Exit For
End If
Next c

If Not r Is Nothing Then
For Each row In r.Rows
For x = 1 To row.Cells.Length - 1
Sheet3.Cells(rw, x).Value = row.Cells(x - 1).innerText
Next x
rw = rw + 1
Next row
End If

Next numPage

End Sub

 

[Don Guillett]

I have created similar projects for clients using external queries. Navigate
to the url desired>right click in the table>import to ms excel>go from
there. If desired, contact me privately with the desired url.

 

[Gary Keramidas]

seems to be a timing issue. if i create a delay for 2 seconds, it seems to
populate correctly

Do While IE.ReadyState < 4 'READYSTATE_COMPLETE
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:02"))

Set r = Nothing
Set allCells = IE.document.getElementsByTagName("td")

 

[ian bartlett]

Gary your idea did the trick!! Awesome this makes setting up my hockey pool
much easier, previously I had to insert Web Queries for each page and hope
the colums would line up properly so I could do vlookups on them, this will
save me time and major frustration.

Thanks again

Don

I was using 2003 Excel, for the web queries now I'm using 2007 but I must
confess I haven't tried web queries in 2007 and with the code working
correctly for my bandwith I probably won't.
Thanks

Bart