Save as vba excel

  • Thread starter Thread starter lena_form
  • Start date Start date
L

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
 
In the ThisWorkbook Module;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.SaveAs Me.Path & "\" & Format(Now, "hh:mm-mm-dd-yyyy")
End Sub
 
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
 
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 -
Click to expand...

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"
 
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
 
Back
Top