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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,361
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,998
Messages
5,508,682
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top