How to get the nth item of a tag in HTML using VBA and selectors for export to Excel

Question

How can I use a selector to check if the content of "alt" is "Meeting" or "Canceled"?

    <body>
      <div class="mainBox">
           <li class="content">
               <img class="title" src="https://url_01/images/img01.gif" alt="Meeting">
           </li>
           <li class="content">
               <img class="title" src="https://url_02/images/img02.gif" alt="Meeting"> 
          </li>
           <li class="content">
               <img class="title" src="https://url_03/images/img03.gif" alt="Canceled">
          </li>
           <li class="content">
               <img class="title" src="https://url_04/images/img04.gif" alt="Canceled">
          </li>
       </div>
    </body>

Important: the selector MUST refer to class="title" because all other tags in these 4 strings occur differently often in the entire html code. Thanks

My Code:

Public Sub GetData()

    Dim ohtml As New HTMLDocument
    Dim elmt As Object
    Dim x As Long
    
    Set ohtml = New MSHTML.HTMLDocument
    
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", "https:...", False
        .send
         ohtml.body.innerHTML = .responseText
    End With
         
    For x = 0 To elmt.Length - 1
    
    Set elmt = ohtml.querySelectorAll("img[class='title']").Item(x)  '<<==  ??????
    
    If instr(????????, "Meeting") then  '<<=====  ???????
      ActiveSheet.Cells(x + 2, 2) = "Meeting"
    Else
      ActiveSheet.Cells(x + 2, 2) = "Canceled"
    End If
    
    Next
            
End Sub

CDP1802 · Accepted Answer · 2021-06-19 10:32:11Z

2

Set elmt before the loop

Public Sub GetData()

    Dim ohtml As New HTMLDocument, elmt As Object, i As Integer
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", "https:...", False
        .send
         ohtml.body.innerHTML = .responseText
    End With

    Set elmt = ohtml.querySelectorAll("img[class='title']")
    For i = 1 To elmt.Length
       'Debug.Print i, elmt(i - 1).alt, elmt(i - 1).src
       ActiveSheet.Cells(i + 1, 2) = elmt(i - 1).alt
    Next
            
End Sub

update1 : replace s, s2 with .responseText

edited Jun 19, 2021 at 10:32

answered Jun 19, 2021 at 9:21

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

7 Comments

Jasco Over a year ago

(src="xxx.gif ) =>> xxx are variable values, hence "x". i cannot declare them in advance!

CDP1802 Over a year ago

@Jasco Sorry I don't understand how the src attribute is relevant ?

Jasco Over a year ago

How declare "s" & "s2" as you described without knowing the content of src?

CDP1802 Over a year ago

@Jasco Those variables are just substitutes for the real HMTL to test the code. You would use .responseText

Jasco Over a year ago

Sorry, I don't think I have described my problem precisely enough. Above the corrected snippet

|

QHarr · Accepted Answer · 2021-06-19 10:43:37Z

1

Extend your css selector to include those additional attribute = values selectors so elmt only includes qualifying nodes, then extract the alt attribute value during the loop. And yes, set elmt before the loop.

Option Explicit

Public Sub GetData()

    Dim ohtml As New HTMLDocument
    Dim elmt As Object 'Dim elmt As MSHTML.IHTMLDOMChildrenCollection '' for later mshtml.dll updates May 2021 onwards
    Dim x As Long
    
    Set ohtml = New MSHTML.HTMLDocument
    
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", "https:...", False
        .send
         ohtml.body.innerHTML = .responseText
    End With
    
    Set elmt = ohtml.querySelectorAll("img[class=title][alt=Canceled], img[class=title][alt=Meeting]")
    
    For x = 0 To elmt.Length - 1
      ActiveSheet.Cells(x + 2, 2) = elmt.Item(x).alt
    Next
            
End Sub

answered Jun 19, 2021 at 10:43

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

1 Comment

Jasco Over a year ago

@QHarr...Hello Sir Genius :-) Is there any possibility to solve this: stackoverflow.com/questions/78656828/…

Collectives™ on Stack Overflow

How to get the nth item of a tag in HTML using VBA and selectors for export to Excel

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related