vba - 是什么间歇性地破坏了我的 VBA 短 block ？

Question

根据 this post我已经修复了对象检查器。有时代码可以很好地运行 10 个条目，使它们全部正确，有时它可以运行 5 个条目。有时它会导致条目错误。

在获取元素的内部文本时总是会失败。当它的 Y/N 结果错误时，我根本不知道是什么原因造成的。

请帮忙!这让我抓狂。我在每个阶段都一遍又一遍地检查错误。

Sub LetsAutomateIE()

Dim barcode As String
Dim rowe As Integer
Dim document As HTMLDocument
Dim Element As HTMLDivElement
Dim text As String
Dim pos As Integer

Set ie = CreateObject("InternetExplorer.Application")
rowe = 2

While Not IsEmpty(Cells(rowe, 2))
    barcode = Cells(rowe, "B").Value
    pos = 0
    text = ""
    Set document = Nothing

    With ie
        .Visible = False
        .navigate2 "https://www.amazon.co.uk/s/ref=nb_sb_noss_1?url=search-alias%3Daps&field-keywords=" & barcode
        Do Until ie.readyState = 4
        Loop
    End With

    Set document = ie.document

    If IsObject(document.getElementById("result_0")) = False Then GoTo Here

    text = document.getElementById("result_0").innerText
    If InStr(text, "STEELBOOK") Or InStr(text, "Steelbook") Or InStr(text, "Steel book") <> 0 Then pos = 1

    If pos <> 0 Then Cells(rowe, 4) = "Y" Else Cells(rowe, 4) = "N"

    Here:

    rowe = rowe + 1
Wend
Set ie = Nothing

End Sub

这里是我正在使用的一些示例条形码。我从来没有成功地完成这些任务。

5030305517076
5030305517816
5060223767925
5060223767949
5060223767956
5060223767970
5060223767994
8717418358563
8717418365851

非常感谢，

山姆

Answer 1

一个问题是，对于某些条形码，找不到结果。如果您使用 IE.Visible = true 测试您的代码然后你会看到这样的文字:

Your search "5060223767949" did not match any products.

另一个问题是条件IsObject(document.getElementById("result_0")) = False 。这不太有效，因为 IsObject(Nothing)返回true 。更好的是使用 If <variable-name> Is Nothing Then ... .

完整代码。 HTH

' Add reference to Microsoft Internet Controls (SHDocVw)
' Add reference to Microsoft HTML Object Library

Sub LetsAutomateIE()
    Dim IE As SHDocVw.InternetExplorer
    Dim barcode As String
    Dim rowe As Integer
    Dim document As HTMLDocument
    Dim Element As HTMLDivElement
    Dim result01 As HTMLListElement
    Dim noResults As HTMLHeaderElement
    Dim text As String
    Dim pos As Integer
    Dim url As String

    rowe = 2
    url = "https://www.amazon.co.uk/s/ref=nb_sb_noss_1?url=search-alias%3Daps&field-keywords="
    Set IE = New SHDocVw.InternetExplorer

    While Not IsEmpty(Cells(rowe, 2))
        barcode = Cells(rowe, "B").Value
        pos = 0
        text = ""

        IE.Navigate url & barcode

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

        Set document = IE.document
        Set result01 = document.getElementById("result_0")

        If result01 Is Nothing Then
            Set noResults = document.getElementById("noResultsTitle")
            If Not noResults Is Nothing Then MsgBox noResults.outerText
            GoTo Here
        End If

        text = document.getElementById("result_0").innerText
        If InStr(text, "STEELBOOK") Or InStr(text, "Steelbook") Or InStr(text, "Steel book") <> 0 Then pos = 1

        If pos <> 0 Then Cells(rowe, 4) = "Y" Else Cells(rowe, 4) = "N"

Here:
        rowe = rowe + 1
    Wend

    IE.Quit
    Set IE = Nothing

End Sub

<小时/>

I'm actually looking just to check the title of the first returned product on the page...

标题显示为 h2 li 内的元素id result_0 。因此可以将搜索限制为 li元素并搜索第一个 h2元素。

' text = document.getElementById("result_0").innerText

Dim h2Elements As IHTMLElementCollection
Dim h2 As HTMLHeadElement

Set h2Elements = result01.getElementsByTagName("h2")

If h2Elements.Length > 0 Then
    Set h2 = h2Elements.Item(0)
    text = h2.innerText
    Debug.Print text
Else
    MsgBox "Text not found"    
End If

Output:

RED 2 Blu-ray Steelbook UK Exclusive
The Hunger Games With Mockingjay Pendant
The Hunger Games
The Hunger Games
Avengers Assemble BD Steelbook
Avengers Assemble Bonus Disc BD Retail