ExcelマクロVBAサンプル集
他ブックへのリンクエラーを探し解除

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2019-03-19

他ブックへのリンクエラーを探し解除


リンクエラーが見つけられない・・・
「リンクの編集」で、「リンクの解除」を選択してもリンクが削除できない・・・
こんな経験をした人は多いのではないでしょうか。


エクセルをいろいろと操作していると、意図せずに参照先が別ブックになってしまい、
かつ、その参照先のブックが無くなってしまっている・・・
こんな場合に、リンクエラーとなります。

通常は、「リンクの編集」→「リンクの解除」で消せるのですが、
この操作で消せないリンクエラーがたまに存在します。

もちろん大前提として、
外部(他ブック)リンクはなるべく使用しないほうが良いということだけ言っておきます。

※この場合のリンクにはハイパーリンクは含まれません。

Excelファイルを開いたときに、以下のダイアログメッセージがでて、

マクロVBA リンクエラー画像

「更新する」を選択すると、

マクロVBA リンクエラー画像

このようなダイアログメッセージが出るときがあります。
ここで、「リンクの」編集を選択するか、
「続行」を選択して開いた後に、リボンの「データ」→「リンクの編集」を選択すると、

マクロVBA リンクエラー画像

「状態」が「エラー:・・・」となっているものが、リンク先のファイルが存在しないエラーとなります。
「状態」が「OK」のものは、リンク先のファイルが存在しているものになります。
エラーとなっているリンクを選択して、「リンクの解除」で通常は消せるのですが、
時おりこの操作で消せないエラーが存在します。

どういう場合に、「リンクの解除」で消せなくなるのか・・・
どのようにリンクエラーを探したら良いか・・・
かいつまんで解説します。


「リンクの解除」で消せるもの、消せないもの

「リンクの解除」で消せるもの
 数式、ボタン等の登録マクロ

「リンクの解除」で消せないもの
 名前定義、条件付き書式、入力規則


名前定義は、定義だけなら他に影響するものではなく名前定義を使った数式が外部リンクとして認識されます。
条件付き書式や入力規則は、そもそも通常操作では他ブックを参照できない仕様になっています。
これらは、シートを別ブックや新規ブックに移動したりすることで発生するものになります。

概ねこのような感じではありますが、絶対とは言えないものです。
大体こんな感じだという程度にとらえておいてください。

つまり、リンクエラーが解消できない場合は、
「条件付き書式」「入力規則」このどちらかにリンクエラーが残っている場合になるでしょう。



外部(他ブック)リンクの探し方

数式

「検索」で、
検索する文字列:[
検索場所:ブック
検索対象:数式
このように指定して、「全てを検索」

マクロVBA リンクエラー画像

表示された、一覧の数式を見れば外部参照であることがわかります。
つまり他ブックを参照している場合は、"[ブックのパス\ブック名]"このようになっているので、
Excelとして特別な記号[を探せばよいということです。

検索文字として、".xls"でも大抵の場合は良いのですが、
リンク切れを起こした時の状況によっては、
"[Book1]"のように拡張子がない場合も存在します。
これは、保存前のブックを参照している状態で、そのブックが無くななってしまったような場合に発生します。


名前定義

名前の管理を開いてください。
「数式」→「名前の管理」(Ctrl+F3)

マクロVBA リンクエラー画像

「名前の管理」のダイアログが表示され、名前の定義の一覧が表示されます。

マクロVBA リンクエラー画像

参照範囲を見れば、外部参照が確認できます。
ここで、選択して「削除」で消すことができます。
この削除が失敗することは滅多にありません。


ボタン等の登録マクロ、条件付き書式、入力規則

これらを簡単に探す方法がありません。
外部参照しているかを一旦おいとけば、
「ジャンプ」(Ctrl+G)

マクロVBA リンクエラー画像

「セル選択」

マクロVBA リンクエラー画像

ここで、
「オブジェクト」「条件付き書式」「データの入力規則」
それぞれを選択して、「OK」をすれば、
オブジェクトまたは当該セルが選択されます。
これで、場所はわかります。
しかし、具体的に外部リンクなのかどうかは内容を一つずつ確認しなければなりません。
そして、これはシート毎に行う必要があります。
つまり、
シート数が多く、それぞれのシートに多くの設定がある場合には、
ほとんど探しようがないという状態になってしまいます。


このように外部(他ブック)リンクを探すには、かなりの手間がかかります。
外部リンクを探せるマクロVBAがあればとても便利です。


しかしWEBを探してみましたが、すっきりと探してくれるマクロVBAは見つけられませんでした。

そこで、自分でも必要で使いたいということで、マクロを作成したのでここに公開します。
公開にあたって、かなり機能を盛り込みました。
自分で使うだけなら、UIや使い勝手はどうでも良いのですが、
公開するのであれば、せっかくなのでもっともらしいアプリっぽくしてみました。

マクロVBAもかなり大きくなってしまったので、VBAコードは次ページに掲載しています。
VBAの詳しい解説は省略していますが、
意図的に、細かいテクニックをいろいろなパターンで使っていますので、
VBA内のコメントを頼りにコードを読み解いていくのは、VBAの良い勉強になると思います。


他ブックへのリンクエラーを探し解除するマクロ

一旦掲載後に変更しています。
変更前後を掲載していますので、プロシージャー分割等の参考にしてください。


機能概要

「検索開始」ボタン
・指定ブックの外部(他のブック)リンクを全て取得して一覧表示します。
 リンク切れかどうかにかかわらず、全ての外部(他のブック)リンクを出力します。
・リンク判定として、リンク切れの場合は「×」、数式が複雑で判定困難な場合は「△」を出力します。
・削除すべきリンクについては、削除欄に「する」を出力。
「削除」ボタン
削除欄「する」のリンクを削除します。
「表示最大件数」について
外部(他のブック)リンクは、正常かエラーかにかかわらずセル毎に全て一覧に出力します。
行全体、列全体に対して(条件付き書式や入力規則が)リンク設定されている場合は膨大な数となってしまいます。
連続セルが同一設定の場合はセル範囲で出力するようにしていますが、
1行おきに交互に違う設定がしてある場合等、
セル範囲でまとめて表現できない場合に出力行数が膨大になってしまう場合があります。
そのような場合の対応として、「表示最大件数」を超えた時点で処理を終了しています。
また、リンクされている件数が多いと多大な処理時間がかかってしまうので、
最初は少ない数字にして確認したほうが良いでしょう。


シート構成

シートは、「リンクエラー検索」の一つだけです。

マクロVBA リンクエラー画像


全体の流れ

「検索開始」ボタン
検索対象は、
数式
名前定義
条件付き書式
入力規則
ボタン等の登録マクロ

これら以外にも存在する可能性はありそうですが、特殊なものになるでしょう。
全てに対応する必要もないとは思いますが、
今後、対応したほうが良いようなものが出てきたら本ページおよびマクロに追加していきます。

検索対象のブックを選択
このマクロブック以外が開かれているかどうかで動作が分かれます。
・他のブックが何も開かれていない場合
ファイルを開くダイアログ(VBA標準のダイアログ)が表示されます。
検索対象のブックを指定してください。
・他のブックが開かれている場合
ブックの選択(ユーザーフォーム)が表示されます。

マクロVBA リンクエラー画像

既に開かれているブックが検索対象なら、ここで選択し「OK」
開かれていないブックを検索するなら、
「Browse」でファイルを開くダイアログが開かれますのでそこで選択してください。

結果が5行目以降に表示されます。
・D列「リンク判定」は、リンク先のファイルの存在判定結果です。
 ×:リンク先ファイルなし
 △:リンク数式が複雑なため判定保留
  →関数がネストされている場合、
   ファイル名を全て正しく抽出するのが困難であり、
   ユーザーが自身で判定したほうが良いだろうということです。

・E列「削除」は、「削除」ボタンで削除するかどうかを指定しますが、
 検索時には、削除推奨のリンクに対して「する」を設定しています。

検索完了メッセージが表示されます。
メッセージ内容は、
処理時間:処理にかかった時間
確認したセル数:チェックしたセルおよびオブシェクト数の総数
外部リンク数:見つかった外部(他ブック)リンク数
エラーリンク数:リンク先のファイルが存在しない数
保留リンク数:リンク数式が複雑なため判定保留した数
確認したら「OK」

表示された一覧を確認し、E列の「する」を適切に指定してください。

「削除」ボタンでE列「する」が削除されます。
この「削除」はマクロ動作なので元に戻すことができません。
万一間違った削除をしてしまったときは、
対象ブックは「保存しない」で閉じることで対応してください。

※件数が少ないときは、
 今後の事を踏まえると手動で削除したほうが良いでしょう。
 手動で削除することで、その操作がしっかりと頭に残りますので。


他ブックへのリンクエラーを探し解除するマクロ

一旦掲載後に変更しています。
変更前後を掲載していますので、プロシージャー分割等の参考にしてください。




同じテーマ「マクロVBAサンプル集」の記事

エクセルでファイル一覧を作成
アメブロの記事本文をVBAでバックアップする№6
数独(ナンプレ)を解くVBAに挑戦№5
数独(ナンプレ)を解くアルゴリズムの要点とパフォーマンスの検証№4
ナンバーリンク(パズル)を解くVBAに挑戦№8
ナンバーリンクを解くVBAのパフォーマンス改善№3
オセロを作りながらマクロVBAを学ぼう
他ブックへのリンクエラーを探し解除するマクロ(変更前)
Excelシートの複雑な計算式を解析するVBAの関数構文


新着記事NEW ・・・新着記事一覧を見る

VBAコードの全プロシージャー・プロパィ一覧を取得|VBAサンプル集(10月12日)
VBAでエラー行番号を取得できるErl関数|VBA技術解説(10月12日)
手動計算時の注意点と再計算方法|ExcelマクロVBA技術解説(10月9日)
引数の数を可変にできるパラメーター配列(ParamArray)|VBA入門(10月7日)
VBEの使い方:デバッグ|ExcelマクロVBA入門(10月6日)
VBAにおける配列やコレクションの起点について|VBA技術解説(10月5日)
VBEの使い方:オブジェクト ブラウザー|VBA入門(10月5日)
VBEの使い方:ウォッチ ウィンドウ|VBA入門(10月4日)
VBEの使い方:ローカル ウィンドウ|VBA入門(10月3日)
VBEの使い方:イミディエイト ウィンドウ|VBA入門(10月2日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • 他ブックへのリンクエラーを探し解除

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ