Inserting the file path in a cell

N

netorius77

I am using Excel 2003. I know how to insert the full path and file name in a
footer, however, is there any way (possibly a function) to display the full
path and file name in a cell?
 
D

Dave Peterson

You may want to use:

=cell("filename",a1)

If you have multiple workbooks open, then this formula will evaluate to the
activeworkbook.

If you try it with two workbooks open (and windows arranged nicely), you'll see
the problem.
 
B

balterson

So what would be the formula if you want JUST the file name, and not the
whole path???
 
D

Dave Peterson

And by filename, you been something like: Book 2.xls
not c:\my documents\excel\book 2.xls
right???

If yes:

=CELL("Filename",A1)
will give you something like:
C:\My Documents\Excel\[book 2.xls]My Sheet Name

So you can use:
=FIND("[",CELL("Filename",A1))
to find the position of the [ character

And
=FIND("]",CELL("Filename",A1))
to find the position of the ] character

And we want the mid portion of that long string starting one character to the
right of [ character for a length of whatever's between the []'s (subtract the
position of the ] from the position of the [ and subtract 1.

Putting it all together in a giant formula:

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

Remember that the file still needs to be saved at least once for this to work.
 
Top