Display excel file name 2

[jnix]

How can I display the excel file name in a cell of a worksheet in that file?

 

[DaleWatson123321]

jnix,

There are probably other options, but these formulas will work. The following provides you with two options 1) without the ".xls" suffix, and 2) with the suffix.

enter in A3: =CELL("filename",A2)
enter in A4: =FIND(".xls",A3,1)
enter in A5: =FIND("[",A3,1)+1
enter in A6: =MID(A3,A5,(A4-A5))
enter in A7: =MID(A3,A5,(A4-A5)+4)

Hope this is what you wanted.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[euskadi]

Or you can do this:

Function MYWKBNAME(rng As Range)
MYWKBNAME = rng.Parent.Parent.Name
End Function

Then in any cell, enter :

=MYWKBNAME($a$1)

 

[HoosierDaddy]

Here's another:
=CELL("filename&quot

Yep, enter it just as above.

 

[DaleWatson123321]

Hoosier,

Thanks for your contribution. The more contributions - and DIFFERENT approaches and ideas - the better.

HOWEVER, the problem presented with your proposed solution is that is produce MUCH more than what was asked for.

Specifically, it produces:
1) the path to where the file is located.
2) the filename - with (probably) unwanted brackets... [ ]
3) the Sheet name.

That is why I proposed the formulas shown my previous posting.

I hope you understand, and appreciate the difference.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[HoosierDaddy]

Dale,

Of course I understand. I'm used to working with end-users on a daily basis so I try to keep as simple as possible. I've got a long one that shows just the sheet name, too.

Always interested in what others do, and I certainly will keep your handy for future reference.

Thanks! Tom aka HoosierDaddy