SaveCopy with DateStamp

[sir.wiggles]

Quite a simple one here i think.
I would like to be able to save a copy of the workbook, however the tricky part is it needs to be named with whatever the date is.
so, if someone was to execute the sub today, it would be saved as "14 February 2008.xls"

So far i have this..

Sub Save()
ActiveWorkbook.SaveCopyAs ".xls"
End Sub

Thanks in advance!

 

[chergh]

Essentially it will be:

activeworkbook.savecopyas date() & ".xls"

but you will need to use the format function with the "date()" function to get the format you want. Use the help in the VBE to find how to use the format function.

 

[jon jomaco]

Is there a way to use code similar to this in access, all i can find is FileCopy but i don't think that lets you save the database you have open and in any case it doesn't function like the savecopyas code.

When i made a project excel i used the code below which might be useful to the original poster:

Dim Pathx As String
Dim FileNamex As String
Dim FPathFName As String

Pathx = ActiveWorkbook.Path
FileNamex = Cells(105, 2).Text & " Backup.xls"

FPathFName = Pathx & "\Backups\" & FileNamex

On Error GoTo ErrorTrue

ActiveWorkbook.SaveCopyAs FileName:=FPathFName

'ErrorFalse
MsgBox ("Backup saved. Check the backup folder of the spreadsheet directory")
End

ErrorTrue:
MsgBox ("The directory is full. Please delete old backup files to make space for new ones")

Terminate:
End

Obviously this won't work in access but perhaps there is a similar function which i can use?
In addition I tried the FileCopy code but it says permission denied. When searching in Microsoft Access help it says it throws up an error if you try to save a currently open file, so this clearly isn't the code i'm looking for.

Any help appreciated. Thanks,
Jon.

 

[ghudson]

Is there a way to use code similar to this in access, all i can find is FileCopy but i don't think that lets you save the database you have open and in any case it doesn't function like the savecopyas code.

Check out my old BackupAndZipit() function I posted in the Back up On exit..H..E..L..P! thread on how to copy an open database.

 

[jon jomaco]

Ah what a relief, fantastic that's just what i was looking for. Just a little edit, took out the zip function and it's perfect.

Can't thank you enough, +REP
Jon.