Filename

  • Thread starter Thread starter mg02554
  • Start date Start date
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)
 
Peo Sjoblom said:
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)
Click to expand...

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.
 
Back
Top