Cannot get the text of nth span element using vba

Question

I have the following html part

<div class="description">
<span>Brand:</span> 
<a href="http://www.bestfamily.gr">Nikon</a><br/>
<span>Product Code:</span> 130342 <br/>
<span>Barcode</span> 18208948581 <br/>
<span>Availability:</span>Available</div>

I am trying to get the last span and the word Available using the following

Set availability = ie.Document.getElementsByClassName(".description").getElementsByTagName("span")(2)
wks.Cells(i, "D").Value = availability.innerText

But it shows all span texts What I am doing wrong here?

I'm not really familiar with HTML and VBA. Maybe consider this question for reference: stackoverflow.com/questions/42741997/get-all-innertext-vba — prextor
– prextor, Commented Dec 11, 2018 at 10:51
Try changing ie.Document.getElementsByClassName(".description").. to ie.Document.getElementsByClassName("description")... Remove . from infront of description — Zac
– Zac, Commented Dec 11, 2018 at 11:08
Try using an elements collection and then use it's properties for the count — Nathan_Sav
– Nathan_Sav, Commented Dec 11, 2018 at 11:27

QHarr · Accepted Answer · 2018-12-11 15:42:16Z

1

Use last-child css pseudo class in descendant combination with parent element class selector.

.description span:last-child

The :last-child CSS pseudo-class represents the last element among a group of sibling elements.

Applying:

single match

Set availability = ie.document.querySelector(".description span:last-child")
Cells(1,1) = availability.innerText

all matches

Set availability = ie.document.querySelectorAll(".description span:last-child")
Cells(1,1) = availability.item(0).innerText

Otherwise, you can return the span collection from that parent class and index into it

Set availability = ie.document.querySelectorAll(".description span")
Cells(1,1) = availability.item(2).innerText '<==choose your index here

Or even chain:

Set availability = ie.document.querySelector(".description span + span + span")  '<==expand as required. This uses [adjacent sibling combinator][4].

Sadly, pseudo classes nth-of-type / nth-child are not supported in VBA implementation though you can in many other languages e.g. python.

—-

If after just the Available you should be able to use .description as your selector to return all the text in the div. Then use Split on the .innerText using Chr$(32) to split by and extract the UBound (I.e. the last element of the generated array)

Set availability = ie.document.querySelector(".description")
Dim arr() As String
arr = split( availability.innerText, ":")
Cells(1,1) = arr(UBound(arr))

edited Dec 11, 2018 at 15:42

answered Dec 11, 2018 at 12:49

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

4 Comments

user10358702 Over a year ago

Yes but I get only the word Availability: inside span, not the text Available which belongs to div? I am not sure

QHarr Over a year ago

@Nikos select the div by class name. Split the .innertext and take the ubound

user10358702 Over a year ago

you mean Set availability = ie.document.querySelectorAll(".description div") Cells(1,1) = availability.innertext.unbound ?

user10358702 Over a year ago

instead of chr$(32) I just put ":" and I got the last word thank you very much

TinMan · Accepted Answer · 2018-12-11 11:29:46Z

0

As Zac pointed out in the comments, you shouldn't use a period . with the getElementsByClassName method.

ie.Document.getElementsByClassName is returning a DispHTMLElementCollection of elements. You need to specify which element you want to reference

Set availability = ie.Document.getElementsByClassName(".description")(0).getElementsByTagName("span")(2)

A better way to write the write the code would be to reference the Microsoft HTML Object Library and create a variable to test each element returned. Unfortunately, there is a bug in the DispHTMLElementCollection implementation, so you will need to use Object instead of DispHTMLElementCollection.

Dim doc As HTMLDocument
Dim availability As Object
Set doc = ie.Document
Set availability = doc.getElementsByClassName("description")

Dim div As HTMLDivElement
Dim span As HTMLSpanElement
Dim spans As Object

For Each div In availability
    Set spans = div.getElementsByTagName("span")
    For Each span In spans
        Debug.Print span.innerText
    Next
Next

Output

answered Dec 11, 2018 at 11:29

TinMan

7,8142 gold badges13 silver badges24 bronze badges

1 Comment

user10358702 Over a year ago

Yes but I want not the titles above but the last word Available

Collectives™ on Stack Overflow

Cannot get the text of nth span element using vba

2 Answers 2

4 Comments

Output

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Output

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related