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" <lenaform@discussions.microsoft.com> wrote in message 
news:41DAA1C1-7F08-4771-8D25-82036AD6288C@microsoft.com...
> 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" <lenaform@discussions.microsoft.com> wrote in message 
news:41DAA1C1-7F08-4771-8D25-82036AD6288C@microsoft.com...
> 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" <dave@ozgrid.com> schrieb im Newsbeitrag 
news:8CB80E2A-D816-4A03-ABF0-FE2B3C4B11E5@microsoft.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
>
>
>
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "lena_form" <lenaform@discussions.microsoft.com> wrote in message 
> news:41DAA1C1-7F08-4771-8D25-82036AD6288C@microsoft.com...
>> 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=A0pm, "Helmut Meukel" <NoS...@NoProvider.de> 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" <d...@ozgrid.com> schrieb im Newsbeitragnews:8CB80E2A-D816-4=
A03-ABF0-FE2B3C4B11E5@microsoft.com...
>
>
>
> > In the ThisWorkbook Module;
>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > =A0 =A0Me.SaveAs Me.Path & "\" & Format(Now, "hh:mm-mm-dd-yyyy")
> > End Sub
>
> > --
> > Regards
> > Dave Hawley
> >www.ozgrid.com
> > "lena_form" <lenaf...@discussions.microsoft.com> wrote in message
> >news:41DAA1C1-7F08-4771-8D25-82036AD6288C@microsoft.com...
> >> 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" <NoSpam@NoProvider.de> wrote in message 
news:OaRj6mr5KHA.3184@TK2MSFTNGP05.phx.gbl...
> 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" <dave@ozgrid.com> schrieb im Newsbeitrag 
> news:8CB80E2A-D816-4A03-ABF0-FE2B3C4B11E5@microsoft.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
>>
>>
>>
>> -- 
>> Regards
>> Dave Hawley
>> www.ozgrid.com
>> "lena_form" <lenaform@discussions.microsoft.com> wrote in message 
>> news:41DAA1C1-7F08-4771-8D25-82036AD6288C@microsoft.com...
>>> 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