⚡
ScheduledDataRefresh
自動化ブック内の全クエリ・ピボットテーブルを一括更新します
指定した時刻または一定の間隔で自動的にデータを更新する処理を実行するマクロです。外部データ接続のリフレッシュ・数式の再計算・特定のデータ取得処理などを定刻に自動実行できます。Application.OnTimeを活用して、業務時間中の定期的なデータ更新を自動化します。朝の業務開始前にデータを更新しておく・1時間ごとに最新データを取得するなど、リアルタイム性が必要な業務に活用できます。内部ではApplication.OnTimeで指定時刻にプロシージャを呼び出すよう予約しており、呼び出し先で外部データ接続のRefreshメソッドや再計算処理を実行します。AutoSaveBackupと同様にタイマーベースの仕組みですが、目的がバックアップではなくデータの鮮度維持である点が異なり、両者を併用すれば「最新化」と「保護」を同時に自動化できます。
📖 使い方
- VBAエディタを開く（Alt + F11）
- モジュールを挿入（挿入 > モジュール）
- 下記VBAコードをコピー&ペースト
- ブック上で実行（Alt + F8 でマクロ選択）
💡 実行例： ScheduledDataRefresh を実行すると、全データ接続とピボットテーブルが更新されます
🎯 活用シーン
- ✓株価や為替レートなどのリアルタイムデータを1時間ごとに自動更新して、最新情報を常に表示するモニタリングシートを作りたいとき
- ✓業務システムとのデータ連携で、設定した時刻に自動的に最新データをExcelに取り込む定期インポート処理を組み込みたいとき
- ✓朝の業務開始時刻に自動的にデータを更新して、担当者が出社したときには常に最新のデータが用意されているようにしたいとき
- ✓為替レートや原材料価格など外部指標を定時に取り込み、コスト試算シートを常に最新化したいとき
- ✓基幹システムのデータベースと連携し、始業前に自動で最新の在庫データを反映させたいとき
💡 実装のヒント
- ▸Application.OnTimeで設定したタイマーはExcelが開いている間のみ動作します。Excelを閉じるとタイマーがリセットされます。
- ▸定期的な更新処理で大量のデータを取得する場合、更新中にユーザーの操作がブロックされることがあります。非同期処理や更新中の表示制御を組み込むことをお勧めします。
- ▸更新処理が失敗した場合のリトライ処理やエラーログの記録を組み込むと、問題が発生したときの対処が容易になります。
- ▸外部データソースへの接続が不安定な環境では、更新失敗時のリトライ回数と待機時間をあらかじめ設計しておくと安定します。
- ▸更新処理中に別の操作をブロックしたくない場合は、DoEventsを適宜挟んで他の操作の応答性を保ってください。
💻 VBAコード
' ScheduledDataRefresh
' -----------------
' Macro Name: ScheduledDataRefresh
' Description: ブック内の全クエリ・ピボットテーブルを一括更新します
' Parameters: なし
' Returns: なし
' Usage: ScheduledDataRefresh を実行すると、全データ接続とピボットテーブルが更新されます
' -----------------
Sub ScheduledDataRefresh()
On Error GoTo ErrorHandler
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Dim conn As WorkbookConnection
Dim refreshCount As Long
Set wb = ActiveWorkbook
refreshCount = 0
' クエリ・データ接続を更新
For Each conn In wb.Connections
conn.Refresh
refreshCount = refreshCount + 1
Next conn
' 全ピボットテーブルを更新
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
refreshCount = refreshCount + 1
Next pt
Next ws
MsgBox refreshCount & " 件のデータを更新しました", vbInformation, "完了"
Exit Sub
ErrorHandler:
MsgBox "エラーが発生しました:" & vbCrLf & Err.Description, vbCritical, "エラー"
End Sub