Activate the temporary "Book1"

bibux

Board Regular
Joined
Mar 13, 2014
Messages
51
I am trying to activate a "generated" workbook that is unsaved and has no extension, just the name "Book1". If I save it as "Book1.xlsx" and then repoen it, I can refer to it. Like this.

Code:
Set sh = Workbooks("Book1.xlsx").Sheets("Stats")
Set rng = sh.UsedRange

The question is, can I refer to it while it is unsaved and named simply "Book1"? I have tried
Code:
Workbooks.("Book1").Sheets("Stats").Activate
- to no avail.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Have you tried Workbooks("Book1")?
 
Upvote 0
I think I was doing that before and the "." was a typo. Anyway I get a runtime error, subscript out of range.
This is a workbook generated by a software application, and I'm afraid it doesn't have a name that Excel likes unless I save it. Strangely I remember being able to activate it some time ago (when I didn't need to), but I don't know how I did that.
 
Upvote 0
So Book1 isn't the name of the workbook?

Is it the only workbook that's open?

If it is then you can refer to it using Workbooks(1).

If it isn't and you know the names of the other open workbooks then you can use code to set a reference to it.

Another possibilty would be to identify it by partial name, that would only really work if there was some pattern/logic the other application uses for the workbook name.
 
Upvote 0
Thanks for your reply. No, it's not the only open workbook, and it would not make sense if was, because it's a generated workbook and I need to reference it from another workbook and copy/paste its data.
I'll attach a screenshot for better understanding.
https://www.dropbox.com/s/nupo5obgdt9hxx0/20140504_201733.jpg
As you can see the workbook does have a name (Book1) and also ActiveWorkbook.Name is Book1
Can you please expand on what would be the best option to make a reference to it. I can't simply save it and then reference to it because this has to be done hundreds of times (once per player).
The software does have a pattern to naming the files. If there's no other generated file open then the name will be "Book1". If there is one generated file already open then the name will be "Book2", if there are two, then "Book3", etc. But I normally only open one.
 
Upvote 0
What does
Code:
Debug.Print ActiveWorkbook.FullName
give you?
 
Upvote 0
I'm sorry, I don't know where to find what it Debug.Print returns as result.
After doing this however
Code:
Range("G1") = ActiveWorkbook.FullName
the result is "Book1"
 
Upvote 0
Can you refer to it as
Code:
Set sh = Workbooks("Book1.xls*").Sheets("Stats")
or
Code:
Set sh = Workbooks("Book1.xlt*").Sheets("Stats")
Although be very careful if you can with xlt*


btw
I'm sorry, I don't know where to find what it Debug.Print returns as result
It puts it in the immediate window (if it isn't showing Ctrl-G)
 
Last edited:
Upvote 0
No luck. And immediate windows still says "Book1"
Didn't say it wouldn't...just saying where to find it as you didn't know. :)


Afraid no other ideas as I don't think you can refer to it as a .tmp file but then again never tried it so wouldn't know.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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
Back
Top