Change workbook name

[dialup]

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?

 

[ravishankar]

Hi
you can rename a file with this code (include filepath and filename of source and destination.)

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

Workbooks.Add
ActiveWorkbook.SaveAs Workbooks("Dialup.xls").Sheets("Sheet1").Cells(1, 1) & ".xls"

RAvi

 

[dialup]

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

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

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]

Sorry but there is no way that you can rename a workbook while it's still open.

 

[ravishankar]

ADVERTISEMENT

Hi
your requirement is not clear to me. This code probably does what you want.

Workbooks.Add
ActiveWorkbook.SaveAs filename:= "Dialup.xls"

Ravi

 

[dialup]

Ravi - i dont want to save the workbook so don't want to use SaveAs

 

[Norie]

Why do you want to name it then?

 

[VoG]

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

Dim wb As Workbook
Set wb = Workbooks.Add