I found this Macro in one of the many threads out there for Web Queries...
Sub Test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://equitytoincome.com/ex.htm"
Do Until .readyState = 4: DoEvents: Loop
Set doc = ie.document
GetAllTables doc
End With
End Sub
Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub
I'm a complete noob when it comes to web queries and pulling data into excel using web queries... What I'm trying to do is, my company has an intranet site that has all production numbers in various reports (blah blah blah), and what I'm trying to do is import some reports into excel where I can analyze the data easier.. The reports that we have, we can flip them around switching axis information, clicking on a particular section and it gives more in depth information to whatever I'm looking for, and stuff, and the problem I'm encountering is that I get an "Unexpected Error" when doing it through the Import Data feature in Excel. I think I've pegged it to the address string being way too long for Excel to import the information. Each time I modify the report it extends the string..
So I came across this macro that can download the info but it shows me all the tables that are on a particular page in that report... Using this I found out exactly which table I need. So my question is, how can I download just THAT specific Table? I found that there was 15 Tables on this particular report, I'm only needing table 9...
HELP...
Sub Test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://equitytoincome.com/ex.htm"
Do Until .readyState = 4: DoEvents: Loop
Set doc = ie.document
GetAllTables doc
End With
End Sub
Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub
I'm a complete noob when it comes to web queries and pulling data into excel using web queries... What I'm trying to do is, my company has an intranet site that has all production numbers in various reports (blah blah blah), and what I'm trying to do is import some reports into excel where I can analyze the data easier.. The reports that we have, we can flip them around switching axis information, clicking on a particular section and it gives more in depth information to whatever I'm looking for, and stuff, and the problem I'm encountering is that I get an "Unexpected Error" when doing it through the Import Data feature in Excel. I think I've pegged it to the address string being way too long for Excel to import the information. Each time I modify the report it extends the string..
So I came across this macro that can download the info but it shows me all the tables that are on a particular page in that report... Using this I found out exactly which table I need. So my question is, how can I download just THAT specific Table? I found that there was 15 Tables on this particular report, I'm only needing table 9...
HELP...