VBA and HTML Tables

[zayd_88]

Hey There,

Need help passing html tables from multiple pages from the same website into custom coloumns in an Excel sheet. I attempted using VBA to do this but I failed... Heres the Site:http://www.asx.com.au/asx/research/listedCompanies.do... Each company table has a unique code linked to a page with its details in an html page. I need to grab the company's name, address, phone number and a point of contact which I noticed is located on a different table... Anyway this is my current attempt.. Note this isnt completed...

Sub GetComp()
Dim ie As Object, allTables As Object, compTable As Object, posTable As Object, tblRow As Object, tblCell As Object
Dim myArr() As Variant, code As Range, y As Worksheet, cc As String
Dim compArrayTable() As String, posArrayTable() As String
Dim compRef As Long, posRef As Long, sheetRef As Long
Dim comp As String


Set ie = CreateObject("InternetExplorer.Application")
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
Sheet1.Range("b1:h1").Value = Array( _
    "Company", "Address", "Suburb", "State", "Postcode", "Name", "Postion")

With ie
    For Each code In Range([q1], [q1027].End(3))
                    
                    
         cc = code.Value
         .navigate "http://www.asx.com.au/asx/research/companyInfo.do?by=asxCode&asxCode=" & cc
         Do While .Busy And .readyState <> 4
         Loop
        With .document
          
          Set compTable = .all.tags("table").Item(3)
          Set posTable = .all.tags("table").Item(4)
         
        
         
         ReDim compArrayTable(1 To compTable.Rows.Length - 2, 1 To 5)
         ReDim posArrayTable(1 To posTable.Rows.Length - 2, 1 To 2)
         
        End With
    Next
       
      
End With
        
End Sub

Any help will be greatly appreciated.

Thanks
Zayd

 

[zayd_88]

loops through the rows

What part of the code indicates the looping through the rows? And how would I get it to start at the second row?

 

[Kyle123]

Ok, I've changed your code, this will dump the data into columns B-E, no need to declare a variable as you are already doing that implicitly whilst looping through the range, for example the adjusted code below:

Sub setTD()
Sheet1.Range("b1:h1").Value = Array( _
    "Company", "Address", "Suburb", "State", "Postcode", "Name", "Postion")
 For Each Code In Range([q1], [q1027].End(3))
        With Code
            .Offset(, -15).Resize(, 4).Value = getTd(.Value)
        End With
    Next Code
End Sub

could be written as:

Sub setTD()
Sheet1.Range("b1:h1").Value = Array( _
    "Company", "Address", "Suburb", "State", "Postcode", "Name", "Postion")
    For Each Code In Range([q1], [q1027].End(3))
        Code.Offset(, -15).Resize(, 4).Value = getTd(.Value)
    Next Code
End Sub

So all you're doing is offsetting the active cell (Q whatever) 15 columns to the right, making it four columns wide and then dumping the array data directly into that 4 column group.

Haha don't worry about the cash for beer, had far too much after this weekend's jubilee celebrations!

 

[Kyle123]

What part of the code indicates the looping through the rows? And how would I get it to start at the second row?

This bit:

For Each Code In Range([q1], [q1027].End(3))

You're saying for every cell between q1 and the last used row in column q (row 1027 upwards) do something.

So to change it to start at q2:

For Each Code In Range([q2], [q1027].End(3))

 

[zayd_88]

*face palm* Im so dense sometimes... Yeah celebrations are still going on right? Thats crazy!?! She has a 3 day celebration. So youre from the UK? Make your way down to London to see the boat show? Or concert?

 

[zayd_88]

Anyway Thanks again mike

 

[Kyle123]

Yep it's all good Nah, just had BBQs in the garden to make the most of the good(ish) weather. I'm back at work today though