VBA中用MSXML替代IE出现运行时错误91：对象变量未设置

Hey there! Let's figure out why you're hitting that Runtime Error 91 ("Object variable or With block variable not set") when switching from IE to MSXML for fetching web pages. First, let's recap the issue: you're processing ~10k pages to pull data into Excel, IE is unstable, but your MSXML function isn't working as expected. Let's fix this step by step.

Your Original IE Function (Filled in Missing Bits) #

Here's your IE-based code, completed with standard logic for page loading:

Function getIEPage(link) As HTMLDocument
    Dim objIE As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    With objIE
        .Top = 0
        .Left = 0
        .Width = 800
        .Height = 600
        .Visible = False ' Hide IE for batch processing
        .Navigate link
        ' Wait for page to fully load
        Do While .Busy Or .ReadyState <> 4
            DoEvents
        Loop
        Set getIEPage = .Document
        .Quit
    End With
    Set objIE = Nothing
End Function

Why You're Getting Error 91 #

The error pops up because MSXML works fundamentally differently than IE:

• IE loads the full browser environment and hands you a ready-to-use HTMLDocument via its .Document property.
• MSXML only returns raw text content from the server. If your MSXML function didn't properly initialize an HTMLDocument object and load the raw response into it, you'd end up trying to return an unassigned variable—hence the "object not set" error.

Working MSXML Function (Fixes Error 91) #

Here's a stable MSXML-based function that mirrors your IE function's output, with comments explaining key steps:

Function getMSXMLPage(link As String) As HTMLDocument
    Dim xmlHttp As Object
    Dim htmlDoc As Object ' Late binding to avoid reference requirements
    Dim responseText As String
    
    ' Initialize MSXML HTTP object (v6.0 has best compatibility)
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Handle errors to prevent crashes during batch processing
    On Error GoTo Cleanup
    
    With xmlHttp
        .Open "GET", link, False ' Synchronous request for predictable batch handling
        ' Mimic a browser to avoid being blocked by sites
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
        .Send
        
        ' Verify the request succeeded
        If .Status <> 200 Then
            Debug.Print "Failed to fetch: " & link & " | Status code: " & .Status
            GoTo Cleanup
        End If
        
        responseText = .ResponseText
    End With
    
    ' Convert raw text to a usable HTMLDocument
    Set htmlDoc = CreateObject("HTMLFile")
    htmlDoc.write responseText
    htmlDoc.close ' Critical step to parse the DOM correctly
    
    ' Assign the parsed document to the function return
    Set getMSXMLPage = htmlDoc
    
Cleanup:
    ' Clean up objects to avoid memory leaks
    Set xmlHttp = Nothing
    ' If an error occurred, this returns Nothing—handle that in your calling code
End Function

Key Tips for Batch Processing 10k Pages #

• Late Binding: I used CreateObject instead of early references so you don't need to enable library references in the VBA editor. If you prefer early binding, add references to Microsoft XML, v6.0 and Microsoft HTML Object Library, then declare variables as MSXML2.XMLHTTP60 and HTMLDocument.
• Synchronous Requests: The function uses synchronous requests (False in .Open) which is better for batch work—it waits for each page to load before moving on, making error handling simpler. Asynchronous requests are faster but require complex event handling.
• Error Resilience: The On Error GoTo Cleanup block ensures a single failed request won't crash your entire batch. You can expand this to log failed links to a worksheet for later review.
• User-Agent Spoofing: Many sites block MSXML requests because they don't look like a browser. Adding a real user-agent string helps avoid this.

This function will return the same HTMLDocument object as your IE function, eliminating the Error 91 and giving you faster, more stable performance for your 10k-page task.

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