使用VBA访问SSRS网站报错错误91，请求代码修正指导

Hey there! Let's work through that Runtime Error 91 you're hitting—this is super common with web automation, and the root issue is almost always timing (your code is trying to interact with elements before they're fully loaded) plus a couple of small code missteps. Let's break this down and fix it.

What's Causing the Error? #

Your current code has two main problems:

1. A redundant IE.navigate call (the second empty one) that disrupts page loading
2. A hardcoded 4-second wait that's unreliable—SSRS pages often take longer to render dynamic elements, so your code tries to grab elements that don't exist yet

Optimized Code with Reliable Waiting #

Here's a revised version of your code that fixes these issues, with comments explaining each change:

Sub AgingReport()
    Dim IE As New SHDocVw.InternetExplorerMedium
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim ReportDate As MSHTML.IHTMLElement
    Dim ViewReportBtn As MSHTML.IHTMLElement
    Dim waitTimeout As Date
    
    ' 1. Navigate to the SSRS page (use FULL URL with http/https!)
    IE.navigate "https://www.example.com" ' Critical: Add http/https here
    IE.Visible = True
    
    ' 2. Wait for the browser to finish loading the base page
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy
        DoEvents ' Keeps Excel responsive while waiting
    Loop
    
    Set HTMLDoc = IE.document
    
    ' 3. Wait specifically for the date field to load (up to 10 seconds)
    waitTimeout = Now + TimeValue("00:00:10")
    Do
        DoEvents
        On Error Resume Next ' Ignore "not found" errors temporarily
        Set ReportDate = HTMLDoc.getElementById("ReportViewerControl_ctl04_ctl09_txtValue")
        On Error GoTo 0 ' Reset error handling
    Loop Until Not ReportDate Is Nothing Or Now > waitTimeout
    
    ' Check if we found the date field before timing out
    If ReportDate Is Nothing Then
        MsgBox "Date field not found! Page may have loaded too slowly, or the element ID changed."
        IE.Quit
        Set IE = Nothing
        Exit Sub
    End If
    
    ' 4. Set the report date
    ReportDate.Value = "06/28/2019"
    
    ' 5. Wait for the "View Report" button to load
    waitTimeout = Now + TimeValue("00:00:10")
    Do
        DoEvents
        On Error Resume Next
        Set ViewReportBtn = HTMLDoc.getElementById("ReportViewerControl_ctl04_ctl00")
        On Error GoTo 0
    Loop Until Not ViewReportBtn Is Nothing Or Now > waitTimeout
    
    If ViewReportBtn Is Nothing Then
        MsgBox "View Report button not found!"
        IE.Quit
        Set IE = Nothing
        Exit Sub
    End If
    
    ' 6. Click the button to run the report
    ViewReportBtn.Click
    
    ' Optional: Wait for the report to finish rendering
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy
        DoEvents
    Loop
    
    ' Cleanup (uncomment if you want to close IE automatically)
    ' IE.Quit
    ' Set IE = Nothing
End Sub

Key Improvements Explained #

• Removed redundant navigate call: The second empty IE.navigate was causing unnecessary page reloads or navigation failures.
• Reliable page waiting: Instead of a fixed 4-second wait, we loop until the browser is no longer busy and the page is fully loaded.
• Element-specific timeout: We wait up to 10 seconds for each target element to exist, which handles cases where SSRS renders elements dynamically after the base page loads.
• Error checking: Added messages to help you debug if elements can't be found (common if SSRS updates control IDs after report changes).

Quick Checks Before Running #

1. Make sure your VBA project has references to:
   • Microsoft Internet Controls (for InternetExplorerMedium)
   • Microsoft HTML Object Library (for HTML elements)
     You can enable these via Tools > References in the VBA editor.
2. Verify the element IDs match your current SSRS page—sometimes parameter changes can alter these IDs.
3. If your SSRS site requires login, you'll need to add code to fill in username/password fields and click the login button first.

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