Excelの警告「保存していないファイルを参照しています」の原因は幽霊リンク？VBAで削除！
Excelで他のブックからシートをコピーして作業中ブックに持ってきた時、保存すると「保存していないファイルを参照しています」の警告メッセージが出るようになった。
実害はなかったのだが、保存の都度メッセージが出るようになり、煩わしくなり、ストレスを感じてきた。
もともと作業中のブックに外部リンクはなかったので、追加したシートが悪さしていることは明らかだが、シートのどこに外部リンクがあるのかが探せなかった。
その時に探した方法とは、Excelメニューの数式＞名前の管理を確認、また、外部リンクは"[ブック名]"のように指定されるので、ブックを文字列 "[" で検索してヒットするかを確認したことだ。
でも、見つからなかった。
そこで、ネットで調べたところ、リンクが壊れた「幽霊リンク」というものがあるらしいことがわかった。
それは表には出てこず、Excel内部に残ることがあるらしい。
まさに私のケースはそれに該当するような気がしたわけだが、表には出てこないということで、ではどうすればよいかと考えると、VBAで削除することが考えられるのである。
ということで、VBAコードを以下に示す。 外部参照リンクをすべて表示するVBAコードである。
1' 外部参照リンクをすべて表示する
2Sub ScanAllExternalLinks()
3
4 Dim ws As Worksheet
5 Dim lo As ListObject
6 Dim c As Range
7 Dim f As FormatCondition
8 Dim nm As Name
9 Dim msg As String
10 Dim formula As String
11 Dim dv As Validation
12 Dim found As Boolean
13
14 Debug.Print "=== 外部リンク総合スキャン開始 ==="
15
16 '--- 1. 名前定義 ---
17 For Each nm In ThisWorkbook.Names
18 On Error Resume Next
19 formula = nm.RefersTo
20 On Error GoTo 0
21 If ContainsExternalRef(formula) Then
22 msg = "【名前定義】" & nm.Name & " = " & formula
23 Debug.Print msg
24 found = True
25 End If
26 Next nm
27
28 '--- 2. 各ワークシート ---
29 For Each ws In ThisWorkbook.Worksheets
30
31 '--- 2-1 セルの数式 ---
32 For Each c In ws.UsedRange.Cells
33 If c.HasFormula Then
34 formula = c.Formula
35 If ContainsExternalRef(formula) Then
36 msg = "【数式】" & ws.Name & "!" & c.Address & " = " & formula
37 Debug.Print msg
38 found = True
39 End If
40 End If
41
42 '--- 2-2 入力規則 ---
43 If c.Validation.Type <> xlValidateNone Then
44 On Error Resume Next
45 formula = c.Validation.Formula1
46 On Error GoTo 0
47 If ContainsExternalRef(formula) Then
48 msg = "【入力規則】" & ws.Name & "!" & c.Address & " = " & formula
49 Debug.Print msg
50 found = True
51 End If
52 End If
53 Next c
54
55 '--- 2-3 条件付き書式 ---
56 For Each f In ws.UsedRange.FormatConditions
57 On Error Resume Next
58 formula = ""
59 If f.Type = xlExpression Then
60 formula = f.Formula1
61 ElseIf f.Type = xlCellValue Then
62 formula = f.Formula1
63 End If
64 On Error GoTo 0
65 If ContainsExternalRef(formula) Then
66 msg = "【条件付き書式】" & ws.Name & " ルール = " & formula
67 Debug.Print msg
68 found = True
69 End If
70 Next f
71
72 '--- 2-4 テーブル（ListObject） ---
73 For Each lo In ws.ListObjects
74 formula = lo.SourceData
75 If ContainsExternalRef(formula) Then
76 msg = "【テーブル】" & ws.Name & "!" & lo.Name & " = " & formula
77 Debug.Print msg
78 found = True
79 End If
80 Next lo
81
82 Next ws
83
84 '--- 3. Excel が保持しているリンク一覧（壊れたリンク含む） ---
85 Dim links As Variant
86 links = ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
87 If Not IsEmpty(links) Then
88 Dim i As Long
89 For i = LBound(links) To UBound(links)
90 Debug.Print "【LinkSources】" & links(i)
91 found = True
92 Next i
93 End If
94
95 If Not found Then
96 Debug.Print "外部参照は見つかりませんでした。"
97 End If
98
99 Debug.Print "=== スキャン終了 ==="
100
101End Sub
102
103
104'===== 外部リンクを判定する関数 =====
105Function ContainsExternalRef(str As String) As Boolean
106 If Len(str) = 0 Then
107 ContainsExternalRef = False
108 Exit Function
109 End If
110
111 '外部参照は [xxxx.xlsx] のように角括弧が入る
112 If InStr(str, "[") > 0 And InStr(str, "]") > 0 Then
113 ContainsExternalRef = True
114 Else
115 ContainsExternalRef = False
116 End If
117End Function
これを実行することでデバッグコンソール（イミディエイトウィンドウ）に外部リンクの一覧が出力される。
開発タブより標準モジュールを作成して、上記コードをコピペして実行すれば良い。
外部リンクの削除方法は、以下VBAを参考に適宜改変して実行すれば良い。
注意すべきは、以下コードの実行によりすべての外部リンクを削除する可能性があるので、必ず自分でコードを理解して、必要な分だけを削除するよう修正して実行すること。
1' 外部リンクを削除する VBA
2Sub DeleteAllExternalLinks()
3
4 Dim ws As Worksheet
5 Dim lo As ListObject
6 Dim c As Range
7 Dim f As FormatCondition
8 Dim nm As Name
9 Dim dv As Validation
10 Dim formula As String
11
12 '--- 1. 名前定義 ---
13 For Each nm In ThisWorkbook.Names
14 formula = nm.RefersTo
15 If ContainsExternalRef(formula) Then
16 nm.Delete
17 End If
18 Next nm
19
20 '--- 2. 各ワークシート ---
21 For Each ws In ThisWorkbook.Worksheets
22
23 '2-1 セルの数式
24 For Each c In ws.UsedRange.Cells
25 If c.HasFormula Then
26 formula = c.Formula
27 If ContainsExternalRef(formula) Then
28 '数式を値に置き換え
29 c.Value = c.Value
30 End If
31 End If
32
33 '2-2 データ入力規則
34 If c.Validation.Type <> xlValidateNone Then
35 On Error Resume Next
36 formula = c.Validation.Formula1
37 On Error GoTo 0
38 If ContainsExternalRef(formula) Then
39 c.Validation.Delete
40 End If
41 End If
42 Next c
43
44 '2-3 条件付き書式
45 For Each f In ws.UsedRange.FormatConditions
46 On Error Resume Next
47 formula = ""
48 If f.Type = xlExpression Then
49 formula = f.Formula1
50 ElseIf f.Type = xlCellValue Then
51 formula = f.Formula1
52 End If
53 On Error GoTo 0
54 If ContainsExternalRef(formula) Then
55 f.Delete
56 End If
57 Next f
58
59 '2-4 テーブル
60 For Each lo In ws.ListObjects
61 formula = lo.SourceData
62 If ContainsExternalRef(formula) Then
63 'テーブルを範囲に戻す
64 lo.Unlist
65 End If
66 Next lo
67
68 Next ws
69
70 '--- 3. LinkSourcesに残るリンクを削除 ---
71 Dim links As Variant
72 Dim i As Long
73 links = ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
74 If Not IsEmpty(links) Then
75 For i = LBound(links) To UBound(links)
76 ThisWorkbook.BreakLink Name:=links(i), Type:=xlLinkTypeExcelLinks
77 Next i
78 End If
79
80 MsgBox "すべての外部リンクを削除しました。", vbInformation
81
82End Sub
保存時は、上記の追加した標準モジュールを削除する。 これで、保存の都度出現していた「保存していないファイルを参照しています」のメッセージは出なくなるはずだ。