Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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


Check out our Excel VBA Resources

=CELL("filename")

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

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


Re: =CELL("filename")

Posted by Kevin on November 20, 2001 12:28 PM
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


Re: =CELL("filename")

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

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


Macro

Posted by Joe Was on November 20, 2001 2:20 PM
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


Here's what I did - thanks

Posted by Kevin on November 21, 2001 7:03 AM
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


Re: Here's what I did - thanks

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

I use =cell("filename",a1)

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.