Excel 2013宏调用IE批量查询许可证号异常处理需求

Got it, let's fix this issue so your macro can keep running even when some license numbers throw site errors. The core fix is adding error trapping to your VBA code—this lets the code detect when a query fails, mark the result appropriately, and move on to the next license instead of crashing.

Here's a revised, robust version of your macro with error handling built in:

Sub QueryLicenseNumbers()
    Dim ie As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim licenseNum As String
    Dim resultText As String
    
    ' Set your target worksheet (update "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Assumes licenses are in column A
    
    ' Initialize Internet Explorer
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False ' Set to True if you want to watch the browser during testing
    
    ' Loop through each license number
    For i = 2 To lastRow ' Start at row 2 if row 1 is a header
        licenseNum = Trim(ws.Cells(i, "A").Value)
        
        ' Skip empty cells to avoid unnecessary queries
        If licenseNum = "" Then
            ws.Cells(i, "B").Value = "Empty License Number"
            GoTo NextLicense
        End If
        
        ' Turn on error handling for this query
        On Error Resume Next
        
        ' Navigate to your query page (replace with your actual search URL)
        ie.Navigate "https://your-query-site.com/search?license=" & licenseNum
        
        ' Wait for the page to finish loading
        Do While ie.Busy Or ie.ReadyState <> 4
            DoEvents
        Loop
        
        ' Check if navigation failed (site error/timeout)
        If Err.Number <> 0 Then
            ws.Cells(i, "B").Value = "Query Failed (Site Error)"
            Err.Clear ' Reset error flag for next iteration
            GoTo NextLicense
        End If
        
        ' Attempt to scrape the result (adjust this to match your site's HTML structure)
        On Error Resume Next ' Catch errors if the result element doesn't exist
        resultText = ie.Document.querySelector(".result-details").innerText ' Example selector
        
        If Err.Number <> 0 Then
            ws.Cells(i, "B").Value = "No Result Found"
            Err.Clear
        Else
            ws.Cells(i, "B").Value = resultText
        End If
        
        ' Reset error handling to default for the next loop
        On Error GoTo 0
        
NextLicense:
    Next i
    
    ' Clean up resources
    ie.Quit
    Set ie = Nothing
    MsgBox "Query process finished!"
End Sub

Key Fixes & Explanations: #

• On Error Resume Next: This tells VBA to continue running instead of crashing when an error occurs. We use it around high-risk steps (navigating to the site, scraping results) where failures are expected.
• Error Checking: After each risky operation, we check Err.Number to see if something went wrong. If an error is detected, we mark the result and jump to the next license with GoTo NextLicense.
• Empty Cell Skip: We added a check to skip blank license numbers, so the macro doesn't waste time on empty cells.
• Error Reset: Err.Clear ensures that errors from one iteration don't affect the next one.

Additional Tips: #

• Test with Visible IE: Set ie.Visible = True while testing to see exactly what happens when a query fails—this helps you tweak the error handling if the site behaves unexpectedly.
• Adjust Scraping Logic: The result-scraping line (resultText = ...) depends on your target site's HTML. Use your browser's inspect tool to find the correct element selector (like getElementById, getElementsByClassName, or querySelector) for the result details.
• Add Delays (If Needed): If the site loads content dynamically with JavaScript, add a short delay after the page-loading loop: Application.Wait Now + TimeValue("00:00:02").

内容的提问来源于stack exchange，提问作者C LeRoy