How to Save with automatic filename plus today's date

[Gary Drumm]

Is there a way to save a file and have it automatically put today's date in the file name?

Example: original File name = test.xls
desired file name = test072807.xls, or test.072807.xls, or test.07 28 07.xls

So, I open the file, do whatever, and then click save.
When I click save, it does one of the above, given that today is 7 28 07.

I tried to search for this topic, but search wasn't working well on my PC today.

Thanks,
Gary

 

[Gary Drumm]

Bump

 

[joefrench]

Place the following in the Workbook Object in VBE. Alter according to your needs:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strDate As String
strDate = Format(Now, " dd-mmm-yy")

ActiveWorkbook.SaveAs ("c:\test " & strDate & ".xls")

End Sub

Will create file as follows: test 28-Jul-07.xls

 

[Gary Drumm]

Joe,

With a couple of minor alterations, it's working great.

Thanks,
This is a keeper.

Gary

 

[Gary Drumm]

Well, it's working, but not great?

It does save the file with a new file name + date, but as soon as it's finished, it causes Excel to crash, and offers two options, shut down, or shut down and notify Microsoft. When I clicked on datails, it listed appcrash, then excel.exe, then several other things in descending order.

I can live with it I suppose, but would prefer a seamless operation.

I'm using Office XP, on a Vista Home machine.

Any ideas would be appreciated.

Thanks,
Gary

 

[joefrench]

I'm assuming either Excel 2003 or later is being used? Could you post the code?

 

[Gary Drumm]

Joe,
Here is what you provided, plus my minor modifications.
I have Small Office XP, and Windows Vista.
Thanks,
Gary

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strDate As String
strDate = Format(Now, "mm-dd-yy")

ActiveWorkbook.SaveAs ("c:\My Documents\Blood Sugar\Blood Sugar " & strDate & ".xls")

End Sub

 

[Gary Drumm]

bump

 

[joefrench]

So It's saving the file? Just happens to be crashing as well?

 

[Gary Drumm]

Joe,
Yes, that's it exactly.
It gives me two options with the new file.
1. Close and go online to look for a solution
2. Close

I tried option 1, and it went to MS site to look for an update.

Thanks,
Gary