lifeofjansen lifeofjansen - 2 months ago 28
HTML Question

VBA to parse and retrieve data from HTML to Excel

I'm trying to write a macro in VBA to transfer data from a website to a spreadsheet. My problem is that my code does not yield any results for href, innertext, etc. Can you help advise where my code is wrong?

Here is a sample of the html--I am trying to retrieve the lot number 41151 and the description.

<tr class=''>
<td class='lot_column'>
<div class='lot_number'>
<span class='lot_digits'>41151</span>
<br />
<a href='property_information.pl?lot=41151' class='sd_button blue'><span>View Data</span></a>
</div>
</td>
<td>
<div class='short_desc'>Michael L. Hanisko, A/I/F for Shirley Isackson - Winterfield Richfield Unit and Grout Unit (Producing MI/RI) - Clare and Gladwin Counties, Michigan</div>
<div class='headline'>7 Mo. Av. Cash Flow $15/mo</div>
<div class='message'></div>
</td>
<td class='high_bid'>
<div class='high_bid'>$810</div>
<div class='reserve_status'>No&nbsp;Reserve</div>
</td>
<td class='lot_close'>
<span class='closing_date'>8/29/2017 1:35 PM</span>
<br />
<a href='bidder_bid.pl?lot=41151' class='sd_button red'><span>Make Bid</span </a>
</td>


Here is a segment of my code:

Set Lot_Data = EnergyNet.getElementsByClassName("lot_digits")
For Each elem In Lot_Data
Set Link = elem.getElementsByTagName("tr")(0)
i = i + 1
'Copy Data to Excel
Worksheets("Sheet1").Activate
Range("B" & i) = Link.href
Range("C" & i) = Link.innerText
Range("D" & i) = Link.ID
Next


Any idea where I went wrong? Thanks for your help.

Answer Source

You get

<span class='lot_digits'>41151</span>

then from this you want to get an element with tag name of "tr" but in the span, with the lot_digits class there isn't any element with tr as tag name so the Link variable will be empty.

For the 41151 element you have to

Set Lot_Data = EnergyNet.getElementsByClassName("lot_digits")

then

Lot_Data(0).innerText will contain 41151

For the description, simply

Set descriptionElements = EnergyNet.getElementsByClassName("short_desc")

and

descriptionElements(0).innerText 

will contain the description