他ブックへのリンクエラーを探し解除
リンクエラーが見つけられない・・・
「リンクの編集」で、「リンクの解除」を選択してもリンクが削除できない・・・
こんな経験をした人は多いのではないでしょうか。
かつ、その参照先のブックが無くなってしまっている・・・
こんな場合に、リンクエラーとなります。
この操作で消せないリンクエラーがたまに存在します。
外部(他ブック)リンクはなるべく使用しないほうが良いということだけ言っておきます。
ここで、「リンクの」編集を選択するか、
「続行」を選択して開いた後に、リボンの「データ」→「リンクの編集」を選択すると、
「状態」が「OK」のものは、リンク先のファイルが存在しているものになります。
エラーとなっているリンクを選択して、「リンクの解除」で通常は消せるのですが、
時おりこの操作で消せないエラーが存在します。
どのようにリンクエラーを探したら良いか・・・
かいつまんで解説します。
数式、ボタン等の登録マクロ
名前定義、条件付き書式、入力規則
条件付き書式や入力規則は、そもそも通常操作では他ブックを参照できない仕様になっています。
これらは、シートを別ブックや新規ブックに移動したりすることで発生するものになります。
大体こんな感じだという程度にとらえておいてください。
「条件付き書式」「入力規則」このどちらかにリンクエラーが残っている場合になるでしょう。
検索する文字列:[
検索場所:ブック
検索対象:数式
このように指定して、「全てを検索」
つまり他ブックを参照している場合は、"[ブックのパス\ブック名]"このようになっているので、
Excelとして特別な記号[を探せばよいということです。
リンク切れを起こした時の状況によっては、
"[Book1]"のように拡張子がない場合も存在します。
これは、保存前のブックを参照している状態で、そのブックが無くななってしまったような場合に発生します。
・名前定義
「数式」→「名前の管理」(Ctrl+F3)
ここで、選択して「削除」で消すことができます。
この削除が失敗することは滅多にありません。
・ボタン等の登録マクロ、条件付き書式、入力規則
外部参照しているかを一旦おいとけば、
「ジャンプ」(Ctrl+G)
「オブジェクト」「条件付き書式」「データの入力規則」
それぞれを選択して、「OK」をすれば、
オブジェクトまたは当該セルが選択されます。
これで、場所はわかります。
しかし、具体的に外部リンクなのかどうかは内容を一つずつ確認しなければなりません。
そして、これはシート毎に行う必要があります。
つまり、
シート数が多く、それぞれのシートに多くの設定がある場合には、
ほとんど探しようがないという状態になってしまいます。
このように外部(他ブック)リンクを探すには、かなりの手間がかかります。
外部リンクを探せるマクロVBAがあればとても便利です。
しかしWEBを探してみましたが、すっきりと探してくれるマクロVBAは見つけられませんでした。
公開にあたって、かなり機能を盛り込みました。
自分で使うだけなら、UIや使い勝手はどうでも良いのですが、
公開するのであれば、せっかくなのでもっともらしいアプリっぽくしてみました。
VBAの詳しい解説は省略していますが、
意図的に、細かいテクニックをいろいろなパターンで使っていますので、
VBA内のコメントを頼りにコードを読み解いていくのは、VBAの良い勉強になると思います。
変更前後を掲載していますので、プロシージャー分割等の参考にしてください。
機能概要
リンク切れかどうかにかかわらず、全ての外部(他のブック)リンクを出力します。
・リンク判定として、リンク切れの場合は「×」、数式が複雑で判定困難な場合は「△」を出力します。
・削除すべきリンクについては、削除欄に「する」を出力。
行全体、列全体に対して(条件付き書式や入力規則が)リンク設定されている場合は膨大な数となってしまいます。
連続セルが同一設定の場合はセル範囲で出力するようにしていますが、
1行おきに交互に違う設定がしてある場合等、
セル範囲でまとめて表現できない場合に出力行数が膨大になってしまう場合があります。
そのような場合の対応として、「表示最大件数」を超えた時点で処理を終了しています。
また、リンクされている件数が多いと多大な処理時間がかかってしまうので、
最初は少ない数字にして確認したほうが良いでしょう。
シート構成
全体の流れ
数式
名前定義
条件付き書式
入力規則
ボタン等の登録マクロ
これら以外にも存在する可能性はありそうですが、特殊なものになるでしょう。
全てに対応する必要もないとは思いますが、
今後、対応したほうが良いようなものが出てきたら本ページおよびマクロに追加していきます。
検索対象のブックを選択
・他のブックが何も開かれていない場合
検索対象のブックを指定してください。
開かれていないブックを検索するなら、
「Browse」でファイルを開くダイアログが開かれますのでそこで選択してください。
結果が5行目以降に表示されます。
×:リンク先ファイルなし
△:リンク数式が複雑なため判定保留
→関数がネストされている場合、
ファイル名を全て正しく抽出するのが困難であり、
ユーザーが自身で判定したほうが良いだろうということです。
検索時には、削除推奨のリンクに対して「する」を設定しています。
検索完了メッセージが表示されます。
処理時間:処理にかかった時間
確認したセル数:チェックしたセルおよびオブシェクト数の総数
外部リンク数:見つかった外部(他ブック)リンク数
エラーリンク数:リンク先のファイルが存在しない数
保留リンク数:リンク数式が複雑なため判定保留した数
確認したら「OK」
表示された一覧を確認し、E列の「する」を適切に指定してください。
↓
「削除」ボタンでE列「する」が削除されます。
万一間違った削除をしてしまったときは、
対象ブックは「保存しない」で閉じることで対応してください。
今後の事を踏まえると手動で削除したほうが良いでしょう。
手動で削除することで、その操作がしっかりと頭に残りますので。
変更前後を掲載していますので、プロシージャー分割等の参考にしてください。
同じテーマ「マクロVBAサンプル集」の記事
数独(ナンプレ)を解くアルゴリズムの要点とパフォーマンスの検証№1
ナンバーリンク(パズル)を解くVBAに挑戦№1
ナンバーリンクを解くVBAのパフォーマンス改善№1
オセロを作りながらマクロVBAを学ぼう
他ブックへのリンクエラーを探し解除
Excelシートの複雑な計算式を解析するVBA
Excel将棋:マクロVBAの学習用(№1)
Excel囲碁:万波奈穂先生に捧ぐ
Excel囲碁:再起動後も続けて打てるように改造
エクセルVBAで15パズルを作ってみた
エクセル麻雀ミニゲーム
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- 他ブックへのリンクエラーを探し解除
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。