Excelファイルを開かずに自動更新する方法【VBAマクロ2選】

揮発性関数（TODAY関数やNOW関数など）やクエリ接続を含むExcelファイルは、開かなくても定期的に自動更新できたら便利ですよね。しかし実は、閉じた状態のExcelファイルを自動更新できるのはVBAマクロだけです。クエリ接続のプロパティによる自動更新は、開いているファイルに対してしか機能せず、閉じているファイルには対応していません。

そこで本記事では、別のxlsm形式のExcelファイルからマクロを実行し、指定した閉じたExcelファイルをバックグラウンドで開いて・保存して・閉じることで、結果的に「開かずに」自動更新を実現する方法を、2つのマクロを使って詳しく解説します。

サンプルワークブックのダウンロード

自動更新の実行には、ヘルパー役となるマクロ用Excelファイルが必要です。このファイルからマクロを実行して、対象ファイルを自動更新します。

Excelファイルを開かずに自動更新する2つの方法

仕組みとしては、別のExcelファイルがマクロを実行し、指定された対象ファイルを一時的に開いて更新してから閉じるという流れになります。例として使用する「Countdown（カウントダウン）」ファイルには、ファイルを開いたタイミングで値が更新される関数が含まれています。

以下の手順に従えば、Excelファイルを実際に手動で開くことなく自動更新できるようになります。

方法1：VBAマクロで特定のExcelファイルを開かずに自動更新する

「Countdown」ファイルには、リアルタイムのカウントダウン表示のためTODAY()関数とNOW()関数が使われています。このファイルを開かずに、これらの関数の値を自動更新したいのが目的です。

手順： 別のExcelファイルでVisual Basicモジュールを挿入します（Alt+F11 → 挿入 → 標準モジュール、または開発タブ → Visual Basic → 挿入 → 標準モジュール）。挿入したモジュールに、以下のマクロを貼り付けてください。

Public Sub AutoRefreshClosedFile()
FilePath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\Countdown"
 With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With
Workbooks.Open FilePath
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
 With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
 End With
End Sub

マクロの解説

• まず、自動更新したいファイルのファイルパスを設定します。

With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With

• 上記のVBAのWithステートメントにより、警告表示や画面更新などを一時的にオフにし、ファイルの状態を変更せずに処理を行います。
• Openステートメントで指定したファイルを開きます。
• Closeステートメント（引数True付き）で、変更を保存してファイルを閉じます。
• 最後のもう一つのWithステートメントで、アプリケーションの設定をもとに戻し、自動更新後のファイルを確定させます。

• F5キーを押してマクロを実行すると、「Countdown.xlsx」ファイルがバックグラウンドで開かれます。このファイルにはTODAY()やNOW()といった揮発性関数が含まれているため、開かれた瞬間に値が再計算され、自動更新されます。あとでファイルを開き直して結果を確認してみてください。

⧭ワンポイント： すでに開いているExcelファイルを自動的に再計算・更新したい場合は、以下のマクロを使います。なお、Range（例：A1:D14）や更新間隔（例："s", 30＝30秒ごと）は、Application.OnTimeの設定で自由に変更できます。

Sub AutoCalculationRange()
Range("A1:D14").Calculate
Application.OnTime DateAdd("s", 30, Now), "AutoCalculationRange"
End Sub

関連記事： VBAを使ってExcelシートを自動更新する4つの方法

類似の読み物

• VBAなしでピボットテーブルを自動更新する3つのスマートな方法
• 【解決済み】ダブルクリックするまでExcelのセルが更新されない問題の5つの対策
• Excelでグラフを更新する2つの効果的な方法
• 元データが変更されたときにピボットテーブルを自動更新する方法

方法2：フォルダ内のすべてのExcelファイルを一括で自動更新する

方法1の応用編として、特定フォルダ内にあるすべてのExcelファイルをまとめて自動更新することもできます。このマクロを実行すれば、指定フォルダ内のファイルを順番に開いて更新し、閉じる処理を自動的に繰り返します。

手順： 先ほどのマクロを、以下のコードに置き換えてください。

Public Sub AutoRefreshFolder()
Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object
mPath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\"
Set mrf = CreateObject("Scripting.FileSystemObject")
Set mfolder = mrf.GetFolder(mPath)
 With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With
 For Each mfile In mfolder.Files
    If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xlsx" Then
            Workbooks.Open mPath & mfile.Name
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            ActiveWorkbook.Close True
    End If
 Next
 With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
 End With
End Sub

マクロの解説

• 変数をObject型として宣言します。

Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object

• mPathに対象となるフォルダのパスを設定します。
• VBAのWithステートメントで、ファイルの状態を変えずに複数の設定をまとめて実行します。
• For Each文とIf文の組み合わせにより、フォルダ内の各xlsxファイルを1つずつ判定し、該当ファイルだけをリモートで開いて・閉じます。

 For Each mfile In mfolder.Files
    If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xlsx" Then
            Workbooks.Open mPath & mfile.Name
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            ActiveWorkbook.Close True
    End If
 Next

• 最後にWithステートメントで設定を元に戻しながら、ファイルの自動更新を完了させます。

• 実行 → Sub/ユーザーフォームの実行（F5）を選ぶと、フォルダ内のファイルが一括で自動更新されます。すでに開いているファイルについては、F9キーを押せばアクティブなワークシートを即座に再計算できます。最終的な結果は方法1と同様になります。

関連記事：VBAなしでピボットテーブルを自動更新する3つのスマートな方法

まとめ

本記事では、Excelファイルを開かずに自動更新するための2種類のマクロを紹介しました。「特定の1ファイルだけを更新したい場合」は方法1を、「フォルダ内の複数ファイルをまとめて更新したい場合」は方法2を活用してください。どちらのマクロも、xlsxファイルをバックグラウンドで開いて更新・保存する仕組みなので、手作業を一切省略できます。

ぜひ、Excelに関する興味深い記事が満載のExceldemyのサイトもチェックしてみてください。

関連記事

• 【解決済み】保存するまでExcelの数式が更新されない問題の6つの対処法
• Excelでバックグラウンド更新を無効化する2つの便利な方法
• Excelでピボットテーブルを更新する4つの効果的な方法
• Excelでピボットテーブルを更新するVBA（5つの実例）
• Excelで5秒間隔の自動更新を実現する方法
• ピボットテーブルが更新されないときの5つの原因と解決策
• Excelでピボットテーブルを自動更新する2つの方法