File Name as Cell Contents - Without File Extention

[eilersca]

Can you put the file name in a certain cell.

For Example:
File Name = C:My DocumentsContents.xls

In the contents.xls file, I would like cell D4 to equal the file name ("contents") without the full path and without the file extension. Same way that it can be display as a header.

lenze:

Your code:
Range("$A$1").Value=ActiveWorkbook.Name works, but Is there a way to remove the file extention (".xls") also?

Thanks for your help.

Chad

[ This Message was edited by: eilersca on 2002-05-07 09:01 ]

[Ricky Morris]

Try this:

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

[Tom Schreiner]

I knew I could find it:

Here:

This one returns the full path...

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

This one returns just the filename...

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

Tom

_________________
Found a solution? If so, please post again so members of this board can spend their time helping others. Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved". Thanks for being courteous!

[ This Message was edited by: TsTom on 2002-05-07 08:27 ]

[lenze]

Use this code to place the name in a cell. Place code in the ThisWorkbook module

Private Sub Workbook_Open()
Range("$D$4").Value=ActiveWorkBook.Name
End Sub

This will place the file name in D4

Use this code for the header or footer

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.Name
End Sub

Replace Name with FullName to include the path

[dwhj]

Here's another way.

Define a Name (let's call it wb) and type in the RefersTo box =GET.DOCUMENT(88)

Then to return the workbook name without the extension, type in any worksheet cell
=SUBSTITUTE(wb,".xls","")

[Yogi Anand]

Here is one more ...

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

regards!