Filename

[mg02554]

I can not find how to display the name of the current file in a
worksheet cell.

 

[Peo Sjoblom]

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

 

[Harlan Grove]

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),
FIND(".",MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

Which requires that the filename have only one period in it - the one just
before the xls extension. Usually a safe assumption. Other quibble is that
this makes lots of CELL calls.

If this is frequently needed, it may make more sense to use defined names,
and store them as part of the default workbook template.


_CFN_
=CELL("Filename",INDIRECT("A1"))

_Seq_
=ROW(INDIRECT("1:1024"))

_Path_
=LEFT(_CFN_,MAX(IF(MID(_CFN_,_Seq_,1)="\",_Seq_))-1)

_Filename_
=MID(_CFN_,LEN(_Path_)+3,FIND("]",_CFN_,LEN(_Path_)+2)-LEN(_Path_)-3)

_BaseFilename_
=LEFT(_Filename_,LEN(_Filename_)-IF(RIGHT(_Filename_,4)=".xls",4,0))

_WSname_
=MID(_CFN_,LEN(_Path_)+LEN(_Filename_)+4,32)


The peculiar definition of _Path_ is necessary to guard against Excel files
stored in directories that contain square brackets in their names (which
Windows allows, so Excel must accomodate).

This also makes a lot of implicit CELL calls. This is one sort of task for
which user-defined functions would be more appropriate.

Function pathname() As String
pathname = Application.Caller.Parent.Parent.Path
End Function

Function filename() As String
filename = Application.Caller.Parent.Parent.Name
End Function

Function wsname() As String
wsname = Application.Caller.Parent.Name
End Function

These have the further, perhaps minimal, advantage of working before the
file has been saved.