Save as vba excel

[lena_form]

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

 

[ozgrid.com]

In the This Workbook Module;

 

[ozgrid.com]

In the ThisWorkbook Module;

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

 

[Helmut Meukel]

Hmm,

I wouldn't 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]

Hmm,

I wouldn't 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







- Show quoted text -

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]

The Format was an example, nothing more. The end user can change it to suit.

 

[Jacob Skaria]

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