Activate the temporary "Book1"

[bibux]

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.

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

Workbooks.("Book1").Sheets("Stats").Activate

- to no avail.

 

[bibux]

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.

Okay, thanks all the same

 

[GTO]

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.

Greetings,

When the other application creates the unsaved workbook, is it created in the same instance of Excel that your coded workbook is running in? That is, I am curious if the other application may be starting a new instance of Excel.

Mark

 

[bibux]

Hi, I think you are very right, I see two "EXCEL"s running in the "Processes" tab in the Task Manager. Is there anything that can be done in this case?

 

[Norie]

If more than one workbook is generated which one(s) do you want to reference?

The first one generated, the last one generated, or all the generated workbooks?

 

[bibux]

I will be generating them one at a time, copy the data into the master workbook, then close it (without saving) and repeat with the next one. As I said, if there are no other generated workbooks open(which will be the case), the name will always be "Book1"

 

[GTO]

Just to confirm, go to View | Switch Windows in the window showing the other app's created sheet. If it is the only workbook showing (and you already have your coded workbook open) then we are confirmed. Conversely, View | Switch Windows in the coded workbook will probably show all open workbooks except the other app's created workbook.

And to answer "...anything that can be done...?": Uhm... probably, but it is some API and would likely be a bit painful, what with the created workbook's name being unpredictable. I am not trying to discourage, just relaying it'll be some code, and may require the user to select the correct workbook from a list of some sort.

Mark

 

[bibux]

Yes, "Book1" is the only workbook showing, so that's confirmed. I've also checked by opening other normal workbooks and the number of "EXCEL" instances running does not increase, whereas generating a new workbook opens a new "EXCEL" instance in the Task Manager
Thank you very much for your input here, I've been bumping my head against this for many hours and probably quite a few more, I would have never imagined that "Book1" is in a different Excel instance.
I guess I'll stop trying then, and go back to saving each sheet, reopening it, etc

 

[Norie]

How are the (new) workbooks generated?

 

[GTO]

Yes, "Book1" is the only workbook showing, so that's confirmed. I've also checked by opening other normal workbooks and the number of "EXCEL" instances running does not increase, whereas generating a new workbook opens a new "EXCEL" instance in the Task Manager
Thank you very much for your input here, I've been bumping my head against this for many hours and probably quite a few more, I would have never imagined that "Book1" is in a different Excel instance.
I guess I'll stop trying then, and go back to saving each sheet, reopening it, etc

To echo Norie's question a bit, are you "manually" creating the (presumably a report of some type) workbooks whilst using (i.e. - I clicked a button or something) the other application?

If yes - can you assure that you will start out with only one instance of Excel, and that the only other instance of Excel will be when the other app creates it, AND, that the other instance will only have the one workbook? That is, I am wondering if we could (via code) look for the one other instance and workbooks(1) therein.

Mark

 

[bibux]

I think the attachment can answer your questions. Also I'm sure that the application is working with an Access database but I don't know if this has any relevance.
https://www.dropbox.com/s/0evxwurhkvlc5wz/20140504_222903.mp4