GTO
08-02-2009, 08:12 PM
Greetings to All:
In attempting to help at: http://vbaexpress.com/forum/showthread.php?p=191668
I was reminded of something that I have been unable to accomplish thus far.
Let's say for whatever reason, I want to have a particular wb insist that it is the only wb open in the instance that it is opened in. Thus far - I have managed to:
If my "LoneWolf" wb is opened and it finds that there are other workbooks existing in the same instance, it will basically replicate itself (SaveAs), create another instance, and reopen itself there.
Once it is open and all on its lonesome, it can prevent other workbooks from opening in the same instance, via the Application Events, something like:
Private Sub XLAPP_ORIG_WorkbookOpen(ByVal Wb As Workbook)
If Not Wb.Name = ThisWorkbook.Name Then
Wb.Close False
MsgBox MsgTxt(2), 64, vbNullString
End If
End Sub
While the above (admittedly rudimentary) will stop the user from opening another wb from 'LoneWolf', the problem remains that the user may have two instances running, but, if 'this instance' (the one that has 'LoneWolf' open) is the last active instance - then opening another wb via Windows Explorer results in the the wb opening (and being cancelled/closed) in 'this instance'.
Now I realize that I could simply create a new instance in the above code, and open the other wb there - but this seems sloppy as any number of instances could be created.
So - is there a way to use winmgmts and/or something else to not simply count the number of instance(s), but to identify them individually and force the workbook to be opened in the other instance?
Apologies for getting wordy, but hopefully my question seems sensibly put.
Thank you so much,
Mark
In attempting to help at: http://vbaexpress.com/forum/showthread.php?p=191668
I was reminded of something that I have been unable to accomplish thus far.
Let's say for whatever reason, I want to have a particular wb insist that it is the only wb open in the instance that it is opened in. Thus far - I have managed to:
If my "LoneWolf" wb is opened and it finds that there are other workbooks existing in the same instance, it will basically replicate itself (SaveAs), create another instance, and reopen itself there.
Once it is open and all on its lonesome, it can prevent other workbooks from opening in the same instance, via the Application Events, something like:
Private Sub XLAPP_ORIG_WorkbookOpen(ByVal Wb As Workbook)
If Not Wb.Name = ThisWorkbook.Name Then
Wb.Close False
MsgBox MsgTxt(2), 64, vbNullString
End If
End Sub
While the above (admittedly rudimentary) will stop the user from opening another wb from 'LoneWolf', the problem remains that the user may have two instances running, but, if 'this instance' (the one that has 'LoneWolf' open) is the last active instance - then opening another wb via Windows Explorer results in the the wb opening (and being cancelled/closed) in 'this instance'.
Now I realize that I could simply create a new instance in the above code, and open the other wb there - but this seems sloppy as any number of instances could be created.
So - is there a way to use winmgmts and/or something else to not simply count the number of instance(s), but to identify them individually and force the workbook to be opened in the other instance?
Apologies for getting wordy, but hopefully my question seems sensibly put.
Thank you so much,
Mark