Thread: script to change filename

i am looking for a script or macro that change the name of a werksheet file
to the contents of two cells(one is a date, the other, a users name), the
first time the file is saved. the file is opened from a template named daily
reports.xlt. Also, i can get the current date to show in the appropriate
cell.... but when i open the file a week later, the date changes to the
current date.

To rename an Excel Workbook, you have to save it as the new name

With ActiveWorkbook
.SaveAs Filename:=.Path & "\" & _
Worksheets("Sheet1").Range("A1").Text & _
Worksheets("Sheet1").Range("B9").Text & _
".xls"
End With


To maintain the date, after getting the date you want, change it to a
constant with:

With Worksheets("Sheet1").Range("A1")
.Value = .Value
End With

or skip the formula and do

With Worksheets("Sheet1").Range("A1")
.Value = Date
End With

--
Regards,
Tom Ogilvy



"jeramie" <jeramie@discussions.microsoft.com> wrote in message
news:3B36120B-090E-4401-97B5-EC8F22FEFA66@microsoft.com...
> i am looking for a script or macro that change the name of a werksheet
file
> to the contents of two cells(one is a date, the other, a users name), the
> first time the file is saved. the file is opened from a template named
daily
> reports.xlt. Also, i can get the current date to show in the appropriate
> cell.... but when i open the file a week later, the date changes to the
> current date.

thanks! I'll add this in and see how it works.

"Tom Ogilvy" wrote:

> To rename an Excel Workbook, you have to save it as the new name
>
> With ActiveWorkbook
> .SaveAs Filename:=.Path & "\" & _
> Worksheets("Sheet1").Range("A1").Text & _
> Worksheets("Sheet1").Range("B9").Text & _
> ".xls"
> End With
>
>
> To maintain the date, after getting the date you want, change it to a
> constant with:
>
> With Worksheets("Sheet1").Range("A1")
> .Value = .Value
> End With
>
> or skip the formula and do
>
> With Worksheets("Sheet1").Range("A1")
> .Value = Date
> End With
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "jeramie" <jeramie@discussions.microsoft.com> wrote in message
> news:3B36120B-090E-4401-97B5-EC8F22FEFA66@microsoft.com...
> > i am looking for a script or macro that change the name of a werksheet
> file
> > to the contents of two cells(one is a date, the other, a users name), the
> > first time the file is saved. the file is opened from a template named
> daily
> > reports.xlt. Also, i can get the current date to show in the appropriate
> > cell.... but when i open the file a week later, the date changes to the
> > current date.
>
>
>

well.... I got it working with a "private sub workbook_open()", but I need it
to save 'after' The username and date are put in the cells. I thought I could
get it going with an exit event or a lostfocus, but I can't seem to get the
syntax right.

"jeramie" wrote:

> thanks! I'll add this in and see how it works.
>
> "Tom Ogilvy" wrote:
>
> > To rename an Excel Workbook, you have to save it as the new name
> >
> > With ActiveWorkbook
> > .SaveAs Filename:=.Path & "\" & _
> > Worksheets("Sheet1").Range("A1").Text & _
> > Worksheets("Sheet1").Range("B9").Text & _
> > ".xls"
> > End With
> >
> >
> > To maintain the date, after getting the date you want, change it to a
> > constant with:
> >
> > With Worksheets("Sheet1").Range("A1")
> > .Value = .Value
> > End With
> >
> > or skip the formula and do
> >
> > With Worksheets("Sheet1").Range("A1")
> > .Value = Date
> > End With
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "jeramie" <jeramie@discussions.microsoft.com> wrote in message
> > news:3B36120B-090E-4401-97B5-EC8F22FEFA66@microsoft.com...
> > > i am looking for a script or macro that change the name of a werksheet
> > file
> > > to the contents of two cells(one is a date, the other, a users name), the
> > > first time the file is saved. the file is opened from a template named
> > daily
> > > reports.xlt. Also, i can get the current date to show in the appropriate
> > > cell.... but when i open the file a week later, the date changes to the
> > > current date.
> >
> >
> >

tom, I got it all working right, but noe my boss wants me to have it save
each file into a folder based on the month.... any suggestions?

"jeramie" wrote:

> thanks! I'll add this in and see how it works.
>
> "Tom Ogilvy" wrote:
>
> > To rename an Excel Workbook, you have to save it as the new name
> >
> > With ActiveWorkbook
> > .SaveAs Filename:=.Path & "\" & _
> > Worksheets("Sheet1").Range("A1").Text & _
> > Worksheets("Sheet1").Range("B9").Text & _
> > ".xls"
> > End With
> >
> >
> > To maintain the date, after getting the date you want, change it to a
> > constant with:
> >
> > With Worksheets("Sheet1").Range("A1")
> > .Value = .Value
> > End With
> >
> > or skip the formula and do
> >
> > With Worksheets("Sheet1").Range("A1")
> > .Value = Date
> > End With
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "jeramie" <jeramie@discussions.microsoft.com> wrote in message
> > news:3B36120B-090E-4401-97B5-EC8F22FEFA66@microsoft.com...
> > > i am looking for a script or macro that change the name of a werksheet
> > file
> > > to the contents of two cells(one is a date, the other, a users name), the
> > > first time the file is saved. the file is opened from a template named
> > daily
> > > reports.xlt. Also, i can get the current date to show in the appropriate
> > > cell.... but when i open the file a week later, the date changes to the
> > > current date.
> >
> >
> >