re-name workbook?
 

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

re-name workbook?
 

Something similar to this should work.

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

HTH,
Paul


"Slow1911s" wrote
in message ...

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

re-name workbook?
 

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

re-name workbook?
 

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

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

re-name workbook?
 

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

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

re-name workbook?
 

Can xlReadWrite also be used? I use a password.

Chip Pearson Wrote:
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
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=507768

re-name workbook?
 

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



--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=507768