Create a new workbook then rename without saving

[Graham Whitehead]

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

 

[Bob Phillips]

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)

 

[Graham Whitehead]

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?

 

[Ardus Petus]

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

HTH

 

[Graham Whitehead]

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

 

[Bob Phillips]

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