Change workbook name

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
Hi ,

is it possible to change a workbook name using vba without using save as?

or if creating a new workbook using vba is it possible to set the name prior to creating the new workbook?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
you can rename a file with this code (include filepath and filename of source and destination.)
Code:
Name "C:\ABC.xls" as "C:\DEF.xls"
the code for naming a new book depends on how you provide the name.
if cell A1 has a name try these codes
Code:
Workbooks.Add
ActiveWorkbook.SaveAs Workbooks("Dialup.xls").Sheets("Sheet1").Cells(1, 1) & ".xls"
RAvi
 

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
thanks for the reply RAvi - i am creating a workbook from a macro - the problem is that the new workbook could be called anything from book1 upwards depending on what the user has been doing. i don't want to save the workbook that has been made and can't rename it using
Code:
Name "C:\ABC.xls" as "C:\DEF.xls"
as i don't know what book number it has been created as - is there another way?
can the workbook be created as "Dialup"?

along the lines of
Code:
Workbooks.Add.name="dialup.xls"

i know this code doesn't work as i get the message 'cant assign to read only property' but some function must define the name of a new workbook or you wouldn't get book1, book2 etc
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Sorry but there is no way that you can rename a workbook while it's still open.:)
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566

ADVERTISEMENT

Hi
your requirement is not clear to me. This code probably does what you want.
Code:
Workbooks.Add
ActiveWorkbook.SaveAs filename:= "Dialup.xls"
Ravi
 

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
Ravi - i dont want to save the workbook so don't want to use SaveAs
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Why do you want to name it then?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If you want to add a workbook and then refer to it in code you can do something like this then refer to it as wb

Code:
Dim wb As Workbook
Set wb = Workbooks.Add
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top