excelでファイル名を自動入力する方法

質問

excelでファイル名をセルに自動入力する方法を探しています。

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5)

上記の方法で自動入力可能なのですが、あとから同時に他のファイルを開いたりすると、そのファイル名がセルに入力されてしまい、困っています。

複数ファイルを同時に開いても、当該ファイル名を入力するようにはどうしたらよいでしょうか？

【詳細】
ファイル１.xlsのセルA1に上記式をセットしてある状態でファイル1.xlsを開くと、セルA1には「ファイル1」と入力されているが、あとからファイル2.xlsを開くと、セルの内容が「ファイル2」に変わってしまう。

回答No.1

merlionXX · ベストアンサー

ユーザー定義関数をつくったらどうでしょうか？

以下の手順をおためしください。

1.Altキー＋F11キーで Visual Basic Editor画面を呼び出します。

2.画面上部のメニューバーから挿入、標準モジュールで出てきたコードウィンド（右側の白い広い部分）に以下のコードをコピペします。

'*****ここから下をコピペ*****

Function myFile()
myFile = ThisWorkbook.Name
myFile = Left(myFile, InStrRev(myFile, ".") - 1)
End Function

'*****ここより上までをコピペ*****

3.Alt+F11キーでワークシートへもどります。

4.お好きなセルに、

=myFile()

と入力すればBOOK名を表示するはずです。

お礼

merlionXXさん

ご回答ありがとうございました。
とてもわかりやすく、感動しました！
お教えいただいたやり方で、基本的にばっちりだったのですが、
実はこのファイルはコピーして複数の人で使い回す予定なのですが、
新しくコピーしていったん開いたあとで、セルを更新しないとファイル名が反映されないらしく、
ファイルを開いた時点で更新されるようにするには
どうしたらいいか、
今調査中です。

回答No.5

merlionXX

> 上書き保存したのですが、

上書き保存だけでは反映しません。
保存して終了後、再度開けば反映しているはずです。
閉じずに上書き保存だけでも反映させたければ保存時に再計算指示をすればいいと思います。
ThisWorkbookモジュールに
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Calculate
End Sub
でどうでしょう？

お礼

merlionXXさん

すみません、ご返信遅くなりました。

VBAを仕込んだテンプレートをコピーして、再度開いただけではファイル名は更新されなかった（セルを更新すればOK）のですが、
おっしゃる通り、再計算指示、

ThisWorkbookモジュールに・・

を仕込んだところ、コピーしたファイルを開いただけで、ファイル名が反映されるようになりました。

いろいろとありがとうございました。
大変参考になりました。

回答No.4

merlionXX

merlionXXです。

No4で回答したコードはまさにそこを解消したものなのですが・・・。
名前を付けて保存し、開けば自動的に新しいファイル名に変わるはずですが。

お礼

merlionXXさん

私もソースをみて、そうかな？と思ったのですが、
教えていただいたソースをまるまるコピって先ほどのものに
上書き保存したのですが、
やはりいったんセルを更新しないと、
更新されないようです。

もしかして私のやり方が悪いのかもしれません＾＾；

回答No.3

merlionXX

No1で回答したコードを少し変更させてください。

Function myFile()
Application.Volatile '自動再計算
myFile = ThisWorkbook.Name 'BOOK名取得
If InStr(myFile, ".") > 0 Then '拡張子がある場合
myFile = Left(myFile, InStrRev(myFile, ".") - 1) '拡張子を削除
End If
End Function

お礼

merlionXXさん

お返事行き違いになったようですね。
こちらもさっそく試させていただきました。
ありがとうございます。

さっきのファイルを開いた時点での自動反映は、
Workbook_Open()
か、
Auto_Open()
にお教えいただいた関数を組み込めばいいのかな？
というかんじです。

回答No.2

fujillin

CELL関数のヘルプを見ると
　CELL(検査の種類,対象範囲)　とようになっており、「"filename"：対象範囲を含むファイルの名前(絶対パス名) を返す」となっています。
対象範囲を省略しているので、自動的にアクティブシートのセルが取られているのでは？

試しに、
　=CELL("filename",A1)
のように範囲指定を明示してあげると、他のブックを開いても値が変わることはありませんでした。

お礼

fujillinさん

ご回答ありがとうございました。
さっそく試してみたところ、おっしゃる通りで、
対象範囲を指定したことで、見事問題解消されました！

基本的な大事なところを見逃していたようですね。
どんぴしゃりなご指摘に感動です。