Code issue - before_save() - Filename with date stamp - worksheet
 

Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

Code issue - before_save() - Filename with date stamp - worksheet
 

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

Code issue - before_save() - Filename with date stamp - worksheet
 

You can only change the workbook's name by saving it as that name.

Instead of using workbook_beforesave, have you thought about using a dedicated
macro that does the rename and save:

Option Explicit
Sub SaveMeNow()

With ActiveSheet
.Name = "As of " & Format(Now, "MM-DD-YYYY")
End With

ActiveWorkbook.saveas filename:="{FILENAME}" _
& Format(Now(), "yyyymmdd" & ".xls"), fileformat:=xlworkbooknormal

End Sub

It would scare me using the activesheet and the date. I may be saving the wrong
sheet and I may get an error if that name already exists.

You could use the codename instead and maybe add the time???

Option Explicit
Sub SaveMeNow()

With Sheet1
.Name = "As of " & Format(Now, "MM-DD-YYYY__hhmmss")
End With

ActiveWorkbook.saveas filename:="{FILENAME}" _
& Format(Now(), "yyyymmdd" & ".xls"), fileformat:=xlworkbooknormal

End Sub

Use this to get the codename of the activesheet.

msgbox activesheet.codename




awrex wrote:

Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

--

Dave Peterson

Code issue - before_save() - Filename with date stamp - worksheet
 

.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")

I think the Format function call in the above line should look like this
instead...

Format(Now(), "yyyymmdd") & ".xls"

otherwise the "s" in ".xls" will be interpreted as seconds.

I guess you could leave it embedded if you want, but then you would need to
put a backslash in front of the "s"; like this...

Format(Now(), "yyyymmdd" & ".xl\s")

Rick

Code issue - before_save() - Filename with date stamp - worksheet
 

I missed the format problem that Rick caught.

I'd use this code instead:

Option Explicit
Sub SaveMeNow()

With ActiveSheet
.Name = "As of " & Format(Now, "MM-DD-YYYY")
End With

ActiveWorkbook.saveas filename:="{FILENAME}" _
& Format(Now, "yyyymmdd") & ".xls", fileformat:=xlworkbooknormal

End Sub

Dave Peterson wrote:

You can only change the workbook's name by saving it as that name.

Instead of using workbook_beforesave, have you thought about using a dedicated
macro that does the rename and save:

Option Explicit
Sub SaveMeNow()

With ActiveSheet
.Name = "As of " & Format(Now, "MM-DD-YYYY")
End With

ActiveWorkbook.saveas filename:="{FILENAME}" _
& Format(Now(), "yyyymmdd" & ".xls"), fileformat:=xlworkbooknormal

End Sub

It would scare me using the activesheet and the date. I may be saving the wrong
sheet and I may get an error if that name already exists.

You could use the codename instead and maybe add the time???

Option Explicit
Sub SaveMeNow()

With Sheet1
.Name = "As of " & Format(Now, "MM-DD-YYYY__hhmmss")
End With

ActiveWorkbook.saveas filename:="{FILENAME}" _
& Format(Now(), "yyyymmdd" & ".xls"), fileformat:=xlworkbooknormal

End Sub

Use this to get the codename of the activesheet.

msgbox activesheet.codename

awrex wrote:

Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

--

Dave Peterson

--

Dave Peterson

Code issue - before_save() - Filename with date stamp - worksh
 

Thanks Bob, though I think I may have not asked the question in a proper
format.

When I wrote {FILENAME} this was to indicate a hardcoded filename that I
already have.

I am still unable to get the code to rename the worksheet and it won't
rename the file as well.

I know Dave Petersen suggested a macro, though I would like to not have a
macro if possible. Thanks anyhow Dave!

Thanks Rick for the correction on the ).


"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

Code issue - before_save() - Filename with date stamp - worksh
 

Ok... I've made some changes as I didn't see anything happening. I've revised
the code as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

'ActiveWorkbook.SaveAs Filename = "<MyFilename" & Format(Now(),
"yyyymmdd")


Now the worksheet rename works, however the filename portion bombs out and I
get an error along with a second vbok pop up. I get a 1004 runtime error and
clicking the HELP button on the popup error gives me a grey screen.

Running XP Pro 2k3 11.6560.6568 SP2
VBA Retail:6.4.9972
Ver. 9972
VB 6.3






"awrex" wrote:

Thanks Bob, though I think I may have not asked the question in a proper
format.

When I wrote {FILENAME} this was to indicate a hardcoded filename that I
already have.

I am still unable to get the code to rename the worksheet and it won't
rename the file as well.

I know Dave Petersen suggested a macro, though I would like to not have a
macro if possible. Thanks anyhow Dave!

Thanks Rick for the correction on the ).


"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub

Code issue - before_save() - Filename with date stamp - worksh
 

First, you should post the code that you tried. The code you pasted had that
..saveas line commented out.

If you uncomment it and change the equal sign to:

... filename:="<myfilename" & ....

does it work?

Just a question. You really want characters {Filename} or <Filename to start
the name of the file?? That looks kind of odd to me.

awrex wrote:

Ok... I've made some changes as I didn't see anything happening. I've revised
the code as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

'ActiveWorkbook.SaveAs Filename = "<MyFilename" & Format(Now(),
"yyyymmdd")


Now the worksheet rename works, however the filename portion bombs out and I
get an error along with a second vbok pop up. I get a 1004 runtime error and
clicking the HELP button on the popup error gives me a grey screen.

Running XP Pro 2k3 11.6560.6568 SP2
VBA Retail:6.4.9972
Ver. 9972
VB 6.3

"awrex" wrote:

Thanks Bob, though I think I may have not asked the question in a proper
format.

When I wrote {FILENAME} this was to indicate a hardcoded filename that I
already have.

I am still unable to get the code to rename the worksheet and it won't
rename the file as well.

I know Dave Petersen suggested a macro, though I would like to not have a
macro if possible. Thanks anyhow Dave!

Thanks Rick for the correction on the ).


"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub





--

Dave Peterson

Code issue - before_save() - Filename with date stamp - worksh
 

Here is the full code that works, "sometimes".....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

ActiveWorkbook.SaveAs _
"<filename" & Format(Now(), "yyyymmdd")

End Sub

Once I removed the "FILENAME: =" portion it started to work, however now it
bombs Excel sometimes....

If I take the current file, close it out without saving, rename it outside
of Excel, open it, click Save - it renames the worksheet and renames the file
then bombs. Then once autorecover opens the file again and I click Save it
runs through the routine though it sees there is a duplicate and asks if I
want to save over it I click yes it works with no problems.




"Dave Peterson" wrote:

First, you should post the code that you tried. The code you pasted had that
..saveas line commented out.

If you uncomment it and change the equal sign to:

... filename:="<myfilename" & ....

does it work?

Just a question. You really want characters {Filename} or <Filename to start
the name of the file?? That looks kind of odd to me.

awrex wrote:

Ok... I've made some changes as I didn't see anything happening. I've revised
the code as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

'ActiveWorkbook.SaveAs Filename = "<MyFilename" & Format(Now(),
"yyyymmdd")


Now the worksheet rename works, however the filename portion bombs out and I
get an error along with a second vbok pop up. I get a 1004 runtime error and
clicking the HELP button on the popup error gives me a grey screen.

Running XP Pro 2k3 11.6560.6568 SP2
VBA Retail:6.4.9972
Ver. 9972
VB 6.3

"awrex" wrote:

Thanks Bob, though I think I may have not asked the question in a proper
format.

When I wrote {FILENAME} this was to indicate a hardcoded filename that I
already have.

I am still unable to get the code to rename the worksheet and it won't
rename the file as well.

I know Dave Petersen suggested a macro, though I would like to not have a
macro if possible. Thanks anyhow Dave!

Thanks Rick for the correction on the ).


"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub





--

Dave Peterson

Code issue - before_save() - Filename with date stamp - worksh
 

Hi Dave.... I got a response from J Tomlinson on it...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On error goto ErrorHandler
Application.enableevents = false
cancel = true
MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

ChDir "<new dir path"
ActiveWorkbook.SaveAs _
"<MyFileName" & Format(Now(), "yyyymmdd")
ErrorHandler:
Application.enableevents = true
End Sub

Thanks for your help as well!!!


"Dave Peterson" wrote:

First, you should post the code that you tried. The code you pasted had that
..saveas line commented out.

If you uncomment it and change the equal sign to:

... filename:="<myfilename" & ....

does it work?

Just a question. You really want characters {Filename} or <Filename to start
the name of the file?? That looks kind of odd to me.

awrex wrote:

Ok... I've made some changes as I didn't see anything happening. I've revised
the code as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

'ActiveWorkbook.SaveAs Filename = "<MyFilename" & Format(Now(),
"yyyymmdd")


Now the worksheet rename works, however the filename portion bombs out and I
get an error along with a second vbok pop up. I get a 1004 runtime error and
clicking the HELP button on the popup error gives me a grey screen.

Running XP Pro 2k3 11.6560.6568 SP2
VBA Retail:6.4.9972
Ver. 9972
VB 6.3

"awrex" wrote:

Thanks Bob, though I think I may have not asked the question in a proper
format.

When I wrote {FILENAME} this was to indicate a hardcoded filename that I
already have.

I am still unable to get the code to rename the worksheet and it won't
rename the file as well.

I know Dave Petersen suggested a macro, though I would like to not have a
macro if possible. Thanks anyhow Dave!

Thanks Rick for the correction on the ).


"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name)-4) &
Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"awrex" wrote in message
...
Could someone tell this tourist why this is not working please??

Private Sub Workbook_BeforeSave()

With ActiveSheet
.Name = "As of " & Format(Now(), "MM-DD-YYYY")
End With

With ActiveWorkbook
.Name = "{FILENAME}" & Format(Now(), "yyyymmdd" & ".xls")
End With

End Sub





--

Dave Peterson