Get Name List of All Open Workbook Files

[princ_wns]

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

[Admin]

Hi

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

    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

[Excel Fox]

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

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

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

Msgbox wbk.Name

[princ_wns]

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

[Admin]

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.

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

MsgBox IsWorkbookOpened("MyWorkbook.xlsx")

[LalitPandey87]

Hi princ_wns

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

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