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

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" <(E-Mail Removed)> wrote in message
news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> 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
>

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

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

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

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" <(E-Mail Removed)> wrote in message
> news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
> > > news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> > > > 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

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" <(E-Mail Removed)> wrote in message
> > > > news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> > > > > 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
>

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" <(E-Mail Removed)> wrote in message
> > > > news:9A5B8C8F-FA39-4B82-B49D-(E-Mail Removed)...
> > > > > 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
>