File Name in Spreadsheet

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #2

See the Excel Help topic for the CELL worksheet function.

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Sep 19, 2003

• #3

For File Name with Path & Sheet Name:

=CELL("FILENAME")

For full File Name with Path:

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

For File Name only;

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

Hope that helps,

Smitty

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Sep 19, 2003

• #4

Hi smitty:

=cell("filename")
in a cell will display the name of the active file

to ensure that the cell A1 displays the name of the file to which the cell and its worksheet belong to, I will use

=cell("filename",A1)
to anchor the name of the corresponding file in cell A1.

(I did see that further down in your post you did anchor the filename in cell A1)

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Sep 19, 2003

• #5

And for the name of the workbook only,

the following worked for me ...

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

where A1 contains =cell("filename",A1)

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Sep 20, 2003

• #6

Thanks Yogi,

You always catch the schnznit that I miss!

Smitty

 

Upvote 0

T

Travis

Well-known Member

Joined

Feb 26, 2002

Messages

1,711

• Sep 20, 2003

• #7

If you dont want it in a cell, this will print it in the footer whenever you print the file...

Code:

Private Sub Workbook_BeforePrint (Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "&8" & UCase (ActiveWorkbook.FullName)
End Sub

 

Upvote 0