Create a new workbook with name without saving the file. Possible?

I am curious as to whether it is possible to create a new workbook with a specific file name. BUT can the file name be set without saving the file?

(I have been reading this: http://stackoverflow.com/questions/1...ing-it-to-disk and this: http://www.ozgrid.com/forum/showthread.php?t=83024 )

This is mainly a theoretical exercise so there is no need to point out that this has little to no use in the real world

Is this for coding purposes.
For example: you want to set a variable to an added workbook?

Originally Posted by davesexcel

Is this for coding purposes.
For example: you want to set a variable to an added workbook?

Well the question is mainly theoretical...

However in terms of practical use, I do have an add-in installed that has code running at Workbook_Open. This code creates a new workbook, does something and then closes the temporary workbook before finishing the Workbook_Open code. Now it works fine, except if I use Excel to create a new workbook rather than open an existing workbook. When I do, the new workbook created is default named Book2 (because the temp workbook filename defaults to Book1).

So I was curious to see if it was possible to set the file name of the file in addition to the variable I already have assigned to the temporary workbook. (I could just SaveAs the temp workbook to set the file name and then kill the temp workbook after it's closed but setting the file name seemed the most simple option. If it can be done that is!)

Originally Posted by AlphaFrog

You could create a new workbook from a template. The new workbook will have the template name in the Excel title bar if that's what you mean. You could have a blank template with whatever name you choose.

Hello AlphaFrog. Thanks for your suggestion.

The new workbook would be created from VBA code for temporary purposes only (it would be closed without saving when I am finished with it). So could it be done other than creating it from a template?

Why not create a reference to the new workbook when you create it?

Then you could reference the workbook with that in the rest of the code without never having to use it's name.

Originally Posted by AlphaFrog

I don't know of a way to do that given all your limits.

That's OK (I am only after a solution to see what Excels limits are). I appreciated your help.

Originally Posted by Norie

Why not create a reference to the new workbook when you create it?

Then you could reference the workbook with that in the rest of the code without never having to use it's name.

Thanks for the suggestion Norie.

I could (and already do) do that. However I am curious to see if the workbook filename can be set (without resorting to SaveAs to do so).

A big thank you to all those who have made suggestions so far.

The only problem is that these suggestions aim to assign a variable reference to the new workbook. They don't set the workbook name.

It's my fault for not explaining the thread question well enough in the opening post. I'll try again. See code below.

Please Login or Register  to view this content.

The first sub works fine. It is possible to set the worksheet name when you add a new worksheet to Worksheets. You don't need to save the file in order to set the worksheet name.

The second sub doesn't work. It would appear that it is not possible * to set the workbook name when you add a new workbook to Workbooks.

You can't even add a new workbook (with the name defaulting to Book#) and then add the workbook name in the next line unless you resort to SaveAs to do so.

I find the inconsistency between these 2 scenarios (add new sheet, add new workbook) rather jarring. I can't think of a reason why MicroSoft would want to prevent a user setting a workbook filename without saving first. Unless of course, it was an oversight on their part?


I'm not the only one who is seeking a solution on this scenario. See:
http://www.ozgrid.com/forum/showthread.php?t=83024
http://www.ozgrid.com/forum/showthread.php?t=52305


* unless someone reading this knows otherwise?

Why do you want/need to name the workbook?

Originally Posted by Norie

Why do you want/need to name the workbook?

I don't really want or need to!

I am mainly seeking this knowledge because I am curious to see whats Excels limits are (post #1). And because the inconsistency between the treatment of add new book to Workbooks collection vs add new sheet to Worksheets collection (as covered in post #10) is kind of bugging me.

(I did cover a possible practical use in post #4 but I could work around this with SaveAs file name, close workbook, kill file name. So I'm not after this solution for any real practical use. The answer is its own reward! )

you can't. there is no inconsistency there-you can't name a workbook in the ui either unless you save it but you can a worksheet. there is no point that I can see in naming a workbook without saving it (your point 4 doesn't give a reason as far as I can see-as Norie already said you can use a variable to refer to the workbook object without ever knowing or caring about its name). you didn't say why you can't use a template

Originally Posted by JosephP

there is no point that I can see in naming a workbook without saving it

Well I did say that this question was mainly theoretical.

Originally Posted by JosephP

(your point 4 doesn't give a reason as far as I can see-as Norie already said you can use a variable to refer to the workbook object without ever knowing or caring about its name)

Sample code below is inserted into an add-in and the sub called from workbook open. Now open Excel and create new workbook. New workbook name defaults to Book2 because of this code.

Please Login or Register  to view this content.

so use a template. if you don't you'll still have to create book1 before you can rename it and the "problem" remains ;-)

Firstly a big thank you to everyone who has contributed to this thread.
(I'm pleasantly surprised that a thread I was only asking out of mild curiosity attracted far more suggestions than threads that I require solutions for practical reasons).


I'm afraid that I will have to close this thread as it is getting off the topic IMHO. (The focus keeps shifting to alternative solutions to the weak practical use example that I offered in post #4

I will mark it as SOLVED - It can't be done. ( I'm not entirely convinced. Absence of evidence is not evidence of absence. The most convincing argument against it so far was made by JosephP in the first two lines of post #13).


P.S. I have another theoretical thread if anyone is interested... http://www.excelforum.com/excel-prog...roperties.html