如何用VBA定时刷新Query Table？CSV导入后刷新失效求助

Hey there! I see you're having trouble getting the automatic 30-second refresh working with your QueryTable setup, even though the CSV imports correctly. Since you're comfortable with Python/MATLAB but new to VBA, let's break down the issues and fix this step by step.

Key Issues with Your Current Code #

• You're creating a new QueryTable every time you run the macro: Your code adds a fresh QueryTable each execution, which means the RefreshPeriod you set only applies to that new instance. Run the macro again, and you'll end up with duplicates—old ones might hang around without proper refresh configured.
• Missing a persistent identifier for your QueryTable: Without naming your QueryTable, it's impossible to reference it later to check if it exists, adjust settings, or refresh it directly.

Fixed Code with Reliable Automatic Refresh #

Here's a revised version of your code that solves these problems. It first checks for an existing QueryTable (by a custom name) and either refreshes it or creates a new one with the 30-second refresh interval:

Sub RefreshCSVQueryTable()
    Dim fileName As String
    Dim targetSheet As Worksheet
    Dim qt As QueryTable
    Dim qtName As String
    
    ' Set your core parameters
    fileName = "C:\Users\aofarrell\Desktop\Book1.csv"
    Set targetSheet = ThisWorkbook.Sheets("Sheet8")
    qtName = "CAPTURE_CSV" ' Name your QueryTable for easy reference later
    
    ' Check if the QueryTable already exists in the target sheet
    On Error Resume Next
    Set qt = targetSheet.QueryTables(qtName)
    On Error GoTo 0
    
    If Not qt Is Nothing Then
        ' If it exists, ensure the refresh period is set and trigger a refresh
        qt.RefreshPeriod = 0.5 ' 0.5 minutes = 30 seconds
        qt.Refresh BackgroundQuery:=True
    Else
        ' If it doesn't exist, create a new QueryTable with all your settings
        Set qt = targetSheet.QueryTables.Add( _
            Connection:="TEXT;" & fileName, _
            Destination:=targetSheet.Range("$A$1") _
        )
        
        ' Configure your QueryTable preferences
        With qt
            .Name = qtName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0.5 ' Lock in the 30-second auto-refresh
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=True
        End With
    End If
End Sub

Additional Tips for Smooth Operation #

• Run the macro once to set it up: After executing this macro one time, the QueryTable will stay in your worksheet and automatically refresh every 30 seconds. You only need to re-run it if you want to update settings like the CSV file path.
• Verify Excel's Trust Center Settings: Go to File > Options > Trust Center > Trust Center Settings > External Content and ensure "Enable automatic refresh for all data connections" is turned on—this can block auto-refresh if disabled.
• Handle file locking: If your CSV is being written to by a Python/MATLAB script, make sure the file isn't locked when Excel tries to refresh. You can add error handling to the macro to skip a refresh if the file is unavailable.
• BackgroundQuery behavior: BackgroundQuery:=True lets Excel refresh data without freezing your workbook. If you need the workbook to wait for the refresh to finish before proceeding, set this to False.

内容的提问来源于stack exchange，提问作者Aidan O'Farrell