MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Name of open workbooks?

Posted by salvatore on April 09, 2001 7:06 PM

Hello Everybody:
I want to know if there is a way to get the name (I don't care about filepath) of every open workbook and put them in any range. (If this is possible, I would like to refresh it in accordance with any change in conditions)
Thank You very much in advance.

Posted by Dave Hawley on April 09, 2001 10:14 PM

Hi Salvatore

This code will list the names of all open workbooks in Column A of sheet1

Sub CountOpenWorkbooks()
'Count of All open Workbooks
'Written by OzGrid Business Applications
Dim WBooks As Workbook
Dim ThisBook As String

'Clear column A ready for names

'Loop through and place all names in a list in Column A
For Each WBooks In Application.Workbooks
Sheet1.Range("A65536").End(xlUp).Offset(1, 0) = WBooks.Name
Next WBooks

End Sub

To make it update place this code in the module of "ThisWorkbook". Right click on the sheet picture, top left next to "File" and select "View Code" and paste in this code:

Private Sub Workbook_Activate()
Run "CountOpenWorkbooks"
End Sub


OzGrid Business Applications