Web Scraping data from a table with VBA?

**nobodyukno** · 09-23-2017, 08:32 AM

I am working on an Excel VBA project to scrape some specific information from a website. The view of this data on the website is as such:

ukVGe.png

What I am looking to do is extract text based on two criteria: Name and post date. For example, I have the name Kaelan and the post date of 11/16/2016. I want to extract the amount of $365.

This is the HTML code:

Please Login or Register  to view this content.

This is the VBA code for my attempt.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

So the above code will return the header descriptions of the table such as "Description, Post Date, Effective, Qty, Amount." How can I use this information where I am to extract information from a table below? Since I believe the actual amounts and dates are part of a table below the table where I have extracted information from.

**AB33** · 09-23-2017, 03:57 PM

It is difficult to figure out where the tags are closed as what you have posted is not the full page.
Since this looks like a table, you have a number of options- particularly as you are using IE.

< div class="cmFloatLeft" id="divCategoryLabel_id_4541278" style="width: 430px;" >
Kaelan
< /div >
1. You do not need to loop through as each line as the page has an ID attribute, which normally is unique per page.
You get could use
MyText=doc.GetElementByID("divCategoryLabel_id_4541278").innertext
to extract Kaelan and do similar for the date and amount.

2. You could also use class name to loop through each element in the collection.
Set els = doc.getElementsByClassName("divListTableBodyLabel")
if you know the index position of each element you want to extract you could some thing like

Myamount= doc.getElementsByClassName("divListTableBodyLabel")(3).innertext ' the fourth element with in the collection.

3. You could loop through the entire div which is the root of all the tables, trs,tds and other elements.
4. Loop through table/s.
5. If you know the exact position of the data, you could use like

Myamount= doc.getElementsByTagName("tr")(1).innertext
MyDate= doc.document.getElementsByTagName("td")(1).innertext