Odświeżanie danych przed zapisaniem pliku w Excelu

Pytanie

odświeżanie danych przed zapisaniem pliku

Odpowiedź Sztucznej Inteligencji

Bezpośrednia odpowiedź na pytanie

• Najpewniejszy sposób: przed zapisem wyłącz odświeżanie w tle dla wszystkich połączeń, odśwież je synchronicznie, odśwież tabele przestawne, przelicz formuły, a dopiero potem zapisz. Zrób to w zdarzeniu Workbook_BeforeSave i anuluj domyślne zapisywanie, aby uniknąć podwójnego „Zapisz?”.
• Kluczowe punkty:
  • Ustaw BackgroundQuery = False dla OLEDB/ODBC/QueryTable.
  • Odśwież per-połączenie (conn.Refresh), a nie samym RefreshAll.
  • Po odświeżeniu: Refresh PivotCache/RefreshTable i Application.Calculate(Full/FullRebuild).
  • W zdarzeniu BeforeSave ustaw Cancel = True, wykonaj odświeżenie i zapisz programowo (Me.Save).

Szczegółowa analiza problemu

• Główne źródło problemów to asynchroniczność RefreshAll: Excel startuje odświeżanie w tle i przechodzi do Save, przez co zapis bywa wykonany na starych danych. Wymuszenie trybu synchronicznego per-połączenie rozwiązuje to deterministycznie.
• Kolejność aktualizacji:
  1. Połączenia zewnętrzne (OLEDB/ODBC/Power Query/OLAP),
  2. Pamięci podręczne tabel przestawnych (PivotCache) lub same tabele (RefreshTable),
  3. Przeliczenie formuł (Calculate/CalculateFull/CalculateFullRebuild).
• Minimalizacja ryzyka: wyłącz zdarzenia i odświeżanie ekranu na czas operacji, pilnuj obsługi błędów i przywrócenia ustawień.

Przykładowa, odporna procedura (w pełni automatyczna):

1. W module ThisWorkbook:

   Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error GoTo SafeExit
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    ' Anuluj standardowy zapis i przejmij kontrolę
    Cancel = True
   
    ' 1) Odśwież dane synchronicznie
    Call RefreshAllSync
   
    ' 2) Przelicz formuły (dla złożonych modeli użyj CalculateFullRebuild)
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull
   
    ' 3) Zapisz (Save lub SaveAs, jeśli użytkownik wybrał "Zapisz jako")
    If SaveAsUI Then
        Application.Dialogs(xlDialogSaveAs).Show
    Else
        Me.Save
    End If
   SafeExit:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
   End Sub

2. W standardowym module (Module1): wymuszenie synchronicznego odświeżania wszystkich typów źródeł:

   Public Sub RefreshAllSync()
    Dim conn As WorkbookConnection
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim hadBG As Boolean
   
    ' Opcjonalnie: jeśli korzystasz z Modelu danych (Power Pivot)
    On Error Resume Next
    ThisWorkbook.Model.Refresh    ' w starszych wersjach pominie
    On Error GoTo 0
    ' a) Połączenia OLEDB/ODBC – odśwież synchronicznie
    For Each conn In ThisWorkbook.Connections
        On Error Resume Next
        Select Case conn.Type
            Case xlConnectionTypeOLEDB
                hadBG = conn.OLEDBConnection.BackgroundQuery
                conn.OLEDBConnection.BackgroundQuery = False
                On Error GoTo RefreshErr
                conn.Refresh
                On Error GoTo 0
                conn.OLEDBConnection.BackgroundQuery = hadBG
   
            Case xlConnectionTypeODBC
                hadBG = conn.ODBCConnection.BackgroundQuery
                conn.ODBCConnection.BackgroundQuery = False
                On Error GoTo RefreshErr
                conn.Refresh
                On Error GoTo 0
                conn.ODBCConnection.BackgroundQuery = hadBG
   
            Case Else
                ' Niektóre typy (np. MODEL, WORKSHEET) nie wspierają BackgroundQuery – spróbuj zwykłego Refresh
                On Error GoTo RefreshErr
                conn.Refresh
                On Error GoTo 0
        End Select
    Next conn
   
    ' b) Tabele wyjściowe Power Query (ListObjects) – wymuś brak tła na QueryTable
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            If Not lo.QueryTable Is Nothing Then
                On Error Resume Next
                lo.QueryTable.BackgroundQuery = False
                On Error GoTo 0
                On Error GoTo RefreshErr
                lo.QueryTable.Refresh BackgroundQuery:=False
                On Error GoTo 0
            End If
        Next lo
    Next ws
   
    ' c) Tabele przestawne – odśwież cache lub same tabele
    Dim pc As PivotCache
    On Error Resume Next
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc
    On Error GoTo 0
   
    Exit Sub
   RefreshErr:
    ' Przerwij czyszczenie błędu – rzuć czytelny komunikat i wróć
    Err.Raise Err.Number, "RefreshAllSync", "Błąd odświeżania: " & Err.Description
   End Sub

• Dlaczego tak: odświeżanie przez Connections i QueryTables z BackgroundQuery=False zapewnia blokujące, przewidywalne zakończenie; PivotCache.Refresh aktualizuje źródła tabel przestawnych; pełne przeliczenie spina całość.
• Unikasz podwójnego monitu „Zapisz?”: ponieważ w BeforeSave ustawiasz Cancel=True i sam wywołujesz Save/SaveAs po zakończeniu odświeżeń.

Aktualne informacje i trendy

• W nowszych wydaniach 365/2021 Power Query częściej zwraca wynik jako ListObject z QueryTable; wymuszenie parametru Refresh BackgroundQuery:=False na ListObject.QueryTable daje lepszą kontrolę niż samo RefreshAll.
• W skoroszytach z Modelem danych (Power Pivot) przydaje się ThisWorkbook.Model.Refresh przed odświeżaniem Pivotów.
• W praktyce produkcyjnej unika się „ślepych” Application.Wait – lepiej wymusić tryb synchroniczny per-połączenie niż zgadywać czasy.

Wspierające wyjaśnienia i detale

• BackgroundQuery: gdy True – Refresh działa asynchronicznie; gdy False – Refresh blokuje wątek VBA do zakończenia transferu.
• Pivoty: samo RefreshAll nie gwarantuje odświeżenia PivotCache w odpowiednim momencie; jawny pc.Refresh lub pt.RefreshTable po danych źródłowych eliminuje wyścigi.
• Calculate vs CalculateFull/FullRebuild:
  • Calculate – szybkie przeliczenie przy włączonym łańcuchu zależności,
  • CalculateFull – pełne przeliczenie,
  • CalculateFullRebuild – dodatkowo odbudowa zależności (najcięższe, gdy formuły/dane zmieniają strukturę).

Aspekty etyczne i prawne

• Odświeżanie może wykonywać zapytania do systemów produkcyjnych; respektuj limity zapytań, okna serwisowe i polityki dostępu.
• Nie przechowuj haseł w jawnej treści ConnectionString – używaj uwierzytelniania zintegrowanego lub menedżerów poświadczeń.
• Zgodność z RODO/polityką firmy: dane osobowe w raportach odświeżanych automatycznie muszą mieć podstawę prawną i kontrolę dostępu.

Praktyczne wskazówki

• Przed długą operacją: Application.ScreenUpdating=False, Application.EnableEvents=False; po wszystkim koniecznie przywróć.
• Dodaj logowanie czasu (Timer) i prosty pasek stanu: Application.StatusBar = "Odświeżanie…"
• Wprowadź timeout awaryjny i komunikat dla użytkownika, jeśli jakieś źródło nie odpowie.
• Jeśli użytkownik czasem nie chce czekać: dodaj prompt (MsgBox Yes/No) i ścieżkę „zapisz bez odświeżania”.

Ewentualne zastrzeżenia lub uwagi dodatkowe

• Niektóre konektory firm trzecich mogą ignorować BackgroundQuery – wtedy rozważ zdarzenia AfterRefresh lub dedykowane API dodatku.
• W niektórych scenariuszach wywołanie Refresh w BeforeSave zmienia skoroszyt tuż przed zapisem – bez Cancel=True Excel może po zapisie poprosić drugi raz o „Zapisz?”. Zaproponowane podejście eliminuje ten efekt.
• Bardzo duże modele: CalculateFullRebuild może być zbyt kosztowny – stosuj warunkowo.

Sugestie dalszych badań

• Zdarzenia QueryTable_AfterRefresh oraz Workbook_SheetChange do sygnalizacji postępu i detekcji niepowodzeń.
• Wersjonowanie raportów: automatyczny SaveCopyAs po odświeżeniu na ścieżkę archiwalną.
• Przeniesienie ciężkich odświeżeń do Power BI/serwera ETL i w Excelu tylko konsumpcja gotowych modeli.

Krótkie podsumowanie

• Problem wynika z asynchronicznego RefreshAll. Rozwiązaniem jest wymuszenie odświeżania synchronicznego (BackgroundQuery=False) dla wszystkich połączeń/QueryTables, następnie odświeżenie pivotów i pełne przeliczenie formuł, a zapis wykonany programowo w obsłudze BeforeSave z Cancel=True. Zaproponowany szablon kodu jest deterministyczny, odporny na typowe wyścigi i eliminuje podwójny monit o zapis. Jeśli chcesz, przygotuję wersję dostosowaną do Twoich konkretnych typów źródeł (SQL/OLAP/plikowe/Power Query).