Excelでブックを統合したあと、「名前の管理」や「編集リンク」には何も出てこないのに、シートを開くたびに 「…file.xlsm が見つかりません」 と怒られる――。本記事では、このような「外部リンクがどこにも見つからないのに RefreshAll でエラーが出続ける」状態を、VBAを使って 確実に“犯人オブジェクト”を特定し、修正するところまで を丁寧に解説します。
Excelで「外部リンクが見つからないのに …file.xlsm が見つかりません」と出るとき
よくあるケースは次のような流れです。
- ブックAとブックBがあり、ブックAの全シートをブックBに統合した。
- [数式 > 名前の管理] で外部参照の定義名を修正・削除した。
- [データ > クエリと接続 > 編集リンク]にも何も出てこない。
- ところが、シートを開くたびに実行している
ActiveWorkbook.RefreshAllにより、
「◯◯◯…file.xlsm が見つかりません。移動・名前変更・削除されませんでしたか?」
というメッセージがシートごとに毎回表示されてしまう。 - セルの検索(Ctrl+F)でも、古いファイル名はヒットしない。
この場合、外部リンクの実体は「セル以外のどこか」に潜んでいます。しかもそれが RefreshAll の対象になっているため、該当オブジェクトを見つけて修正しない限り、エラーは消えてくれません。
なぜ「編集リンク」「名前の管理」に出ない外部参照が残るのか
Excel の外部リンクは、セルの数式以外にも様々な場所に埋め込まれます。しかもそれらの多くは、通常の UI からは一覧できません。
| 格納場所 | UIで見えるか | RefreshAllの対象か | コメント |
|---|---|---|---|
| 通常のセル数式 | 検索(Ctrl+F)、編集リンクで見つかる | ○ | もっとも分かりやすい外部参照 |
| 定義名(名前の管理) | 基本は見えるが、非表示名は見落としやすい | ○ | 古いブックへの参照が残りがち |
| ピボットキャッシュ | UIからは原則見えない | ○ | 外部ブックをデータソースにしていた場合の要注意ポイント |
| グラフの系列式 | グラフを選択して式を見れば分かるが、一覧しづらい | ○ | =SERIES([旧ファイル名.xlsm]シート!範囲,...) の形で残る |
| 接続(WorkbookConnection) | [データ > クエリと接続]で一部見える | ○ | ODBC/OLEDB接続や古いQueryTableが原因になることも多い |
| QueryTable / ListObjectの裏側 | 表としては見えるが、接続文字列までは見えにくい | ○ | 昔の「外部データの取り込み」の名残が潜んでいる場合がある |
| リンク画像・図形・ハイパーリンク | パッと見では分からない | △(ものによる) | 画像のリンク元パスやハイパーリンク先に旧ファイル名が残る |
今回のように RefreshAll 実行時だけメッセージが出る 場合、特に疑うべきなのは次の3つです。
- ピボットキャッシュ(PivotCache)
- グラフ系列(Chart.Series.Formula)
- 接続/QueryTable/ListObject(テーブル)のクエリ
これらは UI から一括で検索できないため、本記事では VBA を使って総ざらいする手順 を紹介します。
よくある原因候補のイメージを押さえておく
ピボットキャッシュの参照先
ピボットテーブルは「どの範囲をデータ元にしているか」を PivotCache という内部オブジェクトに持っています。この参照先が
'[旧ブック名.xlsm]シート名'!$A$1:$D$1000
といった形で外部ブックを指していると、RefreshAll 時にそのブックを探しに行きます。問題は、この情報が [名前の管理]や[編集リンク]では見えない 場合があることです。
グラフの系列式(SERIES)
グラフのデータ範囲は、内部的には =SERIES(系列名, X軸範囲, Y軸範囲, 順序) という数式で表されます。ブック統合前に、
- グラフの元データが旧ブックAの範囲だった
という状況で統合を行うと、統合後も系列式が
=SERIES(…, '[旧ファイル名.xlsm]シート名'!$A$2:$A$100, …)
のまま残ってしまいがちです。これも RefreshAll のタイミングで参照され、外部リンクエラーの原因になります。
接続/QueryTable/テーブルのクエリ
昔のバージョンから使っているファイルほど、
- 「外部データの取り込み」で作った QueryTable
- テーブル化(ListObject)された範囲の背後にある クエリ
- ODBC/OLEDB接続の 接続文字列
などが生き残っていることがあります。ブック統合やコピー&貼り付けの過程で、「もう使っていないのに接続だけ残る」という状態になると、RefreshAll のたびに旧ブックや旧パスを探しに行きます。
定義名・入力規則・条件付き書式・図形など
インパクトはやや小さいものの、次のような場所にも外部参照が潜みます。
- 非表示の定義名(名前の管理では見えないもの)
- 入力規則(リスト参照先が別ブックの範囲)
- 条件付き書式(数式内に外部参照)
- リンク画像(別ブックのグラフや範囲を貼り付け)
- リンク付きの図形(図形の「リンク先」やハイパーリンク)
これらもまとめて洗い出せるよう、後述の 総当たりスキャン用マクロ を用意します。
VBAで「見えない外部リンク」を一気に洗い出す
準備:マクロを実行できる状態にする
- 対象のブック(エラーが出ているファイル)を開く。
- Alt + F11 で VBA エディター を開く。
- [挿入 > 標準モジュール] を選択し、Module1 などのモジュールを追加。
- 以降に出てくるマクロコードを、このモジュールに貼り付ける。
- 実行したいマクロ内にカーソルを置き、F5 キーで実行。
- 結果は イミディエイトウィンドウ(Ctrl + G) に表示されます。
注意ポイント: イミディエイトウィンドウに For Each pc In wb.PivotCaches のような ループ1行だけ を書いても実行できません。必ず Sub ~ End Sub で囲まれたマクロとして実行してください。
手順1:ピボットキャッシュとグラフ系列を一覧表示する
まずは RefreshAll と相性の悪い ピボットキャッシュ と グラフ系列 をチェックします。
ピボットキャッシュの参照先/接続を列挙するマクロ
Sub ListPivotCacheSources()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim pc As PivotCache
On Error Resume Next
For Each pc In wb.PivotCaches
Debug.Print "PivotCache #" & pc.Index & " | SourceData: " & pc.SourceData
Debug.Print "PivotCache #" & pc.Index & " | Connection: " & pc.Connection
Next pc
On Error GoTo 0
End Sub
実行後、イミディエイトウィンドウに次のような行が並びます。
PivotCache #1 | SourceData: '[旧ファイル名.xlsm]Sheet1'!$A$1:$D$1000
PivotCache #1 | Connection:
PivotCache #2 | SourceData: Sheet2!$A$1:$F$500
PivotCache #2 | Connection: ODBC;DSN=...;DBQ=...
ここでチェックしたいのは、
- SourceData に
[旧ファイル名.xlsm]や.xlsmが含まれていないか - Connection の文字列に旧ブック名や古いパスが含まれていないか
です。該当する PivotCache があれば、そのキャッシュを使っているピボットテーブルのデータソースを修正する必要があります(修正方法は後述)。
すべてのグラフ(埋め込み+グラフシート)の系列式を列挙するマクロ
Sub ListAllChartSeriesFormulas()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim ws As Worksheet, co As ChartObject
Dim ch As Chart, s As Series
On Error Resume Next
' 各ワークシート上の埋め込みグラフ
For Each ws In wb.Worksheets
For Each co In ws.ChartObjects
For Each s In co.Chart.SeriesCollection
Debug.Print "Sheet: " & ws.Name & " | Chart: " & co.Name & _
" | Series: " & s.Name & " | Formula: " & s.Formula
Next s
Next co
Next ws
' グラフシート
For Each ch In wb.Charts
For Each s In ch.SeriesCollection
Debug.Print "ChartSheet: " & ch.Name & " | Series: " & s.Name & _
" | Formula: " & s.Formula
Next s
Next ch
On Error GoTo 0
End Sub
出力例:
Sheet: 売上推移 | Chart: Chart 1 | Series: 売上 | Formula: =SERIES("売上",
'[旧ファイル名.xlsm]売上'!$A$2:$A$13,'[旧ファイル名.xlsm]売上'!$B$2:$B$13,1)
Sheet: 売上推移 | Chart: Chart 2 | Series: 粗利 | Formula: =SERIES(...)
イミディエイトウィンドウで Ctrl + F を押し、
[旧ファイル名.xlsm].xlsmや.xlsxといった拡張子
を検索してみてください。該当する系列が見つかれば、そのグラフがエラーの発生源です。
手順2:接続・QueryTable・テーブルの裏側を調べる
次に、ブックレベルの接続や QueryTable、テーブルのクエリを一覧表示します。
Sub ListConnectionsAndQueryTables()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim cn As WorkbookConnection
Dim ws As Worksheet, qt As QueryTable, lo As ListObject
On Error Resume Next
' ワークブック接続
For Each cn In wb.Connections
Debug.Print "Connection: " & cn.Name
If cn.Type = xlConnectionTypeODBC Then
Debug.Print " ODBC.Connection: " & cn.ODBCConnection.Connection
Debug.Print " ODBC.CommandText: " & cn.ODBCConnection.CommandText
ElseIf cn.Type = xlConnectionTypeOLEDB Then
Debug.Print " OLEDB.Connection: " & cn.OLEDBConnection.Connection
Debug.Print " OLEDB.CommandText: " & cn.OLEDBConnection.CommandText
ElseIf cn.Type = xlConnectionTypeMODEL Then
Debug.Print " (データ モデル接続:内部)"
Else
Debug.Print " Type: " & cn.Type
End If
Next cn
' 各シートの QueryTable/テーブルのクエリ
For Each ws In wb.Worksheets
For Each qt In ws.QueryTables
Debug.Print "QueryTable | " & ws.Name & " | " & qt.Name
Debug.Print " Connection: " & qt.Connection
Debug.Print " CommandText: " & qt.CommandText
Next qt
For Each lo In ws.ListObjects
If Not lo.QueryTable Is Nothing Then
Debug.Print "ListObject(Query) | " & ws.Name & " | " & lo.Name
Debug.Print " Connection: " & lo.QueryTable.Connection
Debug.Print " CommandText: " & lo.QueryTable.CommandText
End If
Next lo
Next ws
On Error GoTo 0
End Sub
ここでも、イミディエイトウィンドウで
- 旧ファイル名(例:
旧売上ファイル.xlsm) - 旧フォルダパス(例:
C:\Users\...\Desktop\)
を検索してみてください。該当する接続や QueryTable があれば、それを修正するか削除することでエラーを止められます。
手順3:総当たりスキャンで「.xls」「.xlsx」「.xlsm」などを拾う
最後に、フォーミュラ・定義名・ハイパーリンク・リンク画像など、外部参照が潜みやすい箇所を 正規表現 で一気にスキャンします。
Sub FindExternalReferences()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim re As Object: Set re = CreateObject("VBScript.RegExp")
Dim ws As Worksheet, rng As Range, cell As Range
Dim nm As Name, hl As Hyperlink, sh As Shape, ole As OLEObject
Dim ch As Chart, s As Series
Dim src As String
With re
.Pattern = "\.xl[a-z]{1,3}\b" ' .xls, .xlsx, .xlsm, .xlsb, .xlam 等
.IgnoreCase = True
.Global = False
End With
On Error Resume Next
' 1) 定義名(非表示名を含む)
For Each nm In wb.Names
If re.Test(nm.RefersTo) Then
Debug.Print "Name: " & nm.Name & " | RefersTo: " & nm.RefersTo
End If
Next nm
' 2) 各シート
For Each ws In wb.Worksheets
' 2-1) 数式セル
Set rng = Nothing
On Error Resume Next
Set rng = ws.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng.Cells
If re.Test(cell.Formula) Then
Debug.Print "Cell: " & ws.Name & "!" & cell.Address(0, 0) & " | " & cell.Formula
End If
Next cell
End If
' 2-2) ハイパーリンク
For Each hl In ws.Hyperlinks
If re.Test(hl.Address & "#" & hl.SubAddress) Then
Debug.Print "Hyperlink: " & ws.Name & " | " & hl.Address & "#" & hl.SubAddress
End If
Next hl
' 2-3) リンク画像・図形
For Each sh In ws.Shapes
Err.Clear
src = ""
On Error Resume Next
src = sh.LinkFormat.SourceFullName
If Err.Number = 0 Then
If re.Test(src) Then
Debug.Print "Linked Shape: " & ws.Name & " | " & sh.Name & " | " & src
End If
End If
On Error GoTo 0
Next sh
' 2-4) OLEオブジェクトの LinkedCell
For Each ole In ws.OLEObjects
If re.Test(ole.LinkedCell) Then
Debug.Print "OLE LinkedCell: " & ws.Name & " | " & ole.Name & " | " & ole.LinkedCell
End If
Next ole
' 2-5) 入力規則(リスト等)
Set rng = Nothing
On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng.Cells
If re.Test(cell.Validation.Formula1) Then
Debug.Print "DataValidation: " & ws.Name & "!" & cell.Address(0, 0) & _
" | " & cell.Validation.Formula1
End If
Next cell
End If
Next ws
' 3) グラフシートの系列
For Each ch In wb.Charts
For Each s In ch.SeriesCollection
If re.Test(s.Formula) Then
Debug.Print "ChartSheet: " & ch.Name & " | Series: " & s.Name & " | " & s.Formula
End If
Next s
Next ch
On Error GoTo 0
End Sub
このマクロは、
- 定義名
- 数式セル
- ハイパーリンク
- リンク画像・図形
- OLEオブジェクトの LinkedCell
- 入力規則
- グラフシートの系列
に対して、「.xls」「.xlsx」「.xlsm」などを含む文字列を機械的に拾い上げます。
| 対象 | 検出例 | 想定される外部リンク |
|---|---|---|
| Name | Name: 外部参照_売上 | RefersTo: ='[旧ファイル.xlsm]Sheet1'!$A$1:$A$100 | 非表示定義名が旧ファイルを参照 |
| Cell | Cell: 売上!A2 | ='[旧ファイル.xlsm]売上'!B2 | セル数式に外部参照が残っている |
| Hyperlink | Hyperlink: メニュー | C:\...\旧ファイル.xlsm#'シート1'!A1 | ハイパーリンク先が旧ファイル |
| Linked Shape | Linked Shape: グラフ画像 | C:\...\旧ファイル.xlsm!図1 | リンク画像のリンク元が旧ファイル |
ポイント: 旧ファイル名がはっきり分かっている場合は、正規表現を
.Pattern = "旧ファイル名\.xlsm"
のように変えて、ピンポイントで探すのも有効です。
見つかった外部参照をどう修正するか
ここまでのマクロで「外部リンクの出所」が見つかったら、次は 実際にそれを直す作業 です。代表的なパターンごとに UI での操作手順をまとめます。
ピボットテーブルのデータソースを修正する
- 問題のピボットテーブルのセルを1つクリック。
- リボンから [ピボットテーブル分析]タブ(または[オプション]) を開く。
- [データソースの変更] をクリック。
- 「テーブル/範囲の選択」 で、同一ブック内の表(テーブル)または範囲 を指定する。
元データ範囲を指定する際は、次のようにしておくと再発しにくくなります。
- 元データを Ctrl+T で テーブル化 しておく。
- ピボットのデータソースには テーブル名 を指定する。
こうしておけば、行が増減しても外部ブックではなく同一ブック内のテーブルを参照し続けてくれます。
グラフの系列の参照元を修正する
- 問題のグラフをクリック。
- 右クリックして [データの選択] を選ぶ。
- [凡例項目(系列)]の一覧から該当系列を選択し、[編集] をクリック。
- 「系列の値」 や 「系列名」 が
[旧ファイル名.xlsm]を含んでいないか確認。 - 旧ブックを指している場合は、同じブック内のシート・範囲 に修正。
系列式を直接編集することもできますが、Excel に慣れていない場合は上記ダイアログから修正した方が安全です。
接続/QueryTable/ListObject を修正・削除する
手順2のマクロで怪しい接続が見つかった場合は、次の流れで対処します。
- リボンの [データ]タブ を開く。
- [クエリと接続] をクリックし、右側のペインで該当の接続/クエリを選択。
- 右クリックして [プロパティ] または [編集] を選ぶ。
- [定義]タブ で、接続文字列やコマンドテキストに旧ファイル名/旧パスが無いか確認。
- 不要な接続なら 削除、必要なら新しいデータソースに合わせて文字列を修正。
ワークシート上のテーブルが原因の場合は、
- テーブル内のセルを1つ選択。
- リボンの [テーブル デザイン]タブ(バージョンにより名称は異なる)から [外部データのプロパティ] などを開く。
- 背後の QueryTable/接続設定を見直す。
定義名・入力規則・条件付き書式を修正する
マクロの出力で「Name: …」「DataValidation: …」などに旧ファイル名が含まれていた場合は、次のように修正します。
定義名(名前の管理)
- リボンから [数式 > 名前の管理] を開く。
- 該当の名前を選択し、[参照範囲] をクリック。
- 外部ブックを参照している場合は、同じブック内の範囲 に付け替える。
もし 名前の管理の一覧に出てこないのにマクロには出てくる 場合、それは 非表示の定義名 の可能性があります。その場合は、一時的に次のようなマクロで表示状態を切り替えると UI からも扱いやすくなります。
Sub ShowHiddenNameExample()
ThisWorkbook.Names("名前をここに").Visible = True
End Sub
入力規則・条件付き書式
- 入力規則:セルを選択 → [データ > データの入力規則] を開き、
「リスト」「数式」などの式の中に外部参照が無いか確認。 - 条件付き書式:セルを選択 → [ホーム > 条件付き書式 > ルールの管理] から、
数式ルールに外部参照が含まれていないかを確認。
リンク画像・OLEオブジェクトを修正する
マクロで「Linked Shape: …」がヒットした場合、その図形は別ブックの画像や範囲にリンクされています。
- 該当の図形・画像を選択。
- 右クリックメニューや図のツールから 「リンクの解除」 に相当する操作を探す。
- 見つからない場合は、コピーして[形式を選択して貼り付け]で「図」として貼り付け直す と、外部リンクではない純粋な画像になります。
OLEオブジェクトの場合も、LinkedCell やソースデータ が外部ブックを指していないかを個別に確認し、必要に応じて削除・挿し直しを行います。
RefreshAllで毎回エラーメッセージが出る場合の考え方
今回のように「各シートに入るたびに ActiveWorkbook.RefreshAll を実行する」仕様になっていると、たとえ外部リンクの出所が1つだけでも、シートを開くたびにメッセージが繰り返し出てしまいます。
RefreshAllを乱発しない仕組みに変える
可能なら、次のような見直しを検討するとよいです。
- Workbook_Open で1回だけ RefreshAll する
- どうしてもシートごとに最新化したい場合でも、
本当に必要なオブジェクトだけを個別に Refresh する
例えば、ThisWorkbook のモジュールに次のようなコードを書けば、ブックを開いたときに1度だけ RefreshAll を実行できます。
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub
各シートの Worksheet_Activate イベントなどに書かれている ActiveWorkbook.RefreshAll は、基本的には 削除またはコメントアウト した方が、安全で動作も軽くなります。
一時的にメッセージを抑止することは可能だが…
VBA では Application.DisplayAlerts = False にすることでメッセージを抑止できますが、
- 本当の原因(外部リンク)が残ったままになる
- 他の重要な警告メッセージも見えなくなる
というデメリットがあるため、恒久対策としてはおすすめできません。あくまで「原因切り分けの一時的な手段」として使うに留めましょう。
つまずきやすいポイントとよくある勘違い
- ループの1行だけをイミディエイトで実行しようとする
前述のとおり、For Each pc In wb.PivotCachesなどは単体では実行できません。必ずSub ~ End Subにまとめてから実行します。 - Shapes コレクションから直接グラフを探そうとしてエラーになる
旧コード例ではmsoChart定数などを使って図形を直接走査していましたが、
環境によってはうまく動作しません。ここでは、より確実なChartObjectsとChartsを使う方法に統一しています。 - 「編集リンクに何も出ない=外部リンクは無い」と思い込みがち
実際には、ピボットキャッシュやグラフ、接続など UI に出てこない外部参照が残っていることがよくあります。
RefreshAll でエラーが出る場合は「編集リンクに何も無い=安全」とは限りません。 - エラーが出るシートだけが怪しい
メッセージが出るタイミングが「そのシートを開いたとき」なだけで、実際の原因オブジェクトは
別シートのピボットや接続 のこともあります。マクロでブック全体をスキャンするのが安心です。
それでも解決しないときのチェックリスト
ここまでやってもまだメッセージが出る場合、次の点を追加で確認してみてください。
| チェック項目 | 確認内容 |
|---|---|
| Power Query の存在 | [データ > クエリと接続]に Power Query のクエリが残っていないか。旧ブックをソースにしていないか。 |
| ローカル定義名 | シートローカルな名前(例:Sheet1!名前)に外部参照が無いか。マクロ出力にシート名付きで出ているか確認。 |
| 非表示シート | VeryHidden のシートなど、普段見えないシートにピボットやグラフが残っていないか。 |
| 古いバージョンからの互換性 | xls 形式の頃から使っているブックだと、古いオブジェクトが温存されていることがある。不要なシートやオブジェクトを思い切って整理する。 |
| 新規ブックへのコピー | どうしても特定できない場合、問題のブックから「必要なシートだけ」を新しいブックにコピーし、 その後マクロで再度スキャンしてみる。 |
再発防止のポイント
同じような「外部リンクが見つからない地獄」にハマらないために、日頃から次の点を意識しておくと楽になります。
- ピボットの元データは同一ブックのテーブルにする
外部ブックの範囲を直接データソースにするのは極力避け、
必要ならまず同一ブックにデータを取り込んでからピボットを作る。 - グラフの元データも同一ブックに集約
別ブックの範囲を直接グラフの元データにしない。統合や共有の前に、データを1冊に集約してからグラフを作る。 - 統合前に「外部参照の棚卸し」を行う
ブックを統合したりファイル名を変更する前に、今回紹介したマクロのような「外部リンクチェック」を一度かけておく。 - RefreshAll を乱発しない設計にする
「シートを開くたびに RefreshAll」はエラーを増幅させやすいので、
できるだけ 必要な場面(たとえばブック起動時など)に1回だけ 実行する設計に見直す。 - 不要なシート・接続・クエリはすぐ削除
「念のため残しておく」の積み重ねが、数年後のトラブルの元になります。
使っていないものはその時点で削除しておくと、将来の自分が楽になります。
まとめ:UIに出ない外部リンクはVBAであぶり出す
本記事のポイントを整理すると次のとおりです。
- 「編集リンク」「名前の管理」に何も出ないのに RefreshAll で「…file.xlsm が見つかりません」と出る場合、外部リンクはセル以外(ピボットキャッシュ、グラフ、接続、定義名、図形など)に潜んでいる。
- まずは ピボットキャッシュ と グラフ系列 を一覧するマクロで、旧ファイル名や
.xlsmを含む行を探す。 - 次に 接続/QueryTable/ListObject を列挙し、接続文字列やコマンドテキストに旧パスが残っていないか確認する。
- 最後に 総当たりスキャン で、定義名・数式・ハイパーリンク・リンク画像などに含まれる
.xlsxや旧ファイル名を機械的に拾う。 - 見つかったオブジェクトごとに、データソースを同一ブック内に付け替える/不要なら削除することで、外部リンクエラーは解消できる。
- 再発防止のために、外部ブックを直接参照する設計を避けることと、RefreshAll の呼び出しを最小限に抑えることが重要。
「外部リンクが見つからない」問題は、感覚的に探してもなかなか解決しません。今回のように VBA で体系的に洗い出す仕組みを一度作っておけば、今後別のファイルで同じような症状が出ても、短時間で原因にたどり着けるはずです。

コメント