List Open Workbooks In All Excel Instances?

G

George Lee

How I can get a list of all the open workbooks from among all the running
Excel instances?
 
G

Gary Brown

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

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

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