|Check out our Excel VBA Resources|
This gives you the full path if you want to get the filename only you'll have to use the MID() function
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...
.HorizontalAlignment = xlRight
.Name = "Arial"
.Size = 8
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
'This is the optional save code. As it is it will 'only save to the hard coded "Filename."
'ActiveWorkbook.SaveAs Filename:="C:\cp\Test_File.xls", FileFormat:=xlNormal _
', Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
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:
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!
I use =cell("filename",a1)
this seems to anchor it and update it whenever it is saved