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

In the This Workbook Module;
"lena_form" wrote in message
...
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

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
"lena_form" wrote in message
...
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

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


"ozgrid.com" schrieb im Newsbeitrag
...
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
"lena_form" wrote in message
...
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

On Apr 28, 2:40*pm, "Helmut Meukel" wrote:
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

"ozgrid.com" schrieb im ...



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
"lena_form" wrote in message
...
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- Hide quoted text -

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

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



--
Regards
Dave Hawley
www.ozgrid.com
"Helmut Meukel" wrote in message
...
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


"ozgrid.com" schrieb im Newsbeitrag
...
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
"lena_form" wrote in message
...
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

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)


"lena_form" wrote:

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