Cbrine.
You can get a specific instance of Excel if you know the path of any opened workbook located within that instance. Excel instances do not register themselves in the ROT (Running Object Table) but workbooks do. This was sort of covered in
this post.
The OP is looking for an OOPS (out of process server). A dll is an (in process server) and runs in the same processor (processor time slice space) as it's hosting application. An OOPS executes independantly on a seperate thread and, therefore, does not tie up the thread of the instance being served. This is what an ActiveX.exe accomplishes in VB. You can get the same effect in Excel by creating another instance and using callbacks. See this post,
"asynchronous communication between Excel instances", for a simplified example.
Another option that I would look into if you have access to a COM capable, professional development environment is
How To Create a RealTimeData Server for Excel.
The instance should be, if at all possible, isntantiated explicitly as per your example. However, if you wish to create a local, full time server, you can get a reference to an "already existing" instance using getobject.
Create a new folder. Create, save, and close three blank workbooks. Book1.xls, Book2.xls, and Book3.xls. Place these in your new folder. The location is irrelevant. This is just to simplify the example.
open Book1.xls. Place this code within.
Code:
Sub Example()
Dim Book2Path As String, Book3Path As String
Dim Book2ParentInstance As Application
Dim Book3ParentInstance As Object
'assumes that Book2 and Book3 are in the same path as thisworkbook
'open two additional, separate instances of Excel
With ThisWorkbook
Book2Path = Replace(.FullName, .Name, "Book2.xls")
Book3Path = Replace(.FullName, .Name, "Book3.xls")
Shell Application.Path & "\Excel.exe " & Chr(34) & Book2Path & Chr(34)
Shell Application.Path & "\Excel.exe " & Chr(34) & Book3Path & Chr(34)
End With
Set Book2ParentInstance = GetObject(Book2Path).Parent
Set Book3ParentInstance = GetObject(Book3Path).Parent
'prove that we have a reference to the shelled app objects
'this application
Debug.Print ThisWorkbook.Parent.Hwnd
'book2's parent application
Debug.Print Book2ParentInstance.Hwnd
'book3's parent application
Debug.Print Book3ParentInstance.Hwnd
Debug.Print "Now let's close the two newly opened instances..."
Stop
Book2ParentInstance.Quit
Book3ParentInstance.Quit
End Sub
You might have a method in one of your server instances that recieves a reference from the calling application. This could be used for a callback, or in the op's solution, asynchronous notification. I would create a timer in the server instance and then callback a method in the caller. Once again, see this post.
"asynchronous communication between Excel instances"
GetInstanceExample.zip
Here is an example of creating a simple server using an instance of Excel. You will need to add more sophisticated code that would make the instance exclusive to the workbook running the "serving" code. Perhaps you might adapt the method used in
this post?
Anyway, create a workbook named "MyServer.xls", paste this code into thisworkbook class...
Code:
Option Explicit
'must use latebinding by way of a collection
Private WorkbookInstances As Collection
Private DemoCounter As Double
Private NextOnTimeTime As Double
Private Sub Workbook_Open()
Application.UserControl = False
NextOnTimeTime = Now + TimeSerial(0, 0, 1)
Application.OnTime NextOnTimeTime, "ThisWorkbook.ReturningYourCall"
Application.Visible = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime NextOnTimeTime, "ThisWorkbook.ReturningYourCall", , False
End Sub
Public Sub PleaseCallMeBack(CallerReference As Object)
If WorkbookInstances Is Nothing Then Set WorkbookInstances = New Collection
WorkbookInstances.Add CallerReference
End Sub
Friend Sub ReturningYourCall()
On Error Resume Next
DemoCounter = DemoCounter + 1
NextOnTimeTime = Now + TimeSerial(0, 0, 1)
Application.OnTime NextOnTimeTime, "ThisWorkbook.ReturningYourCall"
Dim Cntr As Long
If Not WorkbookInstances Is Nothing Then
For Cntr = 1 To WorkbookInstances.Count
Call WorkbookInstances(Cntr).AnswerThePhone(DemoCounter)
Next
End If
End Sub
Save, close, and fire it up in it's own instance. All it does is run a procedure one per second. Now, from any other workbook in any instance, place this code in Thisworkbook class:
Code:
Sub SetUpCallback()
GetObject("C:\Documents and Settings\Tom\Desktop\MyServer.xls").PleaseCallMeBack Me
End Sub
Public Sub AnswerThePhone(Ringing As Double)
thisworkbook.sheets(1).Range("A1").Value = Ringing
End Sub
There are only two requirements.
1. You must know the full path of MyServer.xls
2. The subscribing workbook(s) must contain a public method to callback.
In this case, the method is AnswerThePhone. I hope this explains it better...
