re-name workbook?

[Slow1911s]

Is it possible, through VBA, to simply rename the workbook without creating a new copy (a la Save As?)

What I want to happen is once the file is opened and edits are made, I want the file name to change based on (1) the button the user presses and then (2) information contained in the worksheet itself.

Make sense?

TIA

[PCLIVE]

Something similar to this should work.

CurrentWBookName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs ("test3.xls")
Kill CurrentWBookName

HTH,
Paul


"Slow1911s" <[email protected]> wrote
in message news:[email protected]...
>
> Is it possible, through VBA, to simply rename the workbook without
> creating a new copy (a la Save As?)
>
> What I want to happen is once the file is opened and edits are made, I
> want the file name to change based on (1) the button the user presses
> and then (2) information contained in the worksheet itself.
>
> Make sense?
>
> TIA
>
>
> --
> Slow1911s
> ------------------------------------------------------------------------
> Slow1911s's Profile:
> http://www.excelforum.com/member.php...o&userid=31113
> View this thread: http://www.excelforum.com/showthread...hreadid=507768
>

[Slow1911s]

thanks

That is straight forward. But, there is no way to simply give the current file a new name without (at the same time) creating a second file?

[PCLIVE]

The file is already saved. When that file is open, you are not working from
a file. You are working from memory and a temporay filespace. To save the
current file as a file with a different name, the only option will be a
second file. Then if you don't want the previous filename to exist, you
must remove somehow. That is where the "Kill" statement comes in. Now that
your file is saved as a different name, the Kill statement removes the old
file. I don't know of any other way to do it.

Good Luck,
Paul

"Slow1911s" <[email protected]> wrote
in message news:[email protected]...
>
> thanks
>
> That is straight forward. But, there is no way to simply give the
> current file a new name without (at the same time) creating a second
> file?
>
>
> --
> Slow1911s
> ------------------------------------------------------------------------
> Slow1911s's Profile:
> http://www.excelforum.com/member.php...o&userid=31113
> View this thread: http://www.excelforum.com/showthread...hreadid=507768
>

Something similar to this should work.

CurrentWBookName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs ("test3.xls")
Kill CurrentWBookName

HTH,
Paul

[Chip Pearson]

Try the following:

With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.SaveAs Filename:="H:\book2.xls"
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Slow1911s"
<[email protected]> wrote in
message
news:[email protected]...
>
> Is it possible, through VBA, to simply rename the workbook
> without
> creating a new copy (a la Save As?)
>
> What I want to happen is once the file is opened and edits are
> made, I
> want the file name to change based on (1) the button the user
> presses
> and then (2) information contained in the worksheet itself.
>
> Make sense?
>
> TIA
>
>
> --
> Slow1911s
> ------------------------------------------------------------------------
> Slow1911s's Profile:
> http://www.excelforum.com/member.php...o&userid=31113
> View this thread:
> http://www.excelforum.com/showthread...hreadid=507768
>

[Slow1911s]

Can xlReadWrite also be used? I use a password.

Try the following:

With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.SaveAs Filename:="H:\book2.xls"
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

[Slow1911s]

Here is a copy of the code that I have where I'm having the problem. The italic lines are where the errors are being pointed out.

If reinspectorID <> "" And reinspectorInitials <> "" And ws.Range("LastName") <> "" And ws.Range("ID") <> "" And ws.Range("Clm") <> "" Then

ActiveWorkbook.saveas Filename:=newfilename, FileFormat:=xlNormal
MsgBox ("Your file has been saved as" & " " & newfilename & " ")

With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.saveas Filename:=newfilename, FileFormat:=xlNormal
End With

Range("A1:P67").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$P$68"
ActiveWindow.SelectedSheets.PrintOut Copies:=1