MrExcel Publishing
Your One Stop for Excel Tips & Solutions

automatically insert filename into cell


Posted by Kevin on November 20, 2001 11:42 AM

How can I automatically insert the name of the spreadsheet file into a cell, and have the contents of cell change as the filename changes, for example if the file is renamed, the cell that contains the filename will also change to the new filename.

Thanks,
Kevin


Posted by giacomo on November 20, 2001 12:04 PM

=CELL("filename")

This gives you the full path if you want to get the filename only you'll have to use the MID() function

Posted by Kevin on November 20, 2001 12:28 PM

Re: =CELL("filename")

I tried this, and it does give me the filename. However, if I save the file as a different name, the cell contents do not change. I need them to change as the file name also changes. Any ideas??

Thanks,
Kevin

Posted by giacomo on November 20, 2001 1:20 PM

Re: =CELL("filename")

it will update when a calculation is performed, you can force a calculation by pressing F9. I realize that's not as automatic as you had hoped. Maybe someone oout there has a good macro for you...

Posted by Joe Was on November 20, 2001 2:20 PM

Macro

You can run this macro or attach it to a save macro. It will update the file name everytime the macro is run. You can improve the commented out save code with a Input Box for the "Drive:\Path\FileName.xls." If you need help with the new code let me know. JSW

Sub mySetFileName()
'
Range("H1").Select
With Selection
.HorizontalAlignment = xlRight
End With
With Selection.Font
.Name = "Arial"
.Size = 8
End With
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Range("H1").Select
Calculate

'This is the optional save code. As it is it will 'only save to the hard coded "Filename."
'ChDir "C:\cp"
'ActiveWorkbook.SaveAs Filename:="C:\cp\Test_File.xls", FileFormat:=xlNormal _
', Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
'CreateBackup:=False
End Sub
'JSW

Posted by Kevin on November 21, 2001 7:03 AM

Here's what I did - thanks

Giacomo,

I continued working with your formula, and made the following additions. The path and filname of my files are as follows:

s:\carroll\tally sheets\2002\02-2002 Tally Sheets\Tally 02-01-02.xls

I used the date formula, and I pulled the year, month, and day values for the date formula from the text in my filename and converted them to values with following formula:

=date(value(mid(cell("filename"),64,2)),value(mid(cell("filename"),58,2)),value(mid(cell("filename"),61,2)))

Then I added a macro that runs automatically when the spreadsheet is opened, and all the macro does is calculate the spreadsheet, to keep my date number updated.

This way, my number is always up-to-date when I rename the file or save it under a different name, and it is also a number in date format instead of text.

Couldn't have gotten this far without you getting me started - thanks!

Kevin

Posted by chris on November 23, 2001 4:17 AM

Re: Here's what I did - thanks

Kevin....

I use =cell("filename",a1)

this seems to anchor it and update it whenever it is saved