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...
Any help will be greatly appreciated.
Thanks
Zayd
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...
Code:
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
