_Communicating between 2 XL instances_ !

[Jaafar Tribak]

I have two instances of XL running at the same time.

Instance A has workBook A loaded.

Instance B has WorkBook B loaded.

How do I get ,via code, the current value of Range("A1") in Sheet1 in Workbook B from WorkBook A ?

Any idea how to best achieve this ?

Regards.

 

[Jaafar Tribak]

Ok,

I found something intersting . I can use the GetObject Method if I hard code the Workbook Path ( in this case is the B Wbk) as follows :

Sub TEST()

    '\\ Calling code from WB A in XL Instance A
    Dim objXLInstance As Workbook
    Set objXLInstance = GetObject("C:\B.XLS")
    MsgBox objXLInstance.Sheets(1).Range("A1")

End Sub

This is not bad but I would like reference an open WBK in a different XL instance without knowing its file path so it could flexibly apply to any open Wbk.

It would look something like:

XLInstanceB.ActiveWorkBook.ActiveSheet.Range("a1").value

Any ideas ?

 

[Legacy 98055]

Well Jaafar...

I cannot get this to work unless the workbook has not been saved in the second instance...

From...
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288902

Sub Example()
    Dim app As Excel.Application
    
    'seeing that you have multiple instances of Excel running
    'you must qualify which instance to get a reference
    'to by explicitly referring to one of the open
    'workbooks in the instance in question.  Else
    'GetObject gets a reference in the order that
    'each instance was created
    Set app = ReferenceInstance("Workbook B")
    
End Sub


Function ReferenceInstance(WorkbookName As String) As Excel.Application
    On Error Resume Next
    Set ReferenceInstance = GetObject(WorkbookName).Application
End Function

Tom

 

[Jaafar Tribak]

Thanks Tom,

'seeing that you have multiple instances of Excel running
'you must qualify which instance to get a reference
'to by explicitly referring to one of the open
'workbooks in the instance in question. Else
'GetObject gets a reference in the order that
'each instance was created

Contrary to what it is said in the link, I have to write the whole file path (Not just the name ) to reference the other XL instance .

I am still trying to reference the other Instance without providing the Workbook name so that I can refer to Range("a1") in whichever workbook happens to be active.


Regards.

 

[Legacy 98055]

It does actually work if the workbook has not been saved as of yet. For example: Open up your second instance, create a new workbook, do not save it. You will note that the xls extension is not in Excel's titlebar. Now reference the name of the workbook in your first instance. It will work without the path but simply the name. Excel does not register it'self as an ActiveX server in the ROT until it loses focus. I tried to accommodate for this, but failed.

Tom

 

[Jaafar Tribak]

Tom,

Yes I noticed that but like I said it works ok if the workbook' full path is passed to the GetObject Method.

This is maddening, I can't seem to be able to reference the other Xl instance without passing the fullpath which raises the following question :

How can I retrieve the path of a workbook that is open in another XL instance ?
If I get an answer to the this question the problem is then solved.

I can get the foreign XL window hwnd and caption as well as that of all the open workbooks. I wonder if there are any APIs that return the XL application object if passed any of the above items.Once we get the App object we can easily solve the problem by using automation.

Regards.

 

[Legacy 98055]

Jaafar. I like your challenging posts and searched the net like the hound of heaven and could not find anything for VB or VBA. There seemed to be some hope in VB.Net, a handful of examples in C, C++, Java, Delphi, ect..., but I was looking for API functions that would allow you to iterate through the objects listed in the running object table and get your reference from there. You may want to check out the COM SDK. I'm not going into COM and know very little about the lower level coding for COM. Will still look into a dirty hack as always.

Tom

 

[Legacy 98055]

Slimy Hack!

Still needs some tweaking, or a makeover, or even a miracle or two.
Put in workbook A and run example after having edited the workbook name arguments...

Sub Example()
    Dim WorkbookInOtherInstance As Workbook
    Dim WorkbookShortName As String
    
    WorkbookShortName = "Workbook B"
    Set WorkbookInOtherInstance = ReferenceInstance(GuessPathFromName(WorkbookShortName), WorkbookShortName)
    
    If WorkbookInOtherInstance Is Nothing Then
        MsgBox "Not located or does not exist..."
        Exit Sub
    End If
    
    WorkbookInOtherInstance.Sheets(1).Range("A1") = "Did it work?"
End Sub


Function ReferenceInstance(WorkBookPath As String, WorkBookIndex As String) As Excel.Workbook
    Dim AppInstance As Object, WbCnt As Integer
    On Error Resume Next
          
    WbCnt = Workbooks.Count
    Set AppInstance = GetObject(WorkBookPath).Application
    If AppInstance Is Nothing Then
        Set AppInstance = GetObject(WorkBookIndex).Application
    End If
    If AppInstance Is Nothing Then
        Set AppInstance = GetObject(WorkBookIndex & ".xls").Application.Workbooks(WorkBookIndex & ".xls")
    End If
    If TypeName(AppInstance) = "Workbook" Then
        WorkBookPath = AppInstance.FullName
        Set AppInstance = GetObject(WorkBookPath).Application
        If Workbooks.Count > WbCnt Then Workbooks.Item(Workbooks.Count).Close
    End If

    Debug.Print TypeName(AppInstance)
    If TypeName(AppInstance) = "Application" Then
        If AppInstance.Hwnd <> Application.Hwnd Then
            Set ReferenceInstance = AppInstance.Workbooks(WorkBookIndex)
            If ReferenceInstance Is Nothing Then
                Set ReferenceInstance = AppInstance.Workbooks(WorkBookIndex & ".xls")
            End If
            Exit Function
        Else
            
            If Workbooks.Count > WbCnt Then Workbooks.Item(Workbooks.Count).Close
        End If
    End If
    
End Function

Function GuessPathFromName(WorkBookName As String) As String
    Dim x As Long
    
    For x = 1 To Application.RecentFiles.Count
    Debug.Print Application.RecentFiles(x).Path
        GuessPathFromName = Application.RecentFiles(x).Path
        If InStr(GuessPathFromName, WorkBookName) <> 0 Then
            Exit Function
        End If
    Next
    GuessPathFromName = ""
End Function

Tom

 

[tusharm]

I'm getting ready for a flight and can't research this in detail. Of course, I am sure you will fill in the blanks yourself given a pointer or two...

IIRC Stephen Bullen has shown how to get a reference to another instance of XL. Search google.com for the XL newsgroup archives.

FWIR he had used an API (it's the standard API everyone who wants to find running apps uses) that returns a reference to each running application (stack?). Then, you would figure out which of the instances is the one you want.

Tom,

Yes I noticed that but like I said it works ok if the workbook' full path is passed to the GetObject Method.

This is maddening, I can't seem to be able to reference the other Xl instance without passing the fullpath which raises the following question :

How can I retrieve the path of a workbook that is open in another XL instance ?
If I get an answer to the this question the problem is then solved.

I can get the foreign XL window hwnd and caption as well as that of all the open workbooks. I wonder if there are any APIs that return the XL application object if passed any of the above items.Once we get the App object we can easily solve the problem by using automation.

Regards.

 

[Jaafar Tribak]

Jaafar. I like your challenging posts and searched the net like the hound of heaven and could not find anything for VB or VBA. There seemed to be some hope in VB.Net, a handful of examples in C, C++, Java, Delphi, ect..., but I was looking for API functions that would allow you to iterate through the objects listed in the running object table and get your reference from there. You may want to check out the COM SDK. I'm not going into COM and know very little about the lower level coding for COM. Will still look into a dirty hack as always.

Tom

Thanks for your interest.

I too searched the net and reference books but found very little

One undocumented API : NtQuerySystemInformation may be of use but is quite complicated and all the code samples I have seen so far are written in C .

Anyway , I'll carry on looking into this and if I come up with something I'll post it here.

By the way the code above didn't really work for me .

Thanks for your help.