Excel - Save as vba excel

Asked By lena_form on 28-Apr-10 04:19 AM
Hello,
How can I create a code in VBA, in Excel 2007, for when closing a book, save
it automatically with a new name (the system date and time)?
Thanks
--
HC




ozgrid.com replied to lena_form on 28-Apr-10 04:25 AM
In the This Workbook Module;
ozgrid.com replied to lena_form on 28-Apr-10 04:26 AM
In the ThisWorkbook Module;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.SaveAs Me.Path & "\" & Format(Now, "hh:mm-mm-dd-yyyy")
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
Helmut Meukel replied to ozgrid.com on 28-Apr-10 05:40 AM
Hmm,

I would not use this formatting, because
the default sorting order is by name, which would sort the files like
06:35-04-27-2010
09:17-04-20-2010
09:25-01-01-2009
10:12-04-27-2010

I would use Format(Now, "yyyy-mm-dd-hh:nn:ss")
BTW, using "mm" for both minutes and months is ambiguous
and may cause problems. "nn" is always interpreted as minutes.

HTH.

Helmut
Javed replied to Helmut Meukel on 28-Apr-10 06:13 AM
A03-ABF0-FE2B3C4B11E5@microsoft.com...
, save

the above will save the file without extension.Which should be
avoided. the correct code is
if 2003 then

Me.SaveAs Me.Path & "\" & Format(Now, "hh:mm-mm-dd-yyyy") & ".xls"
if 2007 then
Me.SaveAs Me.Path & "\" & Format(Now, "hh:mm-mm-dd-yyyy") & ".xlsx"
ozgrid.com replied to Helmut Meukel on 28-Apr-10 06:20 AM
The Format was an example, nothing more. The end user can change it to suit.



--
Regards
Dave Hawley
www.ozgrid.com
Jacob Skaria replied to lena_form on 28-Apr-10 06:21 AM
I hope you are looking to save a copy of the workbook to the same path with
the date/time...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Me.SaveCopyAs Me.Path & "\" & Format(Now, "yyyymmdd-hhmmss") & ".xls"
Application.DisplayAlerts = True
End Sub

--
Jacob (MVP - Excel)