Macro to File/Save as "Numbers as of [today's date]" on desktop

**duugg** · 02-17-2009, 04:03 PM

Hi All,

I'd like a macro to have the workbook save as

Numbers as of "today's Date"

and then close that workbook.

I already tried the following...

Sub File_SaveAs()
'
' File_SaveAs Macro
' Macro recorded 2/17/2009 by ...

    ActiveWorkbook.SaveAs Filename:= _
        "D:\Documents and Settings\Smith\Desktop\Numbers as of.xls" & Format(Date, "mm-dd-yy")
        FileFormat:=xlNormal, Password:="", WriteResPassword:="",
        ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

I got a Compile and Syntax error.

Thanks

**dustycrockett** · 02-17-2009, 04:48 PM

try using this for your file name, instead:

...
"D:\Documents and Settings\Smith\Desktop\Numbers as of " & Format(Date, "mm-dd-yy")
 & ".xls" 
...

**Mallycat** · 02-17-2009, 05:11 PM

I think you also need a comma after the file name and before FileFormat=....

**duugg** · 02-17-2009, 06:17 PM

I'm lost on this one. I want to do 2 things here

1. Replace "today's date with the current date, I know that the "DATE" function needs to be used, but I also know that if a comma or () or whatever other character I need is just 1 off, it won't work. Can anyone tell me exactly is wrong here?

2. How do you record closing a workbook with a macro when there's no way to click on "stop" to stop the macro after it's been closed? What is the exact code for this and where do I put it in the relation to the "File/Save As" macro?

Thanks much

Sub File_SaveAs()
'
' File_SaveAs Macro
' Macro recorded 2/17/2009 by ...
'

'
    ActiveWorkbook.SaveAs Filename:= _
        "D:\Documents and Settings\Smith\Desktop\Numbers as of today's date.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

**dustycrockett** · 02-17-2009, 07:36 PM

"Date" by itself gives you today's date (assuming your computer clock is working).

    ActiveWorkbook.SaveAs Filename:= _
"D:\Documents and Settings\Smith\Desktop\Numbers as of " _
& Format(Date, "mm-dd-yy") _
& ".xls"

this is where you missed the comma in the first version, right before "FileFormat" -- but notice that you don't really need those other options, unless when you opened it, the file was password-protected and read-only and some other format (.txt or .csv, for example).

If you're using the "Record Macro" button on the toolbar, it's still there after you close a file. When you start recording, specify "New workbook" in the "Store macro in" drop-down box. Then you can close your active workbook and the macro will still be there.