Hi All, I'm trying to export all of the tables on this website:
http://ca.ishares.com/broker_dealer/index.do
Using a standard web query only brings in the first table "iShares Cdn Equity Fund" ...and none of the tables below (with Fixed Income Funds or Hedged Funds). I've tried to edit the query and 'check off' these tables, but it doesn't work.
Alternatively, I've borrowed some code from another post to get the data using VBA. I've pasted it below. It gets ALL the data (tables, text, other), but dumps it all into a single cell! instead of a 'proper table'.
I could really really use some help either making the web query work (and bring in all these price tables on the website), or modifying the code to capture the data properly in excel. Please take a look and let me know if you have any thoughts! All help much appreciated!
http://ca.ishares.com/broker_dealer/index.do
Using a standard web query only brings in the first table "iShares Cdn Equity Fund" ...and none of the tables below (with Fixed Income Funds or Hedged Funds). I've tried to edit the query and 'check off' these tables, but it doesn't work.
Alternatively, I've borrowed some code from another post to get the data using VBA. I've pasted it below. It gets ALL the data (tables, text, other), but dumps it all into a single cell! instead of a 'proper table'.
I could really really use some help either making the web query work (and bring in all these price tables on the website), or modifying the code to capture the data properly in excel. Please take a look and let me know if you have any thoughts! All help much appreciated!
Code:
Sub GetNavs()
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate "[URL]http://ca.ishares.com/broker_dealer/index.do[/URL]"
Do Until .ReadyState = 4: DoEvents: Loop
Set myTextField = .Document.all.Item("Search_SAC")
myTextField.Value = "529910"
IE.Document.Forms(0).Submit
Do Until .ReadyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop
Set doc = IE.Document
GetOneTable doc, 1
.Quit
End With
End Sub
Sub GetOneTable(d, n)
' d is the document
' n is the table to extract
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
Dim I As Long
Dim J As Long
For Each e In d.all
If e.nodename = "TABLE" Then
J = J + 1
End If
If J = n Then
Set t = e
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("A" & nextrow)
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
Exit For
End If
Next e
End Sub