Using VBA to search for search for a specific Class Element in DOM

[VickyAlice100]

Hi all,

I’m fairly new to VBA and I’ve been struggling to write some coding that would search webpage’s DOM for a specific named class element and confirm if it is present or absent in the DOM. I don’t need to import any data from the webpage, I just need the code to check if the class element is present and return a ’yes’ or ‘no’ value into the spreadsheet.

Any ideas how this might be done?

Thanks in advance for any help ?

 

[John_w]

Welcome to MrExcel forums.

The basic code would be:

Public Sub IE_Find_Class()

    Dim IE As Object
    Dim classElement As Object
        
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .navigate "https://www.mrexcel.com/board/forums/excel-questions.10/"        'CHANGE URL
        While .Busy Or .readyState <> 4: DoEvents: Wend
    End With
    
    Set classElement = IE.document.getElementsByClassName("structItem-title")(0)    'CHANGE CLASS NAME
    If Not classElement Is Nothing Then
        ActiveSheet.Range("A1").Value = "yes"
    Else
        ActiveSheet.Range("A1").Value = "no"
    End If
               
End Sub

which searches this forum for elements with the class name "structItem-title". Change the URL and class name as required.

The macro could be changed to a UDF with a few changes.

 

[VickyAlice100]

Hi John,

Thanks so much for this.

I've tried using the code and most of it seems to work, except the line where it should check if the element is present or not on the page. For some reason, that's not working. I've tried tinkering about, but I've not had any luck.

Here's what I've written so far (I've removed the website link):

Sub Browsetosite()

Dim IE As New SHDocVw.InternetExplorer
Dim htmldoc As MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLButtons As MSHTML.IHTMLElementCollection
Dim HTMLButton As MSHTML.IHTMLElement
Dim classElement As Object

IE.Visible = True
IE.Navigate "WEBSITE"

Do While IE.ReadyState <> READYSTATE_COMPLETE

Loop

Set htmldoc = IE.Document
Set HTMLInput = htmldoc.getElementById("SearchTextInput")
Set HTMLButtons = htmldoc.getElementsByTagName("button")

HTMLInput.Value = "SEARCH TEXT"

HTMLButtons(1).Click

Do While IE.ReadyState <> READYSTATE_COMPLETE

Loop

Set classElement = htmldoc.getElementsByClassName("CLASS NAME")

If classElement Is Nothing Then

    ActiveSheet.Range("B1").Value = "No result found"
    
Else
    ActiveSheet.Range("B1").Value = "Requires checking"

End If
    
End Sub

What I am trying to do, is to paste a term in to a search bar and then return a value into the spreadsheet depending on whether the search yields results or not. I know that when a page does yield results a specific class name will appear - I hope that makes sense.

Many thanks again, for your help with this!

 

[John_w]

Change:

Set classElement = htmldoc.getElementsByClassName("CLASS NAME")

to:

Set classElement = htmldoc.getElementsByClassName("CLASS NAME")(0)

because getElementsByClassName returns an array/collection of elements and you want the first (0) element.

 

[VickyAlice100]

Hi John,

Sorry, I had originally removed the (0) because even with it in, it didn't work. So I tried removing it and doing something else, to see if it would work but that also failed, so I'm not sure what is falling over.

 

[John_w]

Difficult to help without the URL. Apart from not finding the class, does your code work? That is, is IE displaying the expected search results? Are they in a frame?

Replace the 2nd Do While loop with this:

        While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

Add these lines immediately before the Set classElement = line:

    Debug.Print InStr(1, HTMLdoc.body.outerHTML, "CLASS NAME", vbTextCompare)
    Stop
    Debug.Print InStr(1, HTMLdoc.body.outerHTML, "CLASS NAME", vbTextCompare)

The Debug.Print lines do a case-insensitive search for "CLASS NAME" in the whole HTML body source and output to the VBA Immediate Window (press Ctrl+G to see it) and the Stop statement pauses code execution. At that point press F8 twice to execute the 2nd Debug.Print. If the first Debug.Print displayed zero, and the second Debug.Print displayed non-zero it means there is a timing issue and the element with the specified class name is not available at the time the Set classElement line attempts to access it. If both are zero then it's likely the results are in a frame.

 

[VickyAlice100]

Hi John,

Yeah, it's really weird, I followed your instructions and when I hit F8 x2 it flagged 0 0. I tested it for both conditions where the class name is present in the DOM and where it's not, and they both flagged 00. In excel, which ever condition I test it returns the same value, which is 'yes' the class is present - however, this is not true in the condition where no search results were found. I've double checked the DOM using the find function, and it confirms the class isn't present, so I'm a bit stumped. Unfortunately, the website can only be accessed with login credential which adds a further layer of complexity.

 

[John_w]

You didn't answer my question.

I tested it for both conditions where the class name is present in the DOM and where it's not, and they both flagged 00.

That suggests the search results (and the elements with the class name) are in a frame, in which case you need to access the frame's HTMLDocument. There should be code on the forum showing how to do that.

In excel, which ever condition I test it returns the same value, which is 'yes' the class is present - however, this is not true in the condition where no search results were found.

That doesn't make sense. Are you saying that the classElement object is always defined (i.e. Not Nothing)?

 

[VickyAlice100]

Hi John,

Sorry for not replying sooner! The code is now working fine - I’m not sure why it wasn’t behaving as it should before, but when I came back to it today to tinker about with it, it ran fine.

Thank you so much for your help, this has been a really useful learning experience!