VBA Data Scraping from Websites

Question

document.getElementsByTagName("tr").length returns zero when I execute a VBA script to find the number of tr elements on a specific web page

Sub AutomaticMode()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "https://www.example.com/"
Do While IE.readyState < 4
    Application.StatusBar = "DOM Loading ..."
Loop
Set username_field = IE.document.getElementByID("username")
username_field.Value = "username"
Set password_field = IE.document.getElementByID("password")
password_field.Value = "password"
SendKeys "{Tab}{Enter}"
Do While IE.readyState < 4
    Application.StatusBar = "DOM Loading ..."
Loop
Dim trList As IHTMLElementCollection
Set trList = IE.document.getElementsByTagName("tr")
MsgBox (trList.Length)
End Sub

trList.Count makes the program return an error: "Object doesn't support this property or method" trList is an IHTMLElementCollection if that is of any help — nvkrj
– nvkrj, Commented Mar 30, 2016 at 16:58
If you put a break on the Set trList = ... line and wait a little while before continuing execution, does that change the outcome ? — Tim Williams
– Tim Williams, Commented Mar 30, 2016 at 17:35
Try declaring trList like this instead: Dim trList As Object, then try using .count — CaffeinatedMike
– CaffeinatedMike, Commented Mar 30, 2016 at 17:35
i just tested that for 'div' it is returning the correct length - 18 divs total - but for tr it is not and the page is not showing any frames in its source code as well (considering that the tr's may be in a separate frame) — nvkrj
– nvkrj, Commented Mar 30, 2016 at 17:42

Ryszard Jędraszyk · Accepted Answer · 2016-03-30 17:53:31Z

2

No tr tags found on your website can be caused by macro being executed before page fully loads. Try changing:

Do While IE.readyState < 4

to:

Do While IE.readyState < 4 or IE.Busy

Even this doesn't guarantee that everything loads before firing macro, but often helps and is a good practice to always include it. Sometimes you need to find object which displays "loading" etc. in frontend and loop through HTML elements until it disappears - then you are safe to run your script.

edited Mar 30, 2016 at 17:53

answered Mar 30, 2016 at 17:46

Ryszard Jędraszyk

2,4414 gold badges26 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SierraOscar Over a year ago

Good point, but I would go for Or instead of And just to be extra safe. Still doesn't guarantee anything though.

nvkrj Over a year ago

Thanks! Your answer is correct. Although Do While IE.readyState < 4 and IE.Busy didn't return the results, but the problem was that the 'tr' elements were loading sometime after the page loaded. On putting the script on Wait for 5 seconds before it started looking for tr's, it finally returned them! Thanks again!!

Ryszard Jędraszyk Over a year ago

It is true that the statement should be with "or", not "and", my mistake as I got used to condition with "until".

Collectives™ on Stack Overflow

VBA Data Scraping from Websites

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related