Get web page data into excel spreadsheet

[FrankMars]

Hi, I'm a beginner with Excel and I am trying to get data from a web page into my spreadsheet using VBA. I've managed to navigate to a search results page but am unable to access the data. The source code for one instance in the results list is:

<li><b>154 Morris Ave</b>, Summit City, NJ<br>
Block: <A HREF="

http://red12012.TaxRecords.com/cgi-...k=4207+&output=011&account=xxxxxxxx">4207</A>

Lot:
<A HREF="

http://red12012.TaxRecords.com/cgi-bin/TAXLOT?account=xxxxxxxx&city=2018&block=4207&lot=17">

17</A><br>
Google Map : <a href="javascript:void()" onClick="mappopup('154 Morris Ave, Summit City, NJ');">Click Here!</a><br>
Width: 66 X Depth: 102 Land: 56,500.00 Build: 147,700.00 Total <b>$204,200.00</b><br>
Sold: <b>$1.00</b> on <b>03/05/2007</b> BOOK: 5639
PAGE: 277
<br>
$0.00 - **/**/**** &nbsp;&nbsp;&nbsp;
$0.00 - **/**/**** &nbsp;&nbsp;&nbsp;
$0.00 - **/**/****<br>
<A HREF="

http://www.zipcodedatabase.com/NJ-U...-codes-population-housing-census-tract#07458"

target="_blank">Census Tract Info</A> (opens in new window)<br>
<br>

My VBA code is:

For Each ele In .document.all
Select Case ele.classname
Case "????"
RowCount = RowCount + 1
Case "Address"
sht.Range("A" & RowCount) = ele.innertext
Case "Block"
sht.Range("B" & RowCount) = ele.innertext
Case "Lot"
sht.Range("C" & RowCount) = ele.innertext
Case "Width"
sht.Range("D" & RowCount) = ele.innertext
Case "Depth"
sht.Range("E" & RowCount) = ele.innertext
End Select
Next ele

End With

Set objie = Nothing

End Sub

Thank You in advance!

 

[SkipVought]

hi,

I assume that you are coding this IN EXCEL.

Why not, then, do Data > Get External Data > From Web ...???

In this window, navigate to the page of interest and SELECT the table of interest and IMPORT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FrankMars]

Thanks Skip. I have tried your suggestion and I have 2 problems. First, each line of data imported goes into one cell which makes it 'unsortable'. For example, width and depth go into a single cell, so I am unable to sort by width. Secondly, I go for the data on a daily basis so I am trying to automate the task. The 'Data > Get External Data > From Web' requires more navigation than I would like (sign in, etc.). I have seen the code I posted above in a YouTube video which gets me thru the sign in and search criteria but I am unable to import the data into an Excel spreadsheet because the HTML source code is different. I would prefer to use VBA/HTML, can you help me with this?

 

[Larena]

Hi FrankMars.

For problem #2 - once you've got the data from the web once, you don't need to go through the exercise again. You can right-click the data in Excel and select Refresh to re-query the web page.

Larena