Detect All Opened Workbooks (Unsaved or not)

[ShieBoon]

Hi all, i am trying to populate a listbox with all the opened workbooks. But i can't seem to be able to do that.

These are my codes

Sub test()
Dim wb As Workbook
Dim myArray() As Variant
Dim counter As Variant
Load frm_SelectPrompt
For Each wb In Application.Workbooks
    frm_SelectPrompt.lst_SPSelWB.AddItem (wb.Name)
Next
frm_SelectPrompt.Show
End Sub

Thanks
Excel 2003
Shie Boon

 

[Leith Ross]

Hello ShieBoon,

What are the results of your code? Are you getting any workbooks in list?

Sincerely,
Leith Ross

 

[ShieBoon]

Hi Leith,

Yup, i am, but just one. Let's say my workbook's name is MyApple.xls. It is a saved workbook with a filepath. That appears in the list.

But when i opened new workbooks with the default name of 'Book1', 'Book2' etc, and i run the macro, they don't appear. But MyApple.xls does.

I was thinking that maybe it's because the new workbooks haven't been saved. So i tried saving them and ran the macro again, but the results are still the same. Puzzled.

Thanks,
Shie Boon

 

[Leith Ross]

Hello Shie,

The workbook should be in the Workbooks collection whether is has been saved or not. Are all your workbooks open in a single instance of Excel or is each workbook open in own copy of Excel? Are you using a Mac or PC?

 

[ShieBoon]

Hi Leith,

I'm using a PC and.. Each workbook is a single instance of Excel. Hm.. :| Anyway, thank you so much for your help

Thanks,
Shie Boon

 

[Jaafar Tribak]

Put the code below in a standard module and run the Test Macro to populate ListBox1 in the activesheet with all the open workbooks in all excel instannces :

Option Explicit
 
Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type
 
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 Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" _
(ByVal hwnd As Long, _
ByVal dwId As Long, _
riid As GUID, _
ppvObject As Any)
 
Private Const OBJID_NATIVEOM = &HFFFFFFF0
 
Private Sub SetIDispatch(ByRef ID As GUID)
 
    ' IDispatch Interface.
    ' {00020400-0000-0000-C000-000000000046}.
    With ID
        .lData1 = &H20400
        .iData2 = &H0
        .iData3 = &H0
        .aBData4(0) = &HC0
        .aBData4(1) = &H0
        .aBData4(2) = &H0
        .aBData4(3) = &H0
        .aBData4(4) = &H0
        .aBData4(5) = &H0
        .aBData4(6) = &H0
        .aBData4(7) = &H46
    End With
 
End Sub
 
Private Sub Get_XL_APP_Collection(ByRef Col As Collection)
 
    Dim IDispatch As GUID
    Dim oWB As Object
    Dim lXLhwnd As Long
    Dim lXLDESKhwnd As Long
    Dim lWBhwnd As Long
 
    Do
        lXLhwnd = FindWindowEx(0, lXLhwnd, "XLMAIN", vbNullString)
        If lXLhwnd = 0 Then
            Exit Do
        Else
            lXLDESKhwnd = FindWindowEx(lXLhwnd, 0&, "XLDESK", vbNullString)
            lWBhwnd = FindWindowEx(lXLDESKhwnd, 0&, "EXCEL7", vbNullString)
            If lWBhwnd Then
                SetIDispatch IDispatch
                Call AccessibleObjectFromWindow _
                (lWBhwnd, OBJID_NATIVEOM, IDispatch, oWB)
                   Col.Add oWB.Application
            End If
        End If
    Loop
 
    Set oWB = Nothing
 
End Sub


Sub test()

    Dim XL_Col As New Collection
    Dim i As Integer
    Dim j As Integer
    
    Get_XL_APP_Collection XL_Col
    
    For i = 1 To XL_Col.Count
        For j = 1 To XL_Col.Item(i).Workbooks.Count
            ActiveSheet.ListBox1.AddItem "Excel Instance : (" & i & ") " & _
            " Workbook : (" & XL_Col.Item(i).Workbooks(j).Name & ")"
        Next j
    Next i

End Sub

 

[ShieBoon]

Hey Jaafar,

Firstly thank you for the reply! Excel's able to detect the workbooks already so i'm so sorry!

Thanks
Shie Boon