Create a new workbook then rename without saving

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham Whitehead - 14 Jun 2006 13:51 GMT

Does anyone know if it is possible to open a new workbook and rename it
without saving it using VBA?

Reply to this Message

Bob Phillips - 14 Jun 2006 14:39 GMT

Even without opening it

Dim OldName, NewName
OldName = "OLDFILE"
NewName = "NEWFILE"
Name OldName As NewName

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> Does anyone know if it is possible to open a new workbook and rename it
> without saving it using VBA?

Reply to this Message

Graham Whitehead - 15 Jun 2006 11:03 GMT

This only works when I know the name of the workbook I am renaming. When I
create a new workbook it gets named 'Book x' where x indicates how many have
been created in the current excel session. I have no way to track the value
of x - therefore I am unable to reference the old name.

I am creating a new workbook from scratch and collecting data from various
other workbooks and adding them into this new workbook as new worksheets.
Therefore, I need a way of referencing the newly created workbook every time
I need it. Does this make sense?

> Even without opening it
>
[quoted text clipped - 13 lines]
>> Does anyone know if it is possible to open a new workbook and rename it
>> without saving it using VBA?

Reply to this Message

Ardus Petus - 15 Jun 2006 11:10 GMT

Sub tester()
Dim wb As Workbook
Set wb = Workbooks.Add
<Your stuff here>
End Sub

HTH
--
AP

> This only works when I know the name of the workbook I am renaming. When
> I create a new workbook it gets named 'Book x' where x indicates how many
[quoted text clipped - 23 lines]
>>> Does anyone know if it is possible to open a new workbook and rename it
>>> without saving it using VBA?

Reply to this Message

Graham Whitehead - 15 Jun 2006 11:22 GMT

Hi, thanks for the input. However, I am getting an object required error
here. Using the code that you gave me how do I name the workbook that is
creates. For example I am using this:

Dim wb As Workbook

strNewWBName = strCountry & " Checking Workbook"

Set wb = Workbooks.Add
Workbook.Name = strNewWBName

> Sub tester()
> Dim wb As Workbook
[quoted text clipped - 33 lines]
>>>> Does anyone know if it is possible to open a new workbook and rename it
>>>> without saving it using VBA?

Reply to this Message

Bob Phillips - 15 Jun 2006 15:44 GMT

If you are creating a new workbook from scratch, you cannot rename it until
it has been saved. Therefore, when adding the workbook you should set a
variable to that workbook, and use that variable throughout

Dim oWB AsWorkbook

Set oWB = Workbooks.Add
.
' do some stuff

oWB.SaveAs Filename:= "C:\MyDir\MyTest.xls"
oWB.Close

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> This only works when I know the name of the workbook I am renaming. When I
> create a new workbook it gets named 'Book x' where x indicates how many have
[quoted text clipped - 23 lines]
> >> Does anyone know if it is possible to open a new workbook and rename it
> >> without saving it using VBA?

Reply to this Message