How to skip a line when an html element does not return a value?

Question

Here is a breakdown of what I am trying to accomplish:

1)Take a value from a list in the spreadsheet

2)Search a URL with that value

3)Take an ElementId from the HTML and add this to the spreadsheet (this would not need to be added to the spreadsheet, but I need it for the next search and was not sure how to do it otherwise)

4)Use the ElementId to search another URL

5)Pull the value of a ClassName from the HTML

My problem comes when a value in the list does not have an IDValue from my search in the HTML's ElementId. I need to skip those values and GoTo NextName, but have not found a successful way of doing so.

I have tried adding a timer to the Do Loop, but still have not been able to advance to the next i in the list.

Here is my code:

Option Explicit

Public Sub CheckNames()
    Dim ws As Worksheet, i As Long, IE As InternetExplorerMedium
    Dim doc As HTMLDocument, docElement As Object
    Dim docClass As Object

Set ws = ActiveSheet
Set IE = New InternetExplorerMedium

1)

For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    IE.Visible = False

2)

    IE.navigate "Beginning of URL" & ws.Cells(i, 1).Value & "End of URL"
    Do
        DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE

    IE.Refresh

    Set doc = IE.document

3)

    Do
        Set docElement = Nothing
        On Error Resume Next
        Set docElement = doc.getElementById("IDValue")
        On Error GoTo NextName
        DoEvents

    Loop Until Not docElement Is Nothing

    ws.Cells(i, 3).Value = Right(Trim(docElement.innerText), 5)

4)

    IE.navigate "Beginning of another URL" & ws.Cells(i, 3).Value & "End of URL"

5)

    Do
        Set docClass = doc.getElementsByClassName("CLass Name in HTML")(3)

        DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE

    ws.Cells(i, 4).Value = Trim(docClass.innerText)

    Set doc = Nothing
    Set docElement = Nothing

NextName:
Next i
IE.Quit
End Sub

So in short, I am looking for a way to skip to the next i in the list when doc.getElementById("IDValue") does not have a value.

Thanks in advance!

@velblúd I think my question is similar to Continue For Loop but I am dealing with gathering elements from a websites HTML, so I believe my problem needed a time out function for the loop as well. Let me know if this is incorrect or if I need to change something. — C.lei
– C.lei, Commented Sep 17, 2018 at 16:55
@C.lei Oh, I see, continue solves just half of your problem. I didn't realized that the issue was that your code ended up in endless loop. Anyway, the solution of QHarr is nice and clear. :) — velblúd
– velblúd, Commented Sep 17, 2018 at 17:58

QHarr · Accepted Answer · 2018-09-17 16:09:47Z

Some basic pointers:

As a minimum you need a timeout on your loop to prevent infinite looping
You can use an On Error Resume Next ..... On Error GoTo 0 to wrap your attempt to set an element
Then a conditional execution of the rest of the code before the Next i based on If Not docElement Is Nothing Then i.e. element was found and set
Added some typed functions for efficiency

Did a little tidy up of some of the rest of the code but not a full review.

Option Explicit
Public Sub CheckNames()
    Dim ws As Worksheet, i As Long, IE As InternetExplorerMedium
    Dim doc As HTMLDocument, docElement As Object
    Dim docClass As Object, t As Date
    Const MAX_WAIT_SEC As Long = 5

    Set IE = New InternetExplorerMedium

    For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

        IE.Visible = False
        IE.navigate "Beginning of URL" & ws.Cells(i, 1).Value & "End of URL"

        While IE.Busy Or IE.readyState < 4: DoEvents: Wend

        IE.Refresh

        Set doc = IE.document
        t = Timer

        Do
            DoEvents
            Set docElement = Nothing
            On Error Resume Next
            Set docElement = doc.getElementById("IDValue")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop Until Not docElement Is Nothing

        If Not docElement Is Nothing Then
            ws.Cells(i, 3).Value = Right$(Trim$(docElement.innerText), 5)

            IE.navigate "Beginning of another URL" & ws.Cells(i, 3).Value & "End of URL"

            While IE.Busy Or IE.readyState < 4: DoEvents: Wend
            Set docClass = doc.getElementsByClassName("CLass Name in HTML")(3)

            ws.Cells(i, 4).Value = Trim$(docClass.innerText)

        End If
    Next i
    IE.Quit
End Sub

Excellent! Thank you for the quick response! I'm fairly new to VBA, so thanks for the clean up as well!

Collectives™ on Stack Overflow

How to skip a line when an html element does not return a value?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related