如何用C#刷新Excel中的文本框文档而不更新表格？

Hey there! I get it—using RefreshAll() is great for bulk updates, but when you only want to refresh those linked text/document objects without touching your linked tables, it can feel like hitting a wall. Let’s break down two solid approaches to solve this:

Approach 1: Target Only Linked OLE Objects (Precise Control) #

Linked text documents (like embedded Word docs or TXT files you’ve linked to) are typically stored as OLEObjects in Excel. Instead of using RefreshAll(), we can directly iterate through these objects and refresh only the ones we care about.

Here’s a VBA example that does exactly that:

Sub RefreshLinkedTextDocsOnly()
    Dim ws As Worksheet
    Dim oleObj As OLEObject
    
    ' Loop through every worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check each OLE object on the sheet
        For Each oleObj In ws.OLEObjects
            ' Only target linked objects (not embedded ones)
            If oleObj.OLEType = xlOLELink Then
                ' Filter for document types (adjust ProgIDs to match your needs)
                Select Case oleObj.ProgID
                    Case "Word.Document.12", "Word.Document.8", "Text.Document.8"
                        oleObj.Update ' Refresh the linked document
                        Debug.Print "Refreshed: " & oleObj.Name & " (Sheet: " & ws.Name & ")"
                End Select
            End If
        Next oleObj
    Next ws
End Sub

Notes for this approach: #

• Adjust the ProgID values to match the types of documents you’re linking (e.g., use "Excel.Sheet.12" if you wanted to target linked Excel files, but we’re excluding those here).
• This method gives you full control—you won’t accidentally refresh anything else, like linked tables or charts.

Approach 2: Temporarily Disable Table Refreshes, Then Use RefreshAll() #

If you prefer using RefreshAll() but want to skip tables, you can temporarily turn off their refresh settings, run the bulk refresh, then restore the original settings.

Here’s how to do that:

Sub RefreshDocsSkipTables()
    Dim tbl As ListObject
    Dim refreshSettings As Collection
    Dim ws As Worksheet
    
    ' Store original refresh settings for linked tables
    Set refreshSettings = New Collection
    
    ' Error handling to ensure we restore settings even if something goes wrong
    On Error GoTo Cleanup
    
    ' Loop through all worksheets and their linked tables
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            If tbl.QueryTable Is Not Nothing Then
                ' Save the original "refresh on open" setting
                refreshSettings.Add Array(tbl, tbl.QueryTable.RefreshOnFileOpen)
                ' Temporarily disable auto-refresh for the table
                tbl.QueryTable.RefreshOnFileOpen = False
            End If
        Next tbl
    Next ws
    
    ' Run RefreshAll()—this will skip tables now
    ThisWorkbook.RefreshAll
    
Cleanup:
    ' Restore all table refresh settings
    Dim item As Variant
    For Each item In refreshSettings
        item(0).QueryTable.RefreshOnFileOpen = item(1)
    Next item
    
    ' Alert if an error occurred
    If Err.Number <> 0 Then
        MsgBox "Oops, something went wrong: " & Err.Description, vbExclamation
    End If
End Sub

Notes for this approach: #

• This works because RefreshAll() honors the RefreshOnFileOpen setting for linked tables. Disabling it means the tables won’t refresh when you call the method.
• It’s simpler if you have lots of different linked document types, but be aware it might refresh other linked items (like charts) if you have them.

Either of these methods should solve your problem—pick the one that fits your workflow best!

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