Excel関数でファイル名を取得したい！と思ったことってありません？今回はそれを実現する方法を解説します。
ちょっとした落とし穴もあるので、確認してみてください！
まずはフルパスを取得する必要がある。
Cell関数を使おう
ファイル名を取得するのはVBAを使わなくてもExcel関数でちゃんとできます。
ただ、一発では取得できないんです。まずはファイルのフルパスを取得することになります。
Cell(“filename”)やろ
そうです。Excelの任意のセルで
=Cell(“filename”)
と書くとファイルのフルパスを取得できます。
【補足】
Cell関数は第一引数に特定の文字を入れることで様々な情報を取得することができます。
- “col”だと対象範囲の左上隅にあるセルの列番号。
- “row”だと対象範囲の左上隅にあるセルの行番号。
他にもいろいろ取得できる情報がありますが、詳しくはExcelのHELPを見てくださいね。
実際の結果はどうなるか
実際の結果を確認しましょう。
フォルダ構成は以下の通りです。
——-
D:
└ExcelTest
└─test1.xlsx
——-
任意のセルに Cell(“filename”) を入れて、Enter
見事にフルパスがGetできました！
あとはここからファイル名を取るだけなのですが・・・
実はここが落とし穴
うまくできているように思うのですが、もう１つ同じような内容でファイルを作ってみます。
先ほどのフォルダ構成でもう1つファイル(test2.xlsx)を追加します。
——-
D:
└ExcelTest
├─test1.xlsx
└─test2.xlsx
——-
先ほどと同じ要領でCell(“filename”)を記入します。
一緒やんけ！
ま、まぁまぁ、落ち着いて・・・。
この状態でさっきのファイルを開けてみましょう。
この test2.xlsx を開いた状態で最初に作った test1.xlsx を開いてみましょう。すると・・・
なんでどっちも同じになっとるんや！！！！
あら不思議。どちらもtest1.xlsxのフルパスを取得しています。
再度test2.xlsxを閉じて開くと今度はどちらもtest2.xlsxになります。
この現象がなぜ起こるのか。
これはCell関数の第二引数を省略していることで起こる現象だと推測しています。
Cell関数の第二引数の説明は以下の通りとなっています。
情報が必要なセルを指定します。 省略すると、info_type 引数で指定された情報が、変更された最後のセルに対して返されます。 対象範囲がセル範囲の場合、その範囲の左上隅のセルに関する情報だけが返されます。
Microsoft サポートサイトより引用
補足ですが、info_type は第一引数のことを指しています。
要するに、第二引数を省略すると「変更された最後のセル」に対しての情報が返却される。ということなので、この例の場合は、test1.xlsxでCell(“filename”)が有効になると、test1.xlsxで「変更された最後のセル」に対しての情報、つまりtest1.xlsxのフルパスが返ってくることになる。 この情報が別のファイルtest2.xlsxにも渡されることになり、test2.xlsxでも test1.xlsx のフルパスが返ってくるということだと考えられる。
【注】
全てのバージョンで試してはいませんが、Excelのバージョン2003では出ませんでした。
2016、2019ではこの現象を確認しています。ほかのバージョンは確認していませんのであしからず…。
ではどうすればこれを回避できるかというと
第二引数にちゃんとした値を入れたらええんちゃうか
そうです。第二引数を省略しない。が正解です。
第二引数にはそのExcelファイルのどこでもいいので、セルを指定してあげればいいのです。
A1でもB2でも範囲指定でもなんでもいいので、そのファイルのセルを指定してあげれば、そのファイルの 変更された最後のセル」はそのファイル自身の情報になるので、常に自分のファイルのフルパスが返ってきます。
では
test1.xlsx：Cell(“filename”)⇒ Cell(“filename”,A1)
test2.xlsx：Cell(“filename”)⇒ Cell(“filename”,A1)
にそれぞれ変更してみましょう。
それぞれのファイルでフルパスが取れてるで！
あとはファイル名だけ抜き出す
これで複数ファイルにCell(“filename”)を書いても問題が生じなくなったので、あとはファイル名だけを抜き出すだけです。
フルパスからファイル名のところだけ抜き出すには、”[” から “]” の間の文字列を抜き出せばOKです。
文字列の抜き出しはMID関数を使います。
MID(対象文字列, 検索開始位置, 抜き出す文字数)
なので
| 対象文字列 | CELL(“filename”,A1) | 検索対象はフルパス |
| 検索開始位置 | FIND(“[“,CELL(“filename”,A1))+1 | 開始位置は”[“の次 |
| 抜き出す文字数 | FIND(“]”,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1) | 文字数は”]”までの位置から”[“の位置の差から1を引いた数 |
ということで上記をMID関数にはめ込むと
=MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND(“]”,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1)
ということになります。これでファイル名は取得完了です。
今回はこれで以上です。少し長くなりましたが、お役に立てると嬉しいです。
わたしもちなみにこの罠に引っかかって？？？になった一人です笑
これで複数のExcelファイルでもファイル名取得はばっちりやで～
