Cell equals workbook name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how to make a cell reference just the filename of the workbook it is located in, as well as the filenames of other workbooks that I dictate. If the workbook name with address is "C:\Files\Folder\Mementos.xls", then I want the cell to display "Mementos". For reference sake, assume that the cell containing the formula is "A1".

Please help...
 
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) where 32 is
the max length of the sheetname and A1 is just a reference to any cell
in that sheet.
 
There may be a simpler way, but the following should do the trick:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,(FIND(".",CELL("fil
ename",
A1)))-(FIND("[",CELL("filename",A1))+1))

--
HTH,
Laura Cook
Appleton, WI


LupusManTM said:
I need to know how to make a cell reference just the filename of the
Click to expand...
workbook it is located in, as well as the filenames of other workbooks that
I dictate. If the workbook name with address is
"C:\Files\Folder\Mementos.xls", then I want the cell to display "Mementos".
For reference sake, assume that the cell containing the formula is "A1".
 
If you want to use a VBA proceedure, this might work a little easier.

Sub FileNamer()
Dim Filenames As String
Dim filelength
Filenames = ThisWorkbook.Name
filelength = Len(Filenames)
Filenames = Left(Filenames, (filelength - 4))
[A1] = Filenames
End Sub

I had this as a button, but it could also be in a workbook code, maybe
when selected, or activated, or whatever you need.

Hope it helps.

-Bob
 
Thanks, Laur

Your reply was rather helpful. It worked without worry, but as I was typing it into my worksheet, I noticed that your formula has what I consider a "stipulation" of sorts. When the formula finds the characters "[" and "." , it will work so long as the subfolders in the directory and the filename (minus the extension) exclude those characters. If the directory was something like "C:\Sports\Football[2002]\Rams.Preseason.xls" the formula will return the result "2002]\Rams" . This is probably not what the formula is needed to retrieve

I'm not entirely sure how to adjust the formula to avoid this situation indefinitely, but based on what you submitted, I managed to protect my project against any problems regarding just the "." character

This is the formula I am using
=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1))+1,(FIND(".",CELL("filename",$A$1)))-(FIND("[",CELL("filename",$A$1))+1)

I hope this opens up a few doors for not only you and me, but for any and all users who may find themselves in the same situation I was in before I posted today. It is, by no means, a simple or entirely fool-proof formula, but so long as "[" and ".xls" are not used in the subfolders or filename (minus the extension), it does the trick nicely

Thanks again for your help
Jeremy
 
Just to mention about the VBA code I wrote (not trying to disparage an
other, just mentioning).

What the VBA code does is take the filename only (Thisworkbook.Name
and then truncates the last 4 characters from the set. Since the fil
will almost invariably end in .xls (4 characters) this will retur
whatever filename you were looking for. But, then, it might not be
solution you require, as it is in VBA.

-Bo
 
Back
Top