【Excel】Power Queryで閉じたブックを参照して更新に失敗する時の確認手順

Power Queryを使って外部のExcelブックからデータを取得する際、参照先のブックを閉じた状態で更新を実行するとエラーが発生することがあります。この問題は特に他のユーザーが作成した共有ファイルを参照する場合や、バッチ処理で自動更新を設定している場合に顕著です。本記事では、閉じたブックへの参照が原因でPower Queryの更新に失敗した時の具体的な確認手順を解説します。原因の切り分けから管理者へ依頼すべき設定変更まで、実務で役立つ情報をまとめました。

Power Queryが閉じたブックを参照する仕組み

Power Queryはデータソースに接続する際、通常はそのソースが開いているか閉じているかにかかわらずアクセスできます。ただしExcelブックを参照する場合、内部的にはExcelアプリケーションのオブジェクトモデルを使うため、ブックが閉じていると直接読み込めないケースがあります。具体的には、Power Queryは「データソースの種類」がExcelブックの場合、ファイルが開いている時はそのExcelインスタンスを経由してデータを取得しますが、ファイルが閉じている時は別のプロトコル(ACE OLEDBやOfficeドキュメントキャッシュ)を使います。この切り替えがうまくいかないと更新に失敗します。

また、Power Queryの更新はExcelのメインスレッドとは別のバックグラウンド処理で実行されるため、参照先のブックが別のユーザーによって開かれている場合も競合が発生することがあります。これらの動作を理解しておくと、エラーメッセージから原因を推測しやすくなります。

更新失敗の主な原因と切り分け手順

原因1: ファイルパスが正しく設定されていない

最も多い原因は、参照先のファイルパスが絶対パス(C:\Users\…)やネットワークドライブ(Z:\…)で指定されており、他のPCや閉じた状態でアクセスできないことです。Power Queryで閉じたブックを安定して参照するには、UNCパス(\Server\Share\File.xlsx)を使用する必要があります。

1. Power Queryエディターを開き、該当のクエリを選択します。
2. 右側の「クエリ設定」から「適用されたステップ」の「ソース」をクリックし、数式バーに表示されたファイルパスを確認します。
3. パスがローカルドライブ(C:)やマップ済みドライブ(Z:)で始まっている場合、UNCパスに変更する必要があります。
4. パスを修正するには、数式バー内のパスを直接編集するか、詳細エディターでソースステップを書き換えます。例えば Excel.Workbook(File.Contents("\\Server\Share\File.xlsx"), null, true) のように記述します。
5. 修正後、変更を保存してクエリを閉じ、その状態でExcelブックを保存します。
6. 参照先のブックを閉じた状態でPower Queryの更新を実行し、成功するか確認します。

原因2: プライバシーレベルが適切でない

Power Queryのプライバシーレベル設定が「プライベート」になっていると、他のソースとのデータ結合が制限されるため、閉じたブックからのデータ取得がブロックされることがあります。特に複数のソースをマージしているクエリで発生しやすいです。

確認手順は次の通りです。

1. Excelで「データ」タブ →「クエリと接続」をクリックし、クエリ一覧を表示します。
2. 対象のクエリを右クリックして「プロパティ」を開きます。
3. 「定義」タブの「プライバシー レベル」ドロップダウンで「なし」または「公開」に変更します。
4. 「OK」をクリックして変更を保存し、更新を再実行します。

ただし、会社のポリシーでプライバシーレベルがグループポリシーにより固定されている場合があります。その場合は管理者に相談し、必要に応じてポリシーの変更を依頼してください。

原因3: データソースの認証情報が不足している

閉じたブックがネットワーク上の共有フォルダにあり、アクセスに認証が必要な場合、Power Queryが更新時に適切な資格情報を持っていないと失敗します。特に、ファイルサーバーにWindows認証が必要なケースで問題が発生します。

この場合、「データソース設定」から資格情報を設定します。Excelの「データ」タブ →「クエリと接続」→「データソースの設定」を開き、該当するソースを選択して「編集」→「資格情報の変更」でWindows認証または基本認証を入力します。ただし、会社PCではドメインアカウントが自動的に渡されることが多いため、ここで問題になることは比較的少ないですが、もしもエラーが出る場合は確認してください。

具体的な更新失敗パターンと対処法

状況	成功条件	失敗パターン
参照先ブックが開いている	ほとんどの場合成功	別ユーザーが排他ロックしていると失敗
参照先ブックが閉じている(ローカルパス)	同PC上のローカルファイルなら成功	ネットワークドライブでは失敗しやすい
参照先ブックが閉じている(UNCパス)	アクセス権限があれば成功	認証やファイアウォールで失敗
Power BIサービス経由(ゲートウェイ使用)	オンプレミスゲートウェイが適切に構成されていれば成功	ゲートウェイが落ちていると失敗

上表の通り、閉じたブックを安定して参照するための最も重要なポイントはUNCパスの使用です。さらに、プライバシーレベルの調整や認証情報の設定も合わせて行うと良いでしょう。

失敗パターン: 「データソースの資格情報が無効です」エラー

このエラーは、参照先のブックにアクセスするための資格情報が正しく保存されていないか、期限切れになっている場合に発生します。例えば、パスワード変更後や、異なるPCから開いた場合によく起こります。対処法としては、前述のデータソース設定で資格情報を再入力するか、「現在のWindows認証を使用する」に変更すると解決することがあります。

失敗パターン: 「ファイルが開かれているため読み取れません」エラー

このエラーは、参照先のブックが他のユーザーによって排他モードで開かれていると発生します。閉じたブックであれば本来発生しませんが、Power Queryの更新タイミングと重なった場合に起こることがあります。解決策としては、参照先のブックを閉じてから更新を再試行するか、Excelの共有オプションで「読み取り専用で開くことを許可する」設定に変更してもらうと回避できます。

管理者に依頼すべき設定変更

個人の権限では解決できない問題もあります。以下のような場合はIT管理者に連絡して設定変更を依頼してください。

• プライバシーレベルがグループポリシーで固定されている場合: 管理者がActive Directoryのグループポリシーで「データ接続のプライバシーレベルを無視する」を有効にすることで、ユーザー側の設定が可能になります。
• データゲートウェイが必要な場合: Power BI ServiceやExcel Onlineで更新をスケジュールするには、オンプレミスデータゲートウェイのインストールと構成が必須です。管理者にゲートウェイの追加を依頼してください。
• ネットワーク共有のアクセス権限が不足している場合: 参照先ブックの保存先フォルダに対する読み取り権限が付与されているか確認し、不足していれば管理者に権限追加を依頼します。
• ファイアウォールによるブロック: ファイルサーバーとクライアント間でSMBプロトコルがブロックされている可能性があります。管理者にネットワーク設定の確認を依頼してください。

よくある質問(FAQ)

Q1: 参照先のブックを開いた状態で更新すると成功するのに、閉じると失敗するのはなぜですか?

開いているブックには直接Excelインスタンスからアクセスできるため、Power Queryは非常に高速にデータを取得できます。閉じている場合はACE OLEDBエンジンが使われるため、ドライバのバージョンやファイル形式によっては失敗することがあります。まずはUNCパスに変更し、それでもダメならプライバシーレベルを調整してください。

Q2: OneDriveやSharePoint上のファイルを参照している場合はどうすればいいですか?

OneDriveやSharePoint上のファイルはWeb URL(https)で参照する必要があります。Power Queryの「Web」コネクタを使用して直接URLを指定するか、SharePointフォルダーコネクタを使うと閉じた状態でも安定して更新できます。また、SharePointリストとしてデータを準備するとさらに信頼性が向上します。

Q3: 複数のクエリで同じブックを参照している場合、すべてを修正する必要がありますか?

各クエリのソースパスを個別に修正する必要がありますが、「データソース設定」で一括管理できるものではありません。ただし、Power Queryエディタの「パラメータ」機能を使ってファイルパスを変数化すると、1か所の変更で全クエリに反映できるため便利です。

Q4: エラーメッセージに「Expression.Error: The key didn’t match any rows in the table」と出ました。

このエラーは閉じたブックの参照とは直接関係なく、結合キーが一致しないことが原因です。ただし、閉じたブックのデータが不完全な状態で読み込まれた場合にも発生し得ます。ソースのデータを確認してください。

Q5: 自動更新を仕掛けているバッチ処理でだけ失敗します。手動更新では成功します。

バッチ処理は多くの場合、別のユーザーアカウントやサービスアカウントで実行されます。そのアカウントに参照先ブックへのアクセス権限がない可能性があります。バッチ実行アカウントに権限を付与するか、バッチ内で資格情報を明示的に渡す設定に変更してください。

まとめ

閉じたブックをPower Queryで参照する際の更新失敗は、パスの形式、プライバシーレベル、認証情報の3つを確認することで大半が解決します。特にUNCパスの使用は基本中の基本であり、まずこれを徹底してください。それでも解決しない場合は管理者に相談し、グループポリシーやネットワーク設定を見直す必要があります。また、OneDriveやSharePointのURLを使う方法も検討すると、より安定した自動更新が実現できます。本記事の手順を一つずつ試して、根本原因を特定してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】