Prev: Evaluate VLOOKUP in cells in column
Next: Detect macro in excel file without opening it
From: lena_form on 28 Apr 2010 04:19
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
From: ozgrid.com on 28 Apr 2010 04:25
In the This Workbook Module;
"lena_form" <lenaform(a)discussions.microsoft.com> wrote in message
news:41DAA1C1-7F08-4771-8D25-82036AD6288C(a)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

From: ozgrid.com on 28 Apr 2010 04:26
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(a)discussions.microsoft.com> wrote in message
news:41DAA1C1-7F08-4771-8D25-82036AD6288C(a)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

From: Helmut Meukel on 28 Apr 2010 05:40
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(a)ozgrid.com> schrieb im Newsbeitrag
news:8CB80E2A-D816-4A03-ABF0-FE2B3C4B11E5(a)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(a)discussions.microsoft.com> wrote in message
> news:41DAA1C1-7F08-4771-8D25-82036AD6288C(a)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
>


From: Javed on 28 Apr 2010 06:13
On Apr 28, 2:40 pm, "Helmut Meukel" <NoS...(a)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...(a)ozgrid.com> schrieb im Newsbeitragnews:8CB80E2A-D816-4A03-ABF0-FE2B3C4B11E5(a)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" <lenaf...(a)discussions.microsoft.com> wrote in message
> >news:41DAA1C1-7F08-4771-8D25-82036AD6288C(a)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"
 |  Next  |  Last
Pages: 1 2
Prev: Evaluate VLOOKUP in cells in column
Next: Detect macro in excel file without opening it