Excel TWO Instances of Excel One watching Other


Joined
Feb 21, 2018
Messages
216
Reaction score
86
Hi,

I have two excel instances running in memory.
In one instance I have loaded a macro-enabled file File_A.xlsb

While remaining in this excel instance I want to do the following:
A) see the other(B) instance of the excel and force execute a macro ABC which is already available in that instance.

I am doing this but getting stuck up in the last line of the code(i.e Run xl.ActiveWorkbook.FullName & "!ABC").

Dim xl As Application
For Each xl In GetExcelInstances()
If Right(xl.ActiveWorkbook.FullName, 11) = "Master.xlsb" Then
Run xl.ActiveWorkbook.FullName & "!ABC"
End If
Next

The error as popped up on the screen say:
Cannot run the macro 'D:\Amjad\MyFile.xlsb!ABC'. The macro may not be available in this workbook or all macros may be disabled.
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
Hi Friends,

On Error Resume Next
is such a great thing in Excel VBA that it simply helped me to establish continuity of my watch-dog.

See how simple this is:

My Watch-dog (Excel/VBA) is executing repeatedly executing these few lines of VBA code:

The Question:
What is it Watching?
Its watching a macro-enabled xlsb file openned in a separate Excel Shell. That file is keeping an eye on incoming emails (gmail or Outlook). if a registered user sends a query with a certain predetermined Subject it extracts the desired records from the database and sends to the user.

If due to some unforeseen reason this mechanism fails (i.e its macro stops) then the Watch-dog gets activated and re-executes the macro....

Got it?

Sub Test()
Dim xl As Application
For Each xl In GetExcelInstances()
If Right(xl.ActiveWorkbook.FullName, 42) = "xrun-code-every-hour-minute-or-second.xlsb" Then
tm_chk = Right(xl.ActiveWorkbook.Sheets("Sheet1").Range("D10"), 8)
tm = Right(Format(Time, "HH:MM AM/PM"), 8)
tdiff = DateDiff("n", TimeValue(tm), TimeValue(tm_chk))
If Abs(tdiff) > 1 Then

On Error Resume Next
xl.Run "Auto_Open"
On Error GoTo 0

End If
End If
Next
End Sub

Public Function GetExcelInstances() As Collection
Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
guid(0) = &H20400
guid(1) = &H0
guid(2) = &HC0
guid(3) = &H46000000

Set GetExcelInstances = New Collection
Do
hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
If hwnd = 0 Then Exit Do
hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0&, guid(0), acc) = 0 Then
GetExcelInstances.Add acc.Application
End If
Loop
End Function
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
My Screen illustration

upload_2018-8-24_16-54-43.png
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top