Results 1 to 6 of 6

Thread: Get Name List of All Open Workbook Files

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

    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
    Last edited by princ_wns; 04-05-2012 at 09:59 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    Thanks to all for their reply, the last reply is exactly same , i was looking for.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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

Similar Threads

  1. Open And Activate Workbook Before Runing Macro
    By Howardc in forum Excel Help
    Replies: 5
    Last Post: 06-04-2013, 07:23 PM
  2. VBA Code to Open Workbook and copy data
    By Howardc in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 06:58 PM
  3. Replies: 8
    Last Post: 08-05-2012, 10:07 AM
  4. Assign an event to chart on workbook open
    By LalitPandey87 in forum Excel Help
    Replies: 2
    Last Post: 02-20-2012, 07:43 AM
  5. List Of All Files In A Folder
    By Excel Fox in forum Excel Help
    Replies: 2
    Last Post: 10-27-2011, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •