VBA从不同HTML类提取价格报错：对象变量未设置

Let's walk through what's going wrong and get your script working properly. That error pops up because you're trying to access innerText on an object that wasn't successfully created (meaning result2 is Nothing when neither selector finds a match, or your selectors are targeting the wrong elements entirely).

Key Issues in Your Original Code #

• Incorrect CSS Selectors: You wrote .SalePrice and .SalePrice SpecialPrice, but your actual target classes are .kuSalePrice and .kuSalePrice.kuSpecialPrice—important note: when an element has two classes, the selector combines them without spaces (spaces mean "child element", not the same element with multiple classes).
• Missing Null Checks: querySelector returns Nothing if it can't find the element. Without checking for this, trying to access innerText will throw that frustrating error.
• Flawed Logic Flow: Your conditional check doesn't handle the case where neither selector works, and the assignment logic is reversed in spots.

Corrected VBA Script #

Sub PriceCheck()
    Dim ie As InternetExplorer
    Dim doc As HTMLDocument
    Dim targetElement As IHTMLElement
    Dim url As String
    Dim lRow As Long
    
    ' Initialize IE instance
    Set ie = New InternetExplorer
    ie.Visible = False ' Set to True if you need to see the browser window
    lRow = 2
    
    Do
        url = Worksheets("Sheet1").Range("B" & lRow).Value
        If url = "" Then Exit Do ' Exit loop when we hit an empty URL
        
        ' Navigate to the URL and wait for full page load
        ie.Navigate url
        Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
        
        Set doc = ie.document
        Set targetElement = Nothing
        
        ' First try the primary class: .kuSalePrice
        On Error Resume Next ' Temporarily suppress errors if element isn't found
        Set targetElement = doc.querySelector(".kuSalePrice")
        On Error GoTo 0 ' Reset error handling
        
        ' If primary class not found, try the secondary class: .kuSalePrice.kuSpecialPrice
        If targetElement Is Nothing Then
            On Error Resume Next
            Set targetElement = doc.querySelector(".kuSalePrice.kuSpecialPrice")
            On Error GoTo 0
        End If
        
        ' Write the result to the sheet
        If Not targetElement Is Nothing Then
            ' Remove £ symbol directly instead of a separate replace step
            Worksheets("Sheet1").Range("C" & lRow).Value = Replace(targetElement.innerText, "£", "")
        Else
            Worksheets("Sheet1").Range("C" & lRow).Value = "Price not found"
        End If
        
        lRow = lRow + 1
    Loop
    
    ' Clean up IE to avoid memory leaks
    ie.Quit
    Set ie = Nothing
    Set doc = Nothing
    Set targetElement = Nothing
End Sub

What Changed & Why #

1. Fixed CSS Selectors: Now targeting the exact classes you specified, with the correct syntax for elements with multiple classes.
2. Added Null Safety: We explicitly check if targetElement exists before accessing innerText, which eliminates the "Object variable not set" error.
3. Simplified Error Handling: Used On Error Resume Next temporarily to safely attempt to find each element without crashing the script.
4. Built-in Currency Removal: Replaced the separate Cells.Replace call with a direct Replace when writing the value, making the code more efficient.
5. Proper IE Cleanup: Added ie.Quit and nulled out objects to prevent leftover IE processes running in the background.
6. Improved Loop Readability: Added an explicit exit condition for empty URLs to make the loop logic clearer.

Quick Tips #

• Ensure you have the Microsoft Internet Controls and Microsoft HTML Object Library references enabled (go to Tools > References in the VBA editor to check).
• Setting ie.Visible = False will speed up the script since it doesn't render the browser window.
• If some pages load slowly, add a small delay after the readyState check (e.g., Application.Wait Now + TimeValue("00:00:02")).

内容的提问来源于stack exchange，提问作者Jess Murray