seraphyの日記

日記というよりは過去を振り返るときのための単なる備忘録

Excelのリンクが解除できない!!場合の問題箇所の特定方法

Excelのリンクが解除できない!!場合の問題箇所の特定方法

概要

マクロ付きExcel(*.xlsm)を作っていたら、いつのまにか開くたびにリンクエラーが発生して、しかも、それが消せない。という状況に陥った。

いったら、どうしたら消せるのか?

経緯

リンクエラーが発生している場合、ネットではセルのどこかに別bookを参照しているところがあるので、検索ボックスで.xlsを探せ、とか書いてある。

しかし、セルの数式、値いずれも.xlsを含むものは無かった。

excelの非表示オブジェクトで参照しているものがあるかもだから、VBAを使って、図形や名前定義を含めて.xlsを検索して該当アイテムを消すべし」というコード例があったので試してみたが、これでも解決しない。


....


そういえば、*.xlsx, *.xlsmはzip形式になっていて、展開すると中身はXMLだったよなー、ということを思い出した。

zip展開してgrepすれば何か分かるのではないか?

ZIP展開した結果

発見しました!

sheet2.xmlファイル
    ....
    <extLst>
        <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" 
            xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
            <x14:dataValidations disablePrompts="1" count="2" 
                xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
                <x14:dataValidation type="list" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>'C:\Users\seraphy\workbooks\[data201712.xlsm]Master'!#REF!</xm:f>
                    </x14:formula1>
                    <xm:sqref>B184:C184</xm:sqref>
                </x14:dataValidation>
                <x14:dataValidation type="list" showInputMessage="1" showErrorMessage="1">
                    <x14:formula1>
                        <xm:f>Master!$D:$D</xm:f>
                    </x14:formula1>
                    <xm:sqref>B2:C183 B185:C1048576</xm:sqref>
                </x14:dataValidation>
            </x14:dataValidations>
        </ext>
    </extLst>
    ....

(ちなみに、見やすくするためにVisual studio codeのxmltoolでフォーマットした)


uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"というのは、dataValidationsのことらしく、
まあ、sqrefの値が示すのが列範囲であることをみても、これは「入力規則」の定義であろう。


sqrefをみると、B2:C183、B185:C1048576の範囲で自ブックのMasterシートのD列を参照しているのに、B184:C184の1行分だけ、他ブックをみているようだ。(しかもエラーになってる)


ここに他ブックから入力規則ごとコピーしてきてしまったデータがあるようだ。


場所が特定できれば、元のxlsmを開いて修正するのは容易なことである。

結論

今日の教訓

  • リンクエラーが発生した場合、*.xlsx, *.xlsmならzip展開してgrepすると、場所を特定しやすい。
  • セルの値、オブジェクト、名前定義だけでなく、条件付き書式や、入力規則にも外部リンクが入りえるので要注意である。

関連

探してみると、同じことを指摘されている方が何名もおられました。FAQなのかな。

以上、メモ終了