Excel VBA Counting and Storing List of All Open Workbooks

Question

I am looking to try and count the current number of workbooks that are opened. Workbooks.Count will not work because not all of the files are in the same Excel application instance. When the file is opened after being downloaded, its in a new instance of excel, so Workbooks does not include it.

Is there a way to get the count of all workbooks for every instance of Excel that is running?

I tried `For Each b In Application.Workbooks MsgBox (CStr(b.Name)) Next b` but no luck, stil only shows the workbook with the button. — Zionmoose, Dec 02 '15 at 19:23
Are the workbooks open in different applications? (For example, often a downloaded workbook will open up in a completely different excel application) — Demetri, Dec 02 '15 at 19:24
@Demetri Yes. Can it not count them if its different excels that are open? If not is there a way to make it get all of them if this happens? — Zionmoose, Dec 02 '15 at 19:26
You are right. When I opened them all under 1 Excel application it counts them all. There has to be a way to get the workbooks from all open applications. — Zionmoose, Dec 02 '15 at 19:29
As far as I know, there is no easy way to count the workbooks when they are in different applications. There may be a way using functions that look at windows, but they are quite advanced. (FindWindowEx, GetWindowText, GetClassName) — Demetri, Dec 02 '15 at 19:30
Check [here](http://excelribbon.tips.net/T009452_Finding_Other_Instances_of_Excel_in_a_Macro.html) — findwindow, Dec 02 '15 at 19:37
Found this as a good answer. [here](http://stackoverflow.com/a/3303016/2131749) — Zionmoose, Dec 02 '15 at 20:06

Patrick Lepelletier · Answer 1 · 2017-12-20T13:00:52.400

here is an example how it can be done (no API):

Public Function ListAllExcelWB(Optional ByVal SearchFor$) As Variant() 'exept this one
On Error Resume Next

 If SearchFor = vbNullString Then SearchFor = "*.*"

Dim xl As Excel.Application
Dim Wb As Workbook
Dim Status As Long
Dim Arr()
ReDim Arr(1 to 2, 1 To 100)
Dim i&

Do
  Err.Clear
  Set xl = GetObject(, "Excel.Application")
  Status = Err.Number
  If Status = 0 Then
      'xl.Visible = True
      For Each Wb In xl.Workbooks
            With Wb
                  If .Name <> ThisWorkbook.Name Then
                    i = i + 1
                    Arr(1, i) = .Name 
                    Arr(2, i) = .Path & "\" & .Name
                    If i > 99 Then Status = 1
                  Else: Status = 1 'stoppe la loop si se trouve soi meme, car au niveau des stack, l'instance active est la derniere
                  End If
            End With
      Next

  End If
Loop Until Status <> 0 '= 429

If i > 0 Then
      ReDim Preserve Arr(1 to 2,1 To i)
Else: Erase Arr 'ReDim Arr(0 To 0)
End If

Err.Clear: On Error GoTo 0

Set xl = Nothing: Set Wb = Nothing
ListAllExcelWB = Arr
Erase Arr
End Function

Friendly hint: you redimmed Arr(1 To 100, 1 To 2), but assign your 1-100 items to the 2nd dimension; think this to be correct: `Arr(i, 1) = .Name` and `Arr(i, 2) = .Path & "\" & .Name`. BTW `SearchFor$` is not needed in this example. — T.M., Dec 18 '17 at 14:06
yeah, i know, it was re-used code that i changed to my needs, the outcome had to be index in first dimension, wich is not practical generaly speaking because of redim not working unless last dimension. — Patrick Lepelletier, Dec 20 '17 at 00:53

Excel VBA Counting and Storing List of All Open Workbooks

1 Answers1