Excel QueryTable运行渐缓直至冻结问题求助

Hey there, let's dig into why your QueryTable setup is slowing down and freezing over time. The most common culprit here is unmanaged QueryTable instances and memory leaks—even if you're clearing connections, you might be leaving behind orphaned objects that pile up over hours. Here are the key fixes to try:

1. Reuse Existing QueryTables Instead of Creating New Ones #

Every time your code runs QueryTables.Add, it creates a new QueryTable object. After 12 hours (that's 480 runs!), you've got hundreds of hidden, unused QueryTables clogging up memory. Instead, check if a QueryTable already exists and refresh it:

Dim qt As QueryTable
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("YourTargetSheet") ' Use explicit sheet reference instead of ActiveSheet

' Check for existing QueryTable
On Error Resume Next
Set qt = ws.QueryTables("DataImportQuery") ' Name your query for easy reference
On Error GoTo 0

If qt Is Nothing Then
    ' Create new QueryTable only if it doesn't exist
    Set qt = ws.QueryTables.Add( _
        Connection:="URL;http://www.address.com", _
        Destination:=ws.Range("A1") ' Your target cell/range
    )
    qt.Name = "DataImportQuery"
    ' Set your preferred QueryTable properties here (e.g., WebSelectionType, RefreshStyle)
Else
    ' Refresh the existing QueryTable
    qt.Refresh BackgroundQuery:=False ' Wait for refresh to finish before proceeding
End If

' Perform your data operations here...

' Cleanup
Set qt = Nothing
Set ws = Nothing

2. Properly Delete Old QueryTables & Connections (If Reusing Isn't an Option) #

If you must create a new QueryTable each run, delete the old one completely before adding a new one. Orphaned QueryTables are a major memory drain:

' Delete all existing QueryTables on the target sheet
For Each qt In ws.QueryTables
    qt.Delete
Next qt

' Clear orphaned connections linked to your query
For Each conn In ThisWorkbook.Connections
    If conn.Name Like "*DataImportQuery*" ' Match your query's connection name
        conn.Delete
    End If
Next conn

3. Optimize Excel Performance During Runs #

Turn off unnecessary Excel features to reduce overhead—especially important for a macro that runs every 90 seconds:

' At the start of your macro
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' ... Your QueryTable code and data operations ...

' At the end of your macro
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

4. Ensure Queries Complete Before Proceeding #

If you use BackgroundQuery:=True, overlapping runs can cause memory bloat. Force the query to finish before moving on:

qt.Refresh BackgroundQuery:=True
Do While qt.Refreshing
    DoEvents ' Let Excel handle other processes while waiting
Loop

5. Release All Object References #

VBA can hold onto object references even after you think you're done. Always set objects to Nothing to free up memory:

Set qt = Nothing
Set ws = Nothing
Set conn = Nothing

Bonus: Verify the Fix #

Check the number of QueryTables on your sheet over time using the Immediate Window (Ctrl+G in VBA Editor):

? ThisWorkbook.Worksheets("YourTargetSheet").QueryTables.Count

If the count stays at 1 (or 0 if you delete each run), you've fixed the object pile-up issue.

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