none
check if workbook is open

    Question

  • What is the best way to check if a workbook, given its name, is open?

    the workbooks(name).isopen does not work

     

    I am doing it by comparing the name with all the names of the open workbooks but this is not very efficient in my opinion.

    '    For Each w In Workbooks
    '        If UCase(w.Name) = "TEST_NAME.XLS" Then
    '       .....
    '        End If

    Thanks,

     

    Antonio

    Thursday, April 06, 2006 3:35 PM

Answers

  • How you doin' Antonio ?

    Here's some code that might be considered more efficient although it calls On Error Resume Next which isn't recommended.

        On Error Resume Next
        If Application.Workbooks("Book2") Is Nothing Then
            MsgBox ("Does Not Exist")
        Else
            MsgBox ("Exists")
        End If

    It works. If you use it put it in a function on it's own like this.

    Public Function WorkbookExists(WorkbookName As String) As Boolean
    On Error Resume Next
        If Application.Workbooks(WorkbookName) Is Nothing Then
            WorkbookExists = False
        Else
            WorkbookExists = True
        End If
    End Function

    Don't put any other code in the function.

    The code uses the fact that an error occurs if you reference a workbook that doesn't exist and returns false.

    Thursday, April 06, 2006 6:15 PM

All replies

  • How you doin' Antonio ?

    Here's some code that might be considered more efficient although it calls On Error Resume Next which isn't recommended.

        On Error Resume Next
        If Application.Workbooks("Book2") Is Nothing Then
            MsgBox ("Does Not Exist")
        Else
            MsgBox ("Exists")
        End If

    It works. If you use it put it in a function on it's own like this.

    Public Function WorkbookExists(WorkbookName As String) As Boolean
    On Error Resume Next
        If Application.Workbooks(WorkbookName) Is Nothing Then
            WorkbookExists = False
        Else
            WorkbookExists = True
        End If
    End Function

    Don't put any other code in the function.

    The code uses the fact that an error occurs if you reference a workbook that doesn't exist and returns false.

    Thursday, April 06, 2006 6:15 PM
  • Hi Derek, I am doing great and truly enjoying the coding.

    I was reluctant to check for error in case I miss a different type of error, but I agree that it is safe and is probably the only quick way.

     

    Thank you for your code that is helpful and that I will use.

     

    Regards,

     

    Antonio

    Thursday, April 06, 2006 6:30 PM