Prev: Macro for Deleting rows with balnk cell
Next: Find and Replace
From: George Lee on 2 Jun 2010 11:21
How I can get a list of all the open workbooks from among all the running
Excel instances?
From: Gary Brown on 3 Jun 2010 16:22
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

'/========================================/




"George Lee" wrote:

> How I can get a list of all the open workbooks from among all the running
> Excel instances?
 | 
Pages: 1
Prev: Macro for Deleting rows with balnk cell
Next: Find and Replace