Excelでバックグラウンド更新を無効にする2つの便利な方法

Excelでは、クエリの処理を確実に完了させるために、バックグラウンド更新を無効にする必要がある場面がよくあります。バックグラウンド更新を無効にすると、ピボットテーブルよりも先にクエリが更新されるようになります。また、Webページなどの外部データにリンクしたデータを扱う際に発生するエラーを防ぐためにも、バックグラウンド更新を無効にしておくと安心です。

練習用ワークブックはこちらからダウンロードできます：

Excelでバックグラウンド更新を無効にする2つの効果的な方法

外部のWebページにリンクされたデータファイルがあるとします。データを編集しても、しばらくすると自動的に更新されて元のデータに戻ってしまうことがあります。このような場合には、そのExcelファイルのバックグラウンド更新を無効にする必要があります。この記事では、バックグラウンド更新を無効にする2つの簡単な方法を紹介します。ここでは、他通貨からユーロへの換算レートが記録されたデータファイルがあり、そのデータがリアルタイムの為替レートを表示するWebページにリンクされている状況を想定します。

1. クエリプロパティからバックグラウンド更新を無効にする

バックグラウンド更新を無効にするには、クエリプロパティを利用できます。以下の手順に従って操作してください。

手順：

• まず、リンクされたデータを含むセルをどれか1つクリックします。
• 次に、上部リボンの「データ」タブに移動します。
• 続いて、「すべて更新」のドロップダウンメニューをクリックし、「接続プロパティ」を選択します。

• すると、「クエリ プロパティ」という新しいウィンドウが表示されます。
• ここで、「バックグラウンド更新を有効にする」オプションのチェックを解除します。

• これでバックグラウンド更新が無効になりました。データは自動的に更新されなくなります。再度更新したい場合は、「すべて更新」をクリックしてください。

関連記事：ピボットテーブルが更新されないときの原因と対処法（5つの問題と解決策）

あわせて読みたい

• VBAですべてのピボットテーブルを更新する方法（4つの方法）
• Excelでピボットテーブルを自動更新する方法（2つの方法）
• Excelですべてのピボットテーブルを更新する方法（3つの方法）

2. VBAコードを使ってバックグラウンド更新を無効にする

VBAコードを使ってもバックグラウンド更新を無効にできます。ここでは、コピー＆ペーストですぐに実行できるコードと手順を紹介します。以下の手順に従ってください。

手順：

• まず、上部リボンの「開発」タブに移動します。
• 次に、「Visual Basic」を選択します。

• Visual Basicのウィンドウが表示されます。
• 続いて、「挿入 ＞ 標準モジュール」を選択して、新しいモジュールを開きます。

• 新しいモジュールを開いたら、以下のコードをウィンドウに貼り付けてください。

Sub Disable_Background_Refresh()
Dim dbr As Long
    With ActiveWorkbook
        For dbr = 1 To .Connections.Count
          If .Connections(dbr).Type = xlConnectionTypeOLEDB Then
            .Connections(dbr).OLEDBConnection.BackgroundQuery = False
          End If
        Next dbr
    End With
End Sub

• 続いて、「実行」をクリックします。
• 「Disable_Background_Refresh」マクロを選択して実行します。

• これで、バックグラウンド更新が無効になったことが確認できます。
• また、このマクロコードを使えば、バックグラウンド更新を無効にする専用ボタンを作成することも可能です。
• その場合は、「挿入」タブに移動し、「ボタン」オプションを選択します。

• 次に、ワークシート上でボタンを配置したい場所をドラッグして選択します。
• 領域を選択すると、「マクロの登録」というウィンドウが表示されます。
• そこからマクロを選択し、「OK」をクリックします。

• ボタンが作成されるので、名前を「バックグラウンド更新を無効にする」などに変更しておきましょう。
• これで、ボタンを押すたびにバックグラウンド更新オプションのチェックが外れるようになります。

VBAコードの解説：

Sub Disable_Background_Refresh()

• マクロ名を「Disable_Background_Refresh」に設定しています。

Dim dbr As Long

• 大きな範囲の数値を格納できる変数を宣言しています。

With ActiveWorkbook
For dbr = 1 To .Connections.Count
If .Connections(dbr).Type = xlConnectionTypeOLEDB Then
.Connections(dbr).OLEDBConnection.BackgroundQuery = False

この部分のコードは、ブック内のすべての接続を順番に確認していきます。

• Trueに設定すればバックグラウンド更新が有効になり、Falseに設定すれば無効になります。

関連記事：VBAでピボットテーブルを更新する方法（5つの実例）

注意点

• バックグラウンド更新を無効にすると、データは自動的に更新されなくなります。
• データを更新したい場合は、「すべて更新」オプションをクリックする必要があります。
• マクロ実行用のボタンを作成しておくと、同じ作業を繰り返し行うときにとても便利です。

まとめ

この記事では、Excelでバックグラウンド更新を無効にする2つの簡単な方法を紹介しました。練習用ワークブックをダウンロードして、実際に手順を試してみてください。この記事が皆さんのお役に立てれば幸いです。当サイトExceldemyでは、Excelに関するさまざまなコンテンツを公開しています。ご質問やご提案などがあれば、ぜひ下のコメント欄でお聞かせください。

関連記事

• Excelシートを自動的に更新する方法（3つの適切な方法）
• 【解決済み】保存するまでExcelの数式が更新されない（6つの解決策）
• VBAなしでExcelのピボットテーブルを自動更新する方法（3つのスマートな方法）
• 【修正済み！】ダブルクリックしないとExcelのセルが更新されない（5つの解決策）
• Excelでグラフを更新する方法（2つの効果的な方法）
• 元データが変更されたときにピボットテーブルを自動更新する方法