script to change filename
 

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

script to change filename
 

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

script to change filename
 

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