_Communicating between 2 XL instances_ !

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
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 :

Code:
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 ?
 
L

Legacy 98055

Guest
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

Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
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.
 
L

Legacy 98055

Guest

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
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.
 
L

Legacy 98055

Guest

ADVERTISEMENT

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
 
L

Legacy 98055

Guest
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...

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.

rafaaj2000 said:
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
Right_Click said:
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 :unsure:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,537
Messages
5,572,782
Members
412,484
Latest member
deezina07
Top