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
:cheers: