How do I pull a certain table from Internet Explorer in Excel VBA

Question

Hello I've scoured the internet trying to find a solution to this problem.

I'm trying to pull a certain table from this a private website using excel-vba.
I came across this code that allows me to pull all the tables but I only need one table - is there any way I can edit this to pull just one specific table?

code

Sub GetAllTables(doc As Object)

' get all the tables from a webpage document, doc, and put them in a new worksheet

Dim IE As Object
Dim ws As Worksheet
Dim rng As Range
Dim tbl As Object
Dim rw As Object
Dim cl As Object
Dim tabno As Long
Dim nextrow As Long
Dim I As Long
Dim oXL As Excel.Application

Set ws = Sheet1

For Each tbl In doc.getElementsByTagName("TABLE")
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = ws.Range("C" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each rw In tbl.Rows
For Each cl In rw.Cells
rng.Value = cl.outerText
Set rng = rng.Offset(, 1)
I = I + 1
Next cl
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next rw
Next tbl

ws.Range("C:N").ClearFormats

what problems do you run into after removing the for loop to fetch single table? — Danielle, Jul 21 '14 at 00:50
See http://stackoverflow.com/a/8846791/641067 - great approach by Sid Rout — brettdj, Jul 21 '14 at 00:54
You can just Import the table on the Data - Import External Data - New Web Query and select the table then your update options. — D.Ddgg, Jul 21 '14 at 02:23

score 0 · Answer 1 · answered Jul 21 '14 at 02:23

Sure there is. Which table do you need to pull? Either you can identify it by index position:

Dim t as Integer    'represents the index position of the table you want to obtain.
t = 0
Set tbl = doc.getElementsByTagName("TABLE")(t)

Or, if the index position is not known, you would need to develop some other logical tests within a For/Each loop. Note: this is a very simplistic example with pseudo-code. You may require more complex logic/conditions to determine which table is the correct table in the collection:

For each tbl in doc.getElementsByTagName("TABLE")
    If tbl.__some_property__ = "something" Then
        Exit For
    End If
Next

How do I pull a certain table from Internet Explorer in Excel VBA

1 Answers1