【Excel】引き継ぎブックで先に見る外部リンク・名前・接続

引き継いだExcelのブックを開いたとき、私が最初に見るのは関数と非表示列です。数式の参照が切れていたり、計算の根拠があちこちにばらけていたりすることは、日常的にあります。

Excelの引き継ぎでは、関数と非表示列を見たあとに、もう一か所、見ておきたい場所があります。外部リンク、名前の管理、クエリと接続です。セルの数式だけを見ていても、ここにある根拠は見つかりません。この記事では、3つの場所を実機で確かめた結果をまとめます。

検証環境はExcel 16.0（Windows、日本語環境）です。他のバージョンやMac版は未検証です。

想定読者
Excelの引き継ぎで先に見るのは関数と非表示列
外部リンク：LinkSourcesとリンクの編集
名前の管理：#REF!と非表示の名前
クエリと接続：接続専用のクエリ
確認する場所と見つかるもの
VBAで一覧を出す
よくある失敗
応用例：一覧を引き継ぎメモにする
まとめ

想定読者

前任者のブックを引き継ぎ、どこから確認するかを決めたい人です。

Excelの引き継ぎで先に見るのは関数と非表示列

関数は、検索の対象を数式にして、#REF!と[を探します。VBAのFind（検索場所は数式）では、シートを削除したあとの数式の#REF!も、他のブックを指す数式の[も見つかりました。今回の検証は1シートです。エラーになっている数式セルの数も、SpecialCellsで数えられました。

非表示は、列も行もシートも見落としやすい場所です。実機では、非表示の列に入れた数式も、SpecialCellsで拾えました。

シートには、VeryHiddenという状態があります。VeryHiddenのシートがあるブックで、シート見出しを右クリックすると、「再表示」が選べませんでした。VBAでは、VisibleがxlSheetVeryHidden（値は2）として見つかります。

シート見出しの右クリックメニュー。VeryHiddenのシートがあっても「再表示」が選べない — VeryHiddenのシートがあっても「再表示」はグレーのまま

外部リンク：LinkSourcesとリンクの編集

セルの数式が他のブックを指していると、VBAのLinkSourcesにリンク元のファイルが出ました。リンクがないブックでは、Emptyが返りました。

注意したいのは、名前だけが他のブックを指す場合です。セルに数式がなく、名前の参照範囲だけが別のブックのセルを指すブックを、Names.Addで作りました。作った直後のブックと、保存して開いたままのブックでは、LinkSourcesはEmptyでした。保存して閉じ、開き直したブック（リンクの更新なし・読み取り専用で開いた）では、1件になりました。参照先のブックを開いていても閉じていても、開き直したあとに再計算しても、結果は同じでした。数値は、ブックを開いて最初にLinkSourcesを呼んだときのものです。

どの状態でも、[のセル検索には出ませんでした。名前の管理には、参照範囲として表示されます。

名前の形を変えても、同じ結果でした。他のブックの名前を指す名前、OFFSETやINDEXで外部のセルを組む名前、テーブルの列を指す名前、参照先が.csvの名前は、作った直後はEmpty、開き直すと1件です。INDIRECTで文字列から参照を組んだ名前は、開き直してもEmptyでした。一方、シートのコピーで元ブックを指す名前が持ち込まれたブックは、保存前でも1件でした。

名前の管理の画面。N_Extの参照範囲に、別のブックを指す場所が表示されている — 名前の管理には、別のブックを指す参照範囲が出る

作った直後（未保存）のブックでは、「データ」タブの「リンクの編集」もグレーで押せませんでした。

データタブの「接続」グループ。作った直後のブックでは「リンクの編集」がグレーで押せない — 作った直後のブックでは、「リンクの編集」が押せなかった

開き直したブックでは、「リンクの編集」が押せて、リンク元の一覧に参照先のファイルが出ました。リンクの一覧だけで終えず、名前の管理の参照範囲も見てください。

リンクの編集のダイアログ。リンク元の一覧に、参照先のファイルが表示されている — 開き直したブックでは、「リンクの編集」が押せて、参照先が一覧に出た

VBAのBreakLinkでセルのリンクを解除すると、数式は値に変わりました。実行前に、ブックのコピーを取っておくと安心です。

外部リンクの仕組みは、Microsoftサポートの「ブックリンクを作成する」で説明されています。このページには、定義された名前が別のブックを指す場合の説明もあります。

名前の管理：#REF!と非表示の名前

「数式」タブの「名前の管理」で確認します。

シートを削除すると、そのシートを指していた名前の参照先は#REF!になりました。その名前を使っているセルは、エラー値になりました。エラー値の意味は、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認で整理しています。

非表示の名前は、名前の管理の一覧に出ませんでした。VBAでは、For Eachで拾えます。

名前の参照先に構造化参照を書くときの形は、【Excel】構造化参照の@は自分の行だけ｜1行マスタの引き方で試しました。VBAの Names.Add に Table[列名] を渡すと実行時エラー1004になり、Table[[#Data],[列名]] の形なら登録できます。

クエリと接続：接続専用のクエリ

「データ」タブの「クエリと接続」で確認します。VBAのQueriesでは、クエリ名とM式を読めます。M式にフォルダのパスが書かれていれば、文字列として読めます。そのパスが存在しないと、読み込みでエラー1004になり、フォルダが見つからないというメッセージが出ました。フォルダ取込みの設定は、【Power Query】フォルダ内データを自動取込みする設定で紹介しています。

読み込み先を持たない「接続専用」のクエリは、シートに表が出ません。「クエリと接続」の画面には「接続専用」と出ますが、VBAのConnectionsには出ませんでした。2つのクエリのうち、読み込み済みは接続が1件、接続専用は0件です。クエリの確認は、Queriesで行います。

ブッククエリの画面。Q_Loadは1行読み込まれ、Q_Onlyは接続専用と表示されている — 読み込み済みのクエリと、接続専用のクエリ

渡す側の話として、シートを別ブックにコピーしたときに名前やクエリがどう残るかは、【VBA】シートを別ブックにコピーして配布用ブックを作るで実測しています。名前を使う数式を入れたあとの元ブックからシートをコピーすると、値にしたあとでも、元ブックを指す名前が新しいブックに残りました。

確認する場所と見つかるもの

確認する場所	見つかるもの	放置するとどうなるか
関数（検索：`#REF!`、`[`）	参照切れの数式、他のブックを指す数式	参照切れのまま、計算の根拠が追えない
非表示の列・行・シート	見えない数式、VeryHiddenのシート	数式が見えない場所に残る
`LinkSources`（リンクの編集）	セルの数式が指す他のブック、開き直したブックでは名前が指す他のブック	開き直す前のブックでは、名前だけが指すリンクは出ない
名前の管理	`#REF!`の名前、非表示の名前、他のブックを指す名前	使うセルがエラーになる
クエリと接続（`Queries`）	M式のパス、接続専用のクエリ	パスがないと読み込みでエラー

VBAで一覧を出す

リンク・名前・クエリを、まとめて出すコードです。標準モジュールに貼り付けて、ShowHandoverReportを実行すると、イミディエイトウィンドウに出力されます。

Public Function HandoverReport(wb As Workbook) As String
    Dim v As Variant, i As Long, nm As Name, q As WorkbookQuery, s As String, r As String
    v = wb.LinkSources(xlExcelLinks)
    If Not IsEmpty(v) Then
        For i = LBound(v) To UBound(v)
            s = s & "Link: " & v(i) & vbLf
        Next i
    End If
    For Each nm In wb.Names
        r = nm.RefersTo
        s = s & "Name: " & nm.Name
        If Not nm.Visible Then s = s & " [hidden]"
        If InStr(1, r, ".xl", vbTextCompare) > 0 Then s = s & " [ext]"
        s = s & " | " & r & vbLf
    Next nm
    For Each q In wb.Queries
        s = s & "Query: " & q.Name & vbLf
    Next q
    HandoverReport = s
End Function

Public Sub ShowHandoverReport()
    Debug.Print HandoverReport(ActiveWorkbook)
End Sub

非表示の名前には[hidden]、参照範囲に.xlを含む名前には[ext]が付きます。xlsx・xlsm・xls・xlsbの拡張子で、[ext]が付くことを確認しました。名前の形は、外部のセルを直接指す名前、他のブックの名前を指す名前、テーブルの列を指す名前、OFFSETやINDEXで外部のセルを組む名前のどれでも、[ext]が付きました。参照先が.csvの名前には、[ext]が付きませんでした。.xlを含まない参照先は[ext]では見つからず、作った直後のブックではLinkSourcesにも出ないため、名前の管理で参照範囲を見ます。INDIRECTで文字列から参照を組んだ名前にも、参照範囲の文字列に.xlsxが含まれるため[ext]が付きましたが、外部リンクではなく、LinkSourcesはEmptyのままでした。

LinkSourcesは、名前を読む前に呼んでください。Namesの個々の名前のNameやRefersToを読んだあとに呼ぶと、リンクがあるブックでもEmptyになりました。Names.Countやセルの検索のあとでは、1件でした。上のコードは、先にLinkSourcesを呼んでいます。

#REF!の名前は、参照先の文字列で分かります。開き直す前のブックでは、名前だけが指すリンクはLinkSourcesに出ないため、Link:の行には出ません。[ext]の行で見つけます。開き直したブックでは、Link:の行にも出ます。

よくある失敗

リンクの一覧だけで終える：開き直す前のブックでは、名前だけが指すリンクは、LinkSourcesにも「リンクの編集」にも出ませんでした。名前の管理の参照範囲も見ます。
Connectionsだけ見る：接続専用のクエリが漏れます。Queriesも見ます。
非表示を見落とす：列、名前、VeryHiddenのシートは、通常の画面に出ません。
先にリンクを解除する：数式が値になり、根拠が追えなくなります。
名前を読んでからLinkSourcesを呼ぶ：リンクがあってもEmptyになりました。先にLinkSourcesを呼びます。