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

[Guest]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

.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

 

[Dave Peterson]

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

 

[Guest]

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 ).

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Guest]

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.

 

[Guest]

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!!!