Retrieving current file name

[Penfold]

I'm not sure whether I'm having a mental blank or not. Is there a simple formula that lists the current excel file you're in? I know that you can use INFO("directory") to get the path name, but what about the file name?

Thanks.

Penfold

 

[Dave Hawley]

Hi Penfold

I have a couple of UDF's here that may suit you:
http://www.ozgrid.com/VBA/WorkbookPath.htm

 

[Penfold]

Thanks Dave, I was hoping that Excel would already have a built in way of doing it, but it appears not. I'll have to use the User Defined Function instead.

Nice website btw. I just discovered I've been using dynamic ranges without even knowing that I've been doing it. Ah OFFSET - hell to learn but mighty powerful once you understand it!

Cheers,

Penfold

 

[Dave Hawley]

Hi Penfold

There is a nested formula that does this it's:

=MID(CELL("fileName"),FIND("[",CELL("fileName"))+1,FIND("]",CELL("fileName"))-FIND("[",CELL("fileName"))-1)

 

[Penfold]

OK, now have the path & file name working ok via the User Defined Function, however the **** thing is not updating even when pressing recalc (F9). How do you get it to automatically recalculate the function when opening the file?

 

[Dave Hawley]

Put:

Application.Volatile

as the first line

 

[Legacy 344]

Here's another way.

Define a Name called WB and type in the RefersTo box :-
=GET.DOCUMENT(88)

Typing =WB in any cell will return the workbook name.

 

[Penfold]

Ah, thanks Dave.

Penfold