Excel Data Refresh - Fixing with code?

LIJON

New Member
Joined
Jan 7, 2010
Messages
7
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!


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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,816
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'.
Because:

GetOneTable doc, 1

is getting the outer table (probably 1 row x 1 column), within which the other table(s) you want are nested. Try different table numbers, e.g.

GetOneTable doc, 4
GetOneTable doc, 5

by trial and error or by viewing the HTML source and counting the Table tags to determine the table number(s) you want.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,158
Messages
5,657,160
Members
418,363
Latest member
Debating_Earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top