Excel - Two Instances - Switch Primary That Responds To File Opens From Windows File Explorer

Why not forcing instance B to close when closing instance A ?

Hi Louis,

That would be just as inconvenient as closing it manually - they'd still have to stop the processes in instance 'B', reopen everything, and restart those processes.

The objective is to keep Instance 'B' open and running, but re-open a new Instance 'A' that becomes the primary / default instance when an Excel file is double clicked in Windows File Explorer.

Thanks,

Alan.

I read your request and comment. Just to understand A and B are both not the same file running right ? like 2 macro excel .xlsm different files ?
Gowflow

Correct.

Instance A would be general files opened and used throughout the day.

Instance B is a set of linked files, running the reporting and analysis process.

None of the files world be open in both instances at the same time.

Thanks,

Alan.

what di you mean None if the files world be open in both instances at the same time???

i guess we need you to post both wirkbooks and we can integrate macro that would flag what is needed.

are these running as shared like used by several users concurently?

we can also create a small macro in an independant xlsm that should run constantly and will monitor what file is ooened given we jnow the name and warn user accordingly. but downside of this is that user has to launch this file if he forget it defeat the purpose

The set of files that could be opened in instance A is completely separate from instance B.

They aren't my files - I can't possibly ask a client to allow me to post their files, and there is nothing specific about the files that is relevant anyway.

How would that code make a new instance be the primary that responds when a file is opened from Windows file explorer?

The choice of instance must be one made by the OS I would think?  Something must record which instance to target - I think that's what we need to find?

sorry i am not following u in ur thinking. not clear to me. u talk about set if files ... and instance A like not clear we have 1 file or many??

Okay, you can re-create it yourself in a few mins:

1) Create two excel files (they can be empty - it doesn't matter) - name them B1 and B2.

2) Close Excel entirely

3) Open Excel (not a file, just an empty instance of Excel) - Call this Instance A

4) Open another instance of Excel (again, no files, just an empty instance of Excel) - Call this Instance B

5) Now open both B1 and B2 in Instance B (you'll need to use File - Open to do that)

6) Now go to Windows File Explorer, and find any Excel file you like (except B1 or B2), and double click to open it.  It will open in Instance A (due to the fact that Instance A was opened before Instance B so is the 'primary' instance for want of a better expression).  Open another file from Windows File Explorer - it will also open in Instance A.

Note that instance B is running away by itself, with no interaction between it and the files in Instance A.  If those were large linked files, pulling data from external sources, running analysis etc, they would have no interaction or effect on any files in Instance A, even if calculations take hours.


7) Now close Instance A - not just the files, but the entire instance of Excel.

Note that Instance B of Excel is still running away happily doing its thing.

8) Open a new instance of Excel (call it Instance C)

9) Open any Excel file from Windows File Explorer - it will now try to open in Instance B, interrupting calculations, or just 'hanging' there if Instance B was busy (in the middle of an hour long VBA process for example).  This is due to Instance B having become the primary instance when Instance A (the previous primary instance) was closed.


This is the problem.

We need to be able to open a new instance of Excel (Instance C in the example above), subsequent to Instance B already being open and busy, and have files opened from Windows File Explorer come up in Instance C, rather than Instance B.

The user is normally very switched on to this situation, and is careful not to actually close Instance A of Excel (just close the files themselves), but sometimes they forget, at which point (if / when they want to open another, unrelated Excel file, but forget to use File - Open in Instance C, and instead double click the file in Windows File Explorer) they are forced to close down Instance B, and start from scratch again (depending on where the reporting and analysis process got to), which is very time consuming and annoying.


Does that help explain better?

Thanks,

Alan.

ok much better

when A and B are started they are as empty excel or specific workbook that get work around them?

like here if i understood well A is not the problem whenever it is started ... but rather B we want to restrict after it is started of having files joining this instance? or i am mistaken?

I would say they would both necessarily have to be empty, else how could it work?

They would open two instances of Excel, go to the secondary, and open the workbook(s) that run the reporting processes, and then carry on working as normal from there.

Alan.

well i need to replicate this and get familiar with the whole process. quite interesting i’ll b looking at this tomorrow as here past 2am :)

just a question
suppose they close A and B the heavy calculation is running ... if a file is doubleclicked what do u expect to happen?
or its not the problem? the user knows they closed A so they open a new instance of excel and want to attach to it all files and these goes to B instead?

i need to understand what scenario is the problem

well i need to replicate this and get familiar with the whole process. quite interesting i’ll b looking at this tomorrow as here past 2am :)

Absolutely - thank you for your help so far, I really appreciate it!

suppose they close A and B the heavy calculation is running ... if a file is doubleclicked what do u expect to happen?
  or its not the problem? 

Currently, I would expect that the new file would try to open in Instance B.

If Instance B is not 'busy', then the file would just open there, in which case, no big deal - they can close it, and re-open manually in the new Instance C.

However, if Instance B is 'busy' (say, running VBA), then the new file would 'hang' and be 'half open' (the OS would believe that the file is open in Excel Instance B, with a file-lock on it), but it would not actually open in Instance B until the code finished running (might be a minute, or could be an hour later).

What they would *like* to happen is that the OS tries to open the file in the new Instance C of Excel.

the user knows they closed A so they open a new instance of excel and want to attach to it all files and these goes to B instead?

Yes - they want any files opened by double-clicking in Windows File Explorer to open in the new Instance C - currently they open (or try to) in Instrance B.


Hope that makes sense!

Alan.

so basically if i understood this well if i think of it say backward or best situation not to have the problem is:

To be able to have since opening of the office in the morning an instance A created ... and that would not stop even if no workbooks are running.

this way this instance would be primary as any other instance would be secondary and consequently all doublecliked files would join that instance and not an other instance.

am i correct?

i take it that users are familiar obviously on how to start a new instance of excel? hold ALT then click on excel in taskbar ...?

am i correct?

Yes - correct on both accounts.

The user also knows that they should not close Instance A of excel through the day, but occasionally they forget.

Even then, they can open a new Instance C of Excel, and can open files in that using File - Open, but if they then forget to do that too, and double-click on a file in Windows File Explorer (which is just the quickest and most natural way to open files in general), that file will attempt to open in Instance B, and get locked up.


Thanks,

Alan.

ok got that.

so basically bottom line if we are able to keep A alive that would do it right?

a quick thought but i guess that has a downside would be to prevent doubleclicking !! ie remove assiciation of excel to xls xlsx xlsm
but i guess this would be regarded as pain in the ... by client right?? but technically this would do it.
correct?

i would say this is the SAFE WAY no mistake and 100% guarantee

Hi,

Yes, that would do it, but I suspect I would be chased out of the client, and never allowed to return if I suggested that!

The user is a 'power user' and is data focused, rather than app centric.  They don't open an application, then open a file - rather they would open the file, and whatever application is required (Excel, Word, PowerPoint, PDF Reader etc etc) opens up.

They are highly productive - minimal mouse usage (way too slow compared to keyboard input and control), and very proficient in the use of all MS Office Apps (I almost never get a question from them - this is the first one in at least a couple of years!)


Thanks,

Alan.

wer dealing with pros !!!
hv u looked at finding active instances of excel running ... i mean VBA ... and manipulating ? don’t know ur background

Hi GowFlow,

I am very comfortable in VBA - I have written full applications in VBA in the past, and the client is also very experienced, so I can't imagine anything we would not be able to handle.

I'm not sure what you mean by 'finding active instances of excel running', and what good that would do from within Excel?

For example, one could use WMI Tasks from within VBA to get Process IDs - not sure how that would help though?

We could also use, say, a WinAPI 'FindWindow' (or 'GetWindow') function.  I am figuring this would only be triggered once the new file had been opened in Instance B, but bearing in mind that Instance B is often in the middle of VBA code, and would likely have everything temporarily disabled.  For example, I would have to check, but it seems almost certain that when the VBA starts up, one of the first things it does would be, say:

Application.EnableEvents = False
Application.ScreenUpdating = False

Select allOpen in new window

I would certainly anticipate doing that if it were me - it avoids any unexpected interruptions.


What is your thinking here?  Where would you see it going with this type of approach?

Thanks,

Alan.

well i don’t want to judge before seeing but usually when apps hang its not due to lot of calculations (very rarely) its most of the time due to bad coding or let me be more precise

coding like activesheet etc.. range.select ... activeworkbook.activesheet ... usually relies on the focused window and if this focus for some reason changes the code goes havock

Again the code may be perfect and very well developped but this is one thing yes disabling events and screen in a start of a procedure is common practice but this doesn’t mean the system would not respond.

Anyway its morning here and i will b looking to see if i can coming with something but honestly i would suggest a different route:

1) trying to isolate when and what program actually create this hanging. and this can be tough as it may happen in certain routine or situations

2) you cannot always blame it on Excel/OS etc... its like saying my code is perfect machine can’t handle what i am throwing at it

3) if your customer is so sure of himself not to want to look again at the analysing and isolating issues and suspecting a mis behavior of code to be the root of the problem then its like going to the doctor with illness symptoms and wanting to blame the medical system and not ready to see why one have these symptoms.

4) I develop for over 40 years hv done complete solutions and still each and every time i look at a code i have done i see room for improvements i see sometimes aberrations to the point i don’t believe its me who did such a flop !!! (although the code initially runs and runs well).

5) The scenario you are talking about is exactly where every single detail in code can make a difference. when a code is put under pressure this is where it either performs if well written or goes havoc. I don’t know if the revamping isolating issues scrutinizing of the code has been done before going for other solutions.

That is my opinion but still i am challenged to look at ur request and see if i cab come-up with something but my honest gut feeling tells me code need to be looked at.

Gowflow

Hi GowFlow,

As I said right up top, we are not looking at the coding or workings of the files that are running the reporting processes - they work fine, they don't crash, and the time they take is not unreasonable given what they do.  There is also a separate project under way to implement a new accounting system which will render the existing processed redundant.

The only reason a newly opened file 'hangs' is that the process (Excel Instance B) that they are trying to open into is already busy, and is designed to be that way.  It is not a fault or error in those workbooks or VBA code.

As an example, follow my instructions above to open two instances of Excel, then in 'Instance B' open an Excel file into which you have added a single module being:

Sub Sample()

Application.Wait Time + TimeSerial(1, 0, 0)

End Sub

Select allOpen in new window

Run the 'Sample' subroutine.  The instance of Excel will sit there just waiting (for an hour if you let it), then gracefully exit once it has finished its task.

When you get to the part about opening any other file from Windows File Explorer, after having closed Instance A, your file will try to open in Instance B, and just sit there waiting until you either break the code execution, or you let it finish (after an hour).  At that point, the file will happily finish opening in Instance B.  There is nothing wrong or poorly written in that one line of code - its just doing exactly what its supposed to do.


Thanks,

Alan.    

well i know its an example and well illustrated for tge purpose of highlighting the issue but excuse me why on earth would u have such an instruction at the first place?? hanging excel waiting for an hour its not logical.

and if it is not there when excel execute tasks and no such instruction is there willingly stopping excel for sometimes then the excecution of instructions are done sequentially and when one finished it takes tge next ine in line which could be the opening of the new workbook.

anyway i guess we are dealing here with a stubborness of not chechking routines.

anyway am looking at this

It was purely to provide an example, that shows you can have perfect code, and stil exhibit the behaviour that double-clicking a file in Windows File Explorer will *appear* to 'hang' during processing of VBA code.  I was trying to politely steer you away from a dead-end :-)

There is no stubbornness - its just a red herring.  The code has been working for years, has never, at least in the last few years, been reported to fail or hang, and always does exactly what it is supposed to do.  It is also subject to an existing project that will replace the current accounting system, and render it redundant.  That's why I said right upfront that we are not considering the reporting / analysing process / VBA coding.

This question is purely:  How to target a double-click file-open to a specific instance of Excel.


Thanks,

Alan.

How to target a double-click file-open to a specific instance of Excel.

that is exactly what I am after

gowflow

Hi again Alan,

Just to put you in the loop I am still very much working on your issue and made quite progress …. meantime have a question:

As we said instance B is the one we need to make sure will not be invaded when running .. and presume instance B is started by A Macro workbook that we will call Main_Instance_B.xlsm file to which maybe other files are added … then reporting and process starts All these thru the Open File and not by doubleclicking

.. Am I ok so Far ?

If this is the case then can we (If I succeed in my task) have a piece of code introduced in this Main_Instance_B.xlsm workbook ?

Gwoflow

Hi,

Sorry for being slow to respond.

I will check tomorrow (Monday) with my client, but I suspect they open one workbook using File - Open, which then opens the other workbooks when required from VBA.

In terms of whether we could have code within the 'Main_Instance_B.xlsm workbook' - I see no obvious reason why not, as long as it does not (significantly) impact on the operations going on in that instance of Excel.

I will confirm for sure Monday morning - say, 14 hours or so from now (I am posting this at about 2000hrs, Sun, 10 May 2020 - hopefully you should be able to convert to your timezone based on what time EE shows my post as being for you).

Thanks,

Alan.

Hi GowFlow,

The above is correct.  They open two instances of Excel, then in the second instance, use File - Open to open a single Excel file (what you referred to as 'Main_Instance_B.xlsm'), which then controls the remainder of the process (finding and opening many subsidiary files from various branches, collating the data, and running the analysis and reports).

They are open to having additional code in 'Main_Instance_B.xlsm' if it will solve the problem, as long as it does not substantially slow down or delay what else is happening in that instance.

Thanks,

Alan.

ok Alan give me sometime to test and see and make trials.
Will revert.

BTW how much are they willing to pay for such solution !!! I usually charge by he hour. This is a big issue here. Not trying to be opportunist here but very realistic. Also please check what version of excel they use.
Gowflow

Hi GowFlow,

They are running 32Bit Excel 2010 on 64Bit Win10 Pro.

I totally understand that you would want to be paid, but that's a rabbit warren I'm not going down.

If that is an issue for you, probably best that you stop helping now.

Thanks in advance for any further help you can provide, or if not, then thanks for trying.

Alan.

Don't worry ! I am eager to find a solution for this.
Will revert.
Gowflow

Hi Gowflow,

I am testing this today - I'll post back.

Thank you!

Alan.

Ok fine

Gowflow

Hi Gowflow,

This is very impressive, and I don't say that lightly - I may *never* had said that before in fact!

It works really well, and as you say, it allows the user to choose whether the 'first' instance receives a new workbook, or it is opened in a new instance.

The only thing I would like to change, is to give the user the option of which instance to open a new file in.  Currently it is either the first instance, or a completely new instance (unless I am missing something).

However, this solves the question as asked, so I am closing this as 'solved', and will start a new question for that tweak:

https://www.experts-exchange.com/questions/29183236/Excel-Instance-Chooser-VBA-Follow-up-question.html#questionAdd

Thank you very much.

Alan.

Your welcome Alan and I am very glad you liked it. Coming from an Expert your appreciation has a different weight and it sure means a lot. thank you for it.

I will sure look into the new one and try to break my head as they say … to find a way but if you allow my humble opinion, the fact that you can divert new opening to an other instance coming from this specific case and workbook I think would be enough to serve the purpose.

For sure I am also curious to see if we can manage and choose an instance to start or join.

Thanks again Alan I really appreciate your comment as I feel genuine.

Regards
Gowflow

Totally agree - hence closing this question as solved, since you nailed it 👍

Thanks again,

Alan.