Multiple Instance of Excel

[Tom59593]

Hey guys,

I am working on an application for a monthly sports tournament that I run. There are three workbooks to this application: the Administration workbook, the Bracket workbook, and the Leaderboard workbook. I currently have a dual monitor setup (extended, not clone) with my laptop in front of me and a large TV on the wall for the players to see. In order for the players to see the bracket and the leaderboard, I open excel, then I open the Bracket.xlsm workbook, then the Leaderboard.xlsm workbook, and I drag those to the TV. I then open another instance of excel on my laptop, and open the Administration workbook. This stays on my laptop and the players cannot see it.

Here's what I want it to do:
In less words, the administration workbook controls the other two workbooks. Every time a player reports a win/loss, I enter that into the Administration workbook, and that needs to update both the bracket and the leaderboard, so the players can see it on the TV. So, basically, all the data is in the administration workbook, and the other two serve as displays for that data in a format the players can understand.

Here's my problem:
When I run a macro that is supposed to update the other two workbooks, I get an error: "Runtime Error '9': Subscript out of range." I believe this is because of the seperate instances of Excel. Since Bracket and Leaderboard are in a different instance than Administration, it can't 'see' those other two workbooks. Below is the part of the code that deals with these three workbooks:

Sub FillBracket()
'Define global variables for worksheets used in function
Dim MatchForm As Worksheet
    Set MatchForm = Workbooks("16_DE_Administration.xlsm").Worksheets("Report Match")
Dim PlayerDB As Worksheet
    Set PlayerDB = Workbooks("16_DE_Administration.xlsm").Worksheets("Player Database")
Dim WBracket As Worksheet
    Set WBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("W Bracket")
Dim LBracket As Worksheet
    Set LBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("L Bracket")
Dim FBracket As Worksheet
    Set FBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("Finals")
Dim Leaderboard As Worksheet
    Set Leaderboard = Workbooks("16_DE_Leaderboard.xlsm").Worksheets("Leaderboard")

Line 8 is the one that is highlighted when the debugger opens. I believe this is due to the seperate instances that I mentioned.

Do you guys have any thoughts about how I could remedy this issue? I thought about trying it in the same instance of Excel, but then I can't display anything on a seperate monitor, and I really don't want the administration tools to be visible to the players! The rest of my code appears to be valid, I just need the connection to the other instance of Excel to go through successfully.

THANK YOU FOR YOUR HELP!!!

-Tom-

 

[Norie]

Tom

How about not using multiple instances?

 

[Tom59593]

How clever!

Unfortunately, if I don't use multiple instance, I can't move the Bracket and Leaderboard workbooks to the second display (that is unless there is something I don't know -- very possible).

The reason I went to seperate instances is because I can drag an entire instance to the other monitor, as oposed to just keeping all the workbooks in one instance (which confines them to one monitor).

Any ideas?

THANKS!!!

-Tom-

 

[Norie]

Tom

I wasn't trying to be 'clever', it was just the first thing that popped into my head.

I've seen plenty of people having problem using multiple instances of Excel.

One of the most common thing was automating Excel from other applications and ending up with 'ghost' instances of Excel kicking about.

I'll admit I don't know much about working with multiple displays - I've got enough trouble working with my own machine.

 

[Legacy 98055]

You can pass references cross process but Excel does not register itself in the running object table. Workbooks do register themselves so this should work. Add this function:

Function GetWSRef(WorkBookFullName As String, WorkSheetName As String) As Worksheet
    Set GetWSRef = GetObject(WorkBookFullName).Parent.Worksheets(WorkSheetName)
End Function

Where "Parent" is the instance.
When you create a workbook, "Book1" for example. It is registered in the ROT as "Book1" until you save the workbook. There is no file extension because multiple formats are supported and a file extension cannot be assumed. Once saved, you must specify the full path for the GetObject funtion. So, in essense, as long as your "other App" workbooks have been saved, you should be fine with this syntax...

Set WBracket = GetWSRef("C:\Users\DeskTop\16_DE_Bracket.xlsm", "W Bracket")

Oviously you will need to replace "C:\Users\DeskTop\16_DE_Bracket.xlsm" with the fullname of your workbook...

Have a good weekend.

Tom

 

[Tom59593]

Hey Tom,

Thanks for the helpful reply! I created a new function called GetWsRef and copied and pasted the code you provided. I then inserted the second piece of code you provided into the routine that I was trying to run before.

I am still, however, getting an error when I run the routine. When I attempt to run, I get the following error: Run-time error '432': File name or class name not found during Automation operation.

Thinking that maybe my filename was incorrect, I double-checked spelling and everything, but found nothing wrong there. Below is my updated code, so feel free to add any insight!

Function GetWSRef(WorkBookFullName As String, WorkSheetName As String) As Worksheet
    Set GetWSRef = GetObject(WorkBookFullName).Parent.Worksheets(WorkSheetName)
End Function

Sub FillBracket()
'Define global variables for function
Dim MatchForm As Worksheet
    Set MatchForm = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Adminisration.xlsm", "Report Match")
Dim PlayerDB As Worksheet
    Set PlayerDB = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Administration.xlsm", "Player Database")
Dim WBracket As Worksheet
    Set WBracket = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Bracket.xlsm", "W Bracket")
Dim LBracket As Worksheet
    Set LBracket = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Bracket.xlsm", "L Bracket")
Dim FBracket As Worksheet
    Set FBracket = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Bracket.xlsm", "Finals")
Dim Leaderboard As Worksheet
    Set Leaderboard = GetWSRef("D:\Tournament Application\16 Player DE\16_DE_Leaderboard.xlsm", "Leaderboard")

THANKS FOR ALL YOUR HELP!!!

-Tom-

 

[Legacy 98055]

see http://support.microsoft.com/kb/238610

You do have the workbooks open. Correct?

 

[Tom59593]

Hey Tom,

Yes, the other windows are all open. In fact, I open them in the following order:

Instance 1:
16_DE_Bracket.xlsm
16_DE_Leaderboard.xlsm

Instance 2:
16_DE_Administration.xlsm

So I would assume that both of the files in Instance 1 are already registered in the ROT as they have lot focus by the time I open Instance 2...right? Now, I did notice in the support link you provided that Microsoft mentions the CreateObject command for VBA. How does this command work? Could I use this command to open the files in Instance 1 instead of manually opening them myself? Would this be a solution?

 

[Legacy 98055]

Sure. A better idea actually to instantiate your own instance with no need to use createobject. Just create a new instance of the application and then call it's workbooks.open method.

Example air code:
Dim xlApp as Application
Dim Bracket as WorkBook
Dim WBracket As Worksheet

Set xlApp = New Application
xlApp.Visible = True
xlApp.UserControl = True
Set Bracket = xlApp.WorkBooks.Open("D:\Tournament Application\16 Player DE\16_DE_Bracket.xlsm")
Set WBracket = Bracket.WorkSheets("W Bracket")

You answered your own question.

 

[Tom59593]

Hey Tom,

I altered the code you provided in your last post just a bit and it work splendidly! All I have to do now is open the Adminstration workbook, run this macro, and POOF!! Up comes another instance of Excel complete with the other two workbooks in it so that I can drag it to the other display!

I do have another question now, though. In other macros (not just the GenerateBracket() one), will I be able to reference these other workbooks without using the Workbooks.Open method? I still have other macros that need to communicate across the seperate instances of Excel, and I don't want to open up a new instance of Excel every time I run one of these macros. I was hoping I might be able to do something like this:

Dim WBracket as Worksheet
    Set WBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("W Bracket")

Will this work even though it's not in the same macro that created the new instance of Excel? I assume it would work fine since I already created the new instance of Excel using another macro, it should be 'registered' in the ROT, right? If this wouldn't work, do you have any ideas as to what I might be able to use instead of Workbooks.Open to be able to communicate across instances?

THANK YOU FOR ALL YOUR HELP!!!

-Tom-

Here is the code in its latest form:

Sub GenerateBracket()
'Create another instance of excel
Dim Administration As Workbook
    Set Administration = Workbooks("16_DE_Administration.xlsm")
Dim xlApp As Application
    Set xlApp = New Application
    xlApp.Visible = True
    xlApp.UserControl = True
Dim Bracket As Workbook
    Set Bracket = xlApp.Workbooks.Open("D:\Tournament Application\16 Player DE\16_DE_Bracket.xlsm")
Dim Leader As Workbook
    Set Leader = xlApp.Workbooks.Open("D:\Tournament Application\16 Player DE\16_DE_Leaderboard.xlsm")
 
'Define global variables for function
Dim MatchForm As Worksheet
    Set MatchForm = Administration.Worksheets("Report Match")
Dim PlayerDB As Worksheet
    Set PlayerDB = Administration.Worksheets("Player Database")
Dim AdminForm As Worksheet
    Set AdminForm = Administration.Worksheets("Administration")
Dim WBracket As Worksheet
    Set WBracket = Bracket.Worksheets("W Bracket")
Dim LBracket As Worksheet
    Set LBracket = Bracket.Worksheets("L Bracket")
Dim FBracket As Worksheet
    Set FBracket = Bracket.Worksheets("Finals")
Dim Leaderboard As Worksheet
    Set Leaderboard = Leader.Worksheets("Leaderboard")