shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

【Excel】外部参照で「値の更新」が出てファイル選択が求められた事例

Excelの外部参照は、他のExcelファイルのデータを参照するための便利な機能です。しかし、OneDrive上にあるファイルを参照する場合、予期せぬ挙動でエラーとなったので備忘録。

なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。

発生した事象

具体的には、次のような数式で外部参照(リンク)されたセルがあり、OneDriveに同期していた。リンク先の値も取得できていた。

例) ='C:\Users\〇〇\OneDrive\test\[サンプル.xlsx]日報'!$C$2

この状態で、リンクされたファイル(この事例では、サンプル.xlsx)を開いた後、当該セルについて

  • リンクの編集から「値の更新」
  • F2キーで数式の編集状態後、何も更新せずEnterで確定

など、値の更新をしようとすると、「値の更新」ダイアログが表示され、リンク先の再選択が必要になる。

通常、「値の更新」ダイアログが起動する場面は、参照先のファイル等が存在しない場合です。しかし、このケースではファイルが存在しているにも関わらず、再選択が必要となります。

しかも、ファイルを再選択しても、再度「値の更新」ダイアログが表示されるの繰り返しでキャンセルをするしかない。

「値の更新」の繰り返し

「値の更新」が表示される

そのため、値の更新をキャンセル(式の編集もしない)で、リンクされたファイルを閉じた後に、リンクの編集から「値の更新」をすると問題なく値が表示される。

 

なお、詳細の原因不明だが、OneDriveに同期されていない場所への外部参照であれば、当該事象は発生しなかった。

VBAでの補足説明

この事象は、VBAの実行時においても、当該事象が発生して原因を探る中で分かった。

なお、あくまで解決できた一例であるが、ファイルを開く際に、読み取り専用とすることでエラーは回避できた

Set リンク先ファイル = Workbooks.Open(フルパス, ReadOnly:=True)

OneDriveに同期されたファイルで、ReadOnly:=True にしていないと、VBAの実行時に「値の更新」ダイアログが表示される状態になっていた。

環境や処理の前提が違うが「読み取り専用」として良いなら、その方が良さそう。

 

以上、OneDrive上にあるファイルを参照する場合、予期せぬ挙動でエラーとなった備忘録でした。