检测到Error 500时刷新IE：Excel VBA数据同步故障求助

Hey Rick, sorry to hear that 500 errors are derailing your VBA data update workflow. I’ve run into exactly this kind of issue when scraping auto-refreshing pages with IE, so let’s walk through how to fix it step by step.

When the server throws a 500 Internal Server Error, the IE page swaps out your target data DOM for an error page. Your existing code tries to copy from elements that no longer exist, and IE’s internal state can get stuck in a broken state—killing the clipboard copy operation entirely. If left unhandled, your timer will keep firing failed attempts, making the problem persist until you manually intervene.

1. Add Error Detection & Recovery Logic #

First, we need to check if the page loaded successfully before trying to copy data. We can scan for the 500 error message in the page content or IE’s status text, then trigger a refresh or skip the update.

Here’s how to modify your core update routine:

Sub UpdateDataFromIE()
    Dim ie As InternetExplorer
    Dim doc As HTMLDocument
    Dim errorLogSheet As Worksheet
    
    Set errorLogSheet = ThisWorkbook.Sheets("ErrorLog") ' Create this sheet first!
    Set ie = GetObject(, "InternetExplorer.Application")
    Set doc = ie.Document

    ' Wait for IE to finish loading (skip if still busy)
    If ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE Then
        LogError errorLogSheet, "Page still loading—skipping update"
        Exit Sub
    End If

    ' Detect 500 error
    If InStr(doc.documentElement.innerHTML, "500 Internal Server Error") > 0 Or _
       InStr(ie.StatusText, "500") > 0 Then
        LogError errorLogSheet, "500 Error detected—refreshing IE"
        ie.Refresh
        Exit Sub
    End If

    ' Try to copy data with error handling
    On Error Resume Next
    doc.getElementById("your-target-element-id").execCommand "Copy"
    If Err.Number <> 0 Then
        LogError errorLogSheet, "Copy failed: Error code " & Err.Number
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0

    ' Paste to your data sheet
    ThisWorkbook.Sheets("Data").Range("A1").PasteSpecial
    LogError errorLogSheet, "Data updated successfully" ' Optional success log
End Sub

' Helper to log errors/success to a sheet
Private Sub LogError(sheet As Worksheet, message As String)
    Dim nextRow As Long
    nextRow = sheet.Cells(sheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    sheet.Cells(nextRow, 1).Value = Now
    sheet.Cells(nextRow, 2).Value = message
End Sub

2. Stabilize Your Timer #

If you hit consecutive 500 errors, don’t keep hammering the server every 90 seconds. Add a cooldown period to avoid making the problem worse:

Sub TimerTrigger()
    Static consecutiveErrors As Integer
    Dim nextRunTime As Date

    UpdateDataFromIE

    ' Adjust timer based on error frequency
    If consecutiveErrors >= 3 Then
        nextRunTime = Now + TimeValue("00:02:00") ' Wait 2 minutes instead of 90s
        consecutiveErrors = 0
    Else
        nextRunTime = Now + TimeValue("00:01:30") ' Normal 90s interval
    End If

    ' Reschedule the timer
    Application.OnTime nextRunTime, "TimerTrigger"
End Sub

Don’t forget to increment consecutiveErrors in your 500 error detection block, and reset it to 0 when a successful update happens.

3. Fallback to a Fresh IE Instance #

Sometimes 500 errors can corrupt the IE instance itself. Add a safety check to recreate IE if it becomes unresponsive:

Function GetWorkingIE() As InternetExplorer
    On Error Resume Next
    Set GetWorkingIE = GetObject(, "InternetExplorer.Application")
    
    ' If IE isn't running or is broken, launch a new instance
    If Err.Number <> 0 Or GetWorkingIE Is Nothing Then
        Set GetWorkingIE = New InternetExplorer
        GetWorkingIE.Visible = True ' Set to False if you want it hidden
        GetWorkingIE.Navigate "your-target-page-url"
        
        ' Wait for the new instance to load
        Do While GetWorkingIE.Busy Or GetWorkingIE.ReadyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End If
    On Error GoTo 0
End Function

Replace your Set ie = GetObject(...) line with Set ie = GetWorkingIE() to use this fallback.

Instead of relying on the clipboard (which is fragile), extract data directly from the DOM and write it to your sheet. For example:

' Replace clipboard copy/paste with direct value assignment
ThisWorkbook.Sheets("Data").Range("A1").Value = doc.getElementById("your-target-element-id").innerText

This avoids clipboard permissions issues entirely and works even if the page’s copy context is broken.

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