リンクの設定等があるExcelファイルの場合、保存場所の変更に注意が必要です。特に、VBAやPower Queryなどで固定のファイルパスに依存した構築を行うと、Excelに不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。
ファイルの保存場所やファイル名が変わる可能性が高い場合は、事前に保存場所変更に備えた構築が必要です。
そのため、Excelファイル自体の移動があっても、データソース等との相対的な位置関係で動作できるようにしておくことも有効です。
相対的な位置関係の基準になる場所は、Excelファイル自体の保存場所にすると便利なケースが多いので、Excelのシートに自動更新で保存場所情報を表示する方法の備忘録。
以下は、「標準の数式」と「ユーザー定義関数（VBAのFunctionプロシージャ）」を使用して保存場所（フォルダ・フルパス等）を表示する方法のサンプル。
ユーザー定義関数に不慣れな人が多い組織では標準の数式の方が良い印象。
標準の数式での方法
現在のフォルダ：
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
表示例）C:\Users\yctest\Desktop\
現在のフルパス：
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
CELL関数の補足説明
CELL関数の詳細は以下のとおり。
CELL(“filename”,A1)で、A1は数式を入力するセル以外のセル場所であれば、どこでも可。
なお、CELL(“filename”)も同様に可能だが、動作安定のためA1といったセル場所を指定する。セル場所を指定しない場合は、アクティブなブックのファイル情報となるため、複数のファイルを開いている場合は意図しない結果となる可能性がある。
また、CELL(“filename”,A1)で、当該ファイルのフォルダ名、ファイル名、sheet名が次のように取得できる。
例）C:\Users\aaa\Desktop\[売上データの統合.xlsm]設定
当該ファイルのsheet名は、“]”の後に記載される。この事例では、 sheet名は“設定”
ユーザー定義関数での方法
VBAで以下のとおり記載。
Function 現在パス() As String 現在パス = ThisWorkbook.Path End Function Function ファイル名() As String ファイル名 = ThisWorkbook.Name End Function
セルは以下とする。
現在のフォルダ：
=現在パス()
現在のフルパス：
=現在パス()&"\"&ファイル名()
保存場所情報から相対的な位置関係を示す方法
Excelファイル自体の保存場所の情報から、下位の階層の場合は数式で”&”などを使って、文字列を連結する。
階層が上位や同列の場合は、 SUBSTITUTE関数を使用して上位フォルダを削除するなどする。
例：セルB2にフォルダ情報があり、Desktopをデータフォルダに置換の場合
=SUBSTITUTE(B2,“Desktop”,“データフォルダ”)
保存場所に関する補足
この方法は、OneDriveを利用している場合、ファイルパスがURLとなるため、そのままでは利用できない。
その場合は次のような文字列の置換処理や直接フルパスを入力などの対応が必要。
以上、Excelのシートに自動更新で保存場所情報を表示する方法の備忘録でした。
なお、powerqueryのデータソースの保存場所変更については、以下に対応例をまとめてみたので、ご覧ください。
