Get Name List of All Open Workbook Files

hi all,

How can i get the name of all opened (excel) file . I have to list the name of all file and check whether the the file which is required by program is open or not.what should be its vba code to do this?


thanks in advance

Hi

This is not the answer to your thread title, but give this a try

Code:

---

    Dim wbkOpened      As Workbook
   
    On Error Resume Next
    Set wbkOpened = Workbooks("YourWorkbookName.xls")
    On Error GoTo 0
   
    If wbkOpened Is Nothing Then
        Set wbkOpened = Workbooks.Open("WorkbookFullName")
    End If

---

If you to check if a particular file you are looking for is currently open, use this function

Code:

---

Function IsBookOpen(strNameOfFile As String) As Boolean
    Dim wbk As Workbook
   
    For Each wbk In Application.Workbooks
        If InStr(1, wbk.Name, strNameOfFile) Then
            IsBookOpen = True: Exit For
        End If
    Next wbk
   
End Function

---

and call it like this

Code:

---

IsBookOpen "MyBook.xlsx"

---

If you want a list of all open workbooks, try modifying by taking out the if condition and printing out the names of each workbook like for example

Code:

---

Msgbox wbk.Name

---

Thanks to all for their reply, the last reply is exactly same , i was looking for.

How would you say my code is not working the same way you were looking for ?

BTW, you could use as a function as well.

Code:

---

Function IsWorkbookOpened(ByVal WorkbookName As String) As Boolean
   
    Dim wbkOpened    As Workbook
   
    On Error Resume Next
    Set wbkOpened = Workbooks(CStr(WorkbookName))
    On Error GoTo 0
   
    IsWorkbookOpened = Not wbkOpened Is Nothing
   
End Function

---

and call like

Code:

---

MsgBox IsWorkbookOpened("MyWorkbook.xlsx")

---

Hi princ_wns

Here is a code which gives the List of All Open Workbook Files in the same instance.

Code:

---

Sub ListOpenBooks()
'lists each book that's OPEN
    Dim wb As Workbook

    For Each wb In Application.Workbooks
          MsgBox wb.Name
    Next wb
End Sub

---

:cheers: