Import HTML table into Excel

[ms1109]

I am familiar with VBA, but this one has me stumped! Basically, I want to have Excel fill out an Aspx form, submit it, and then pull in the data from the html table that the form produces. There are multiple queries it would have to do, so I would want it to loop through until it is done with my list of inputs. Is this possible?!? I don't even know where to start...

The website I need to pull data from is: http://www.usac.org/hc/tools/disbursements/default.aspx

On the form, there are multiple fields, but the one I am interested in is the "Study Area Code." An example input I would have in this field is "529910." I would have a different sheet in my workbook with all the SACs I need to pull.

My vision for the end result is to have all the data pulled into one sheet. Can anyone give advise/help?

Thanks,
Matt

 

[Norie]

Matt

Is it just the main table (High Cost Disbursement...) you want the data from?

Perhaps this will give you a start.

Sub GetDisbursement()
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.usac.org/hc/tools/disbursements/default.aspx"
        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

The SAC is hard-coded but the code can easily be adapted to work with multiple codes/worksheets etc.

 

[ms1109]

Wow!! That is really awesome! When I have multiple SACs, how do I make it loop through them and add the data to the end? Also, is there a way to make it so there is only a header on the first dataset it pulls? If not, how about no header at all?

Thank you sooo much!!

Matt

 

[Norie]

Matt

All that should be possible, but to post specifics more information would be needed.

eg worksheet names, range with the SACs etc

Also are there any other things to consider, for example are there always 61 (or is it 62) records in the table?

 

[ms1109]

So, my vision of this would be to have a sheet, maybe just "sheet1" for now, hold a list of the SACs I am wanting to query using your code. They would be listed in A2 - A###.

Then in another sheet, "sheet2" for now, when I run the VBA, I would want it to go through the different SACs in sheet1 and pull the data into sheet2. There is no set number rows of the html table. It is going to be growing each month. I think your code already pulls everything in the table...

When it loops back through for the next table, I would want it to pull just the dataset, no header, and place it in the next available row.

Does all of that make sense?

Thanks again!

Matt

 

[Norie]

Matt

Makes sense, yep.

And the first part (going through the SACs) should be no problem.

The next bit should be no problem either, just need to think about it further.

I'll post back tomorrow.

 

[LIJON]

Wow, hey Norie, I've got a similar issue with exporting data to excel.
I tried to adapt your code above by changing the website referenced in this line.

.Navigate "http://ca.ishares.com/broker_dealer/index.do"

But the exported data is all in a single cell, as opposed to splitting it into a similar row/column table.

Is there a way to fix the formatting of the output so it's split into x rows and y columns ...instead of just dumping into a single cell?

Many thanks,
Lijon

 

[Kreiz]

GetOneTable Marvelous!


Thank you!

 

[Kreiz]

Would have to provide more information....and probably start a new thread, IDK

 

[aecquam]

Matt

Is it just the main table (High Cost Disbursement...) you want the data from?

Perhaps this will give you a start.

Sub GetDisbursement()
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.usac.org/hc/tools/disbursements/default.aspx"
        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

The SAC is hard-coded but the code can easily be adapted to work with multiple codes/worksheets etc.

Helped me.. Thanks..