automatically insert filename into cell

=CELL("filename")

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")

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")

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

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

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

Kevin....

I use =cell("filename",a1)

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