path and filename display in title bar - 2007

[Joan]

Can I show the full path and filename in the title bar of a workbook in Excel
2007? - I know I can add it to the QAT, but this is not what I want.

Thanks

Joan

 

[ShaneDevenshire]

Hi Joan,

You can use the following code:

Sub myPath()
CapLen = Len(ActiveWorkbook.Name)
FNLen = Len(ActiveWorkbook.FullName)
ActiveWorkbook.Application.Caption = Left(ActiveWorkbook.FullName, FNLen
- CapLen)
End Sub

 

[Holly Dean]

Hi, I got your solution for putting path in file name...

worked great! thanks!

trouble is, it stays there when I open another file. (goes away when I close excel)

what would the code be to clear out the caption when close

AND - what would be the best way to have this work for all workbooks I open?

thanks!