As far as I know you can bring other instances to the forefront, you can list
how many other instances are open and what their handles are BUT VBA doesn't
leap to the new instance and continue running.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
EXAMPLE:
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SwitchToThisWindow Lib "user32" ( _
ByVal hWnd As Long, BOOL As Boolean) As Long
'/========================================/
' Sub Purpose: loop through Excel instances
'/========================================/
'
Public Sub Loop_Thru_Excel_Instances()
Dim varAry()
Dim iInstances As Long 'count of instances for re-dimming
Dim hWndDesk As Long 'Windows handle
Dim hWndXL As Long 'Excel handle
Dim x As Long 'general purpose LONG variable
Dim var As Variant 'general purpose VARIANT variable
On Error GoTo err_Sub
'- - - - - - - - -
'FIND OUT HOW MANY INSTANCES ARE OPEN AND PUT IN ARRAY
'Get a handle for the desktop
hWndDesk = GetDesktopWindow
Do
iInstances = iInstances + 1
hWndXL = FindWindowEx(GetDesktopWindow, hWndXL, _
"XLMAIN", vbNullString)
If hWndXL <> 0 Then
ReDim Preserve varAry(iInstances)
'Get the next Excel window
varAry(iInstances) = hWndXL
Else
Exit Do
End If
Loop
'- - - - - - - - -
'example of how to loop thru each instance
For x = 1 To UBound(varAry)
var = SwitchToThisWindow(hWnd:=varAry(x), BOOL:=False)
Next x
'- - - - - - - - -
'example of how to go to the 1st instance
var = SwitchToThisWindow(hWnd:=varAry(1), BOOL:=False)
'- - - - - - - - -
exit_Sub:
On Error Resume Next
Exit Sub
err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Loop_Thru_Excel_Instances - Module: " & _
"Mod_Instance_testing - " & Now()
GoTo exit_Sub
End Sub
'/========================================/
