file name and path in worksheet

[april]

is there a way to insert the file name and path in a worksheet - i'm not
talking about the footer in the print setup. i am looking for something like
the NOW function.

thanks

 

[Elisabeth D]

Enter this formula in the cell where you want the path and filename to appear:

=CELL("filename",A1)

Note: This formula will not produce a result until you save the file.

Elisabeth

 

[T. Valko]

The file *must* have been saved at least once before these will work.

For the file name:

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

The reference to cell A1 can be *any* cell reference on that sheet.

For the path which includes the file name:

=LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)))

For the path excluding the file name:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

Note that "filename" is the argument and not a placeholder. Some folks see
"filename" and think they need to replace that with the actual file name! Do
not replace "filename".

 

[ShaneDevenshire]

Hi,

You can simplify your use of the CELL function, by removing the second
argument, so the longest of the suggested formulas becomes:

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

And since we call the CELL("filename") funtion four times a range name like
F would simplify this to

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

You can make a name out of the function by choose Insert, Name, Define and
entering F in the Names in workbook box and =CELL("filename") in the Refers
to box.

 

[T. Valko]

You can simplify your use of the CELL function, by removing the second

argument

As long as you don't have multiple files open at the same time. Using A1 as
the 2nd argument "anchors" the formula to the file the formula is entered
in.

Try it without the 2nd argument then open another file.

--
Biff
Microsoft Excel MVP

Hi,

You can simplify your use of the CELL function, by removing the second
argument, so the longest of the suggested formulas becomes:

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

And since we call the CELL("filename") funtion four times a range name
like
F would simplify this to

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

You can make a name out of the function by choose Insert, Name, Define and
entering F in the Names in workbook box and =CELL("filename") in the
Refers
to box.

--
Thanks,
Shane Devenshire

is there a way to insert the file name and path in a worksheet - i'm not
talking about the footer in the print setup. i am looking for something
like
the NOW function.

thanks

 

[Roger Govier]

Hi April

You could use the following Function
Function Fullpath()
Fullpath = ThisWorkbook.FullName
End Function

Usage =Fullpath() typed in any cell, will return the name and path into the
cell where it is entered
If the file has not been saved, then it will return just the filename e.g
Book1

If you save the function in Personal.xls it will always be available to you
in any workbook by typing
=Personal.xls!Fullpath()

Alternatively, to avoid typing that each time, you could create a defined
name in your current workbook
Insert>name>Define Name fpath Refers to =Personal.xls!Fullpath()
and just use
=fpath

If you already have a Personal.xls set up, copy the code and paste into a
module in the file.
If you don't have a Personal.xls, then
Open a new workbook
Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel
Save as Personal.xls in your XLSTART folder