时间戳

首页 > 解决方案 > Error handling when 'getElementById' can't find object/value

excel - Error handling when 'getElementById' can't find object/value

问题描述

I have an excel file that contains ID numbers in column C starting at the second row.

There's about 4 cells that need to be skipped because they have a different value (hence the len > 8 if statement).

The script loops through the columns. Uses the ID to concatenate a hyperlink, that the script then uses to read out a specific piece of data on that website. The 11th character of that string is compared to the cell value to the right of the active cell. If it's the same, the cell is made white. If it's different, the cell is highlighted in yellow. Basically this script will alert the excel user if the data is different from the website.

My script works fine when if can find the data. However, in about 30% of cases, the value that my 'get elementbyid' statement searches for does not exist. I need to alter my script to skip through these cases and can not get it right.

Current script:

Sub specs()
Range("C2").Activate
Do While ActiveCell.Value <> ""

If Len(ActiveCell.Value) > 8 Then

    Dim YouthID As String
    YouthID = ActiveCell.Value

    Dim URLstart As String
    URLstart = "https://www.hattrick.org/en/Club/Players/YouthPlayer.aspx?YouthPlayerID="

    Dim URL As String
    URL = URLstart & YouthID

    Dim appIE As Object
    Set appIE = CreateObject("internetexplorer.application")
    With appIE
    .Navigate URL
    .Visible = True
    End With

    Do While appIE.Busy
        DoEvents
    Loop

    Set allrowofdata = appIE.document.getElementById("ctl00_ctl00_CPContent_CPMain_trSpeciality")
    
    Dim myValue As String: myValue = allrowofdata.Cells(1).innerHTML

    Dim result As String
    result = Mid(myValue, 11, 1)
    
    appIE.Quit
    Set appIE = Nothing
    
    If ActiveCell.Offset(0, 1).Value <> result Then
        ActiveCell.Offset(0, 1).Interior.ColorIndex = 6
    Else
        ActiveCell.Offset(0, 1).Interior.ColorIndex = 0
    End If
    
End If

ActiveCell.Offset(1, 0).Select

Loop
End Sub

When this script encounters a web page that doesn't contain the value I get a 'type mismatch' error on the getelementbyid line.

Searching through stackkoverflow I found 3 possible solutions:

Set allrowofdata = appIE.document.getElementById("ctl00_ctl00_CPContent_CPMain_trSpeciality")
If IsObject(allrowofdata) Then
Dim myValue As String: myValue = allrowofdata.Cells(1).innerHTML
Dim result As String
result = Mid(myValue, 11, 1)
Else
result = ""
End If

If Not appIE.document.getelementbyid("ctl00_ctl00_CPContent_CPMain_trSpeciality") Is Nothing Then
Set allrowofdata = appIE.document.getelementbyid("ctl00_ctl00_CPContent_CPMain_trSpeciality")
Dim myValue As String: myValue = allrowofdata.Cells(1).innerHTML
Dim result As String
result = Mid(myValue, 11, 1)
Else
result = ""
End If

Dim result As String
result = ""
        
On Error Resume Next
Set allrowofdata = appIE.document.getelementbyid("ctl00_ctl00_CPContent_CPMain_trSpeciality")
On Error Resume Next
Dim myValue As String: myValue = allrowofdata.Cells(1).innerHTML
On Error Resume Next
result = Mid(myValue, 11, 1)

Can someone tell me what the final fix is? And preferably also what's wrong in the first two scenario's.

标签： excelvbagetelementbyid

解决方案

既然你知道allrowofdata可能什么都不是，你需要提防这种情况。因此，在 Set 语句之后立即添加一个 If 块：

On Error Resume Next
Set allrowofdata = appIE.document.getElementById("ctl00_ctl00_CPContent_CPMain_trSpeciality")
On Error GoTo 0

If Not allrowofdata Is Nothing then
   Dim myValue As String: myValue = allrowofdata.Cells(1).innerHTML

   Dim result As String
   result = Mid(myValue, 11, 1)

   appIE.Quit
   Set appIE = Nothing

   If ActiveCell.Offset(0, 1).Value <> result Then
      ActiveCell.Offset(0, 1).Interior.ColorIndex = 6
   Else
      ActiveCell.Offset(0, 1).Interior.ColorIndex = 0
   End If
End If

推荐阅读