Excel VBA - For Loop Need To Skip First Element In Web Scrape

Question

I'm fairly new/inexperienced at VBA and I'm (seemingly) one small step from finishing a project that been kicking my butt. For some background, I'm scraping a website and navigating to the appropriate "tbody" tag and iterating through the encompassed "tr" tags. In each tr I am expecting two "td" cells. The problem is the first tr has a "th" tag with a colspan of 2, meaning obviously there is only one child in that tr and the code crashes out. What I don't (yet) know how to do is to skips or account for the exception in the first tr. Here is the relevant snippet of the code:

R = 1

For Each ele In IE.Document.getElementsByTagName("tbody")(13).getElementsByTagName("td")


WSW.Range("H" & R).Value = ele.Children(0).textContent
WSW.Range("I" & R).Value = ele.Children(1).textContent

R = R + 1

Next

Not sure if I've walked myself down a rabbit hole, if the code is salvageable, or if the slate for this section of the code should be recreated. Thank you for any help.

if not ele = "th" then ........... End if R = R + 1 Next Just throwing that out there. — addohm
– addohm, Commented Oct 17, 2018 at 2:20
OK, just tried that, am I missing extra components to that because it's still erroring out. Thank you. — Ryan
– Ryan, Commented Oct 17, 2018 at 2:34
I really don't know. What is the error? Why don't you catch the error and let us know what it is? — addohm
– addohm, Commented Oct 17, 2018 at 2:38
Run-time error '424': Object required. This occurs on the WSW.Range("I" & R).Value = ele.Children(1).textContent line since there is a th that has a colspan of 2. The if statement isn't triggering it seems. — Ryan
– Ryan, Commented Oct 17, 2018 at 2:40

user4039065 · Accepted Answer · 2018-10-17 03:25:11Z

3

The index of getElementsByTagName is zero to n based but the count (aka .length) is one to n based. You use R as the index reference so it does double duty.

dim R as long
R = 1

For R=1 to  IE.Document.getElementsByTagName("tbody")(13).getElementsByTagName("td").length -1

    with IE.Document.getElementsByTagName("tbody")(13).getElementsByTagName("td")(R)
        WSW.Range("H" & R).Value = .Children(0).textContent
        WSW.Range("I" & R).Value = .Children(1).textContent
    end with

Next R

answered Oct 17, 2018 at 3:25

user4039065

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

1 Comment

Ryan Over a year ago

Excellent, this is it. I had to make one small tweak which was the "td"s in your solution should be "tr"s since it goes tbody->tr->td and it's the children of the tr's I'm after. Simple fix though and much appreciated. I'll mark your answer as the solution.

Collectives™ on Stack Overflow

Excel VBA - For Loop Need To Skip First Element In Web Scrape

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related