Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Checking to see if a workbook is open

  1. #1
    Board Regular hennahairgel's Avatar
    Join Date
    Feb 2002
    Location
    Bristol, England
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to check if a workbook is open, an if not open it. How do I go about doing that then?
    TIA
    Henry

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    There are several ways to do this - here is a function that you can use either in code or in a worksheet:-

    Function IsWorkbookOpen(WorkbookName As String) As Boolean
    Dim wb As Workbook

    For Each wb In Excel.Workbooks
    If UCase$(wb.Name) = UCase$(WorkbookName) Then
    IsWorkbookOpen = True
    Exit Function
    End If
    Next
    End Function


    You could use it like this in code:-

    Sub test()
    MsgBox IsWorkbookOpen("Yourworkbook.xls")
    End Sub

    or like this in a worksheet:-

    =IsWorkbookOpen("Yourworkbook.xls")

    Both will return TRUE if the workbook is open.

    HTH,
    D

    [ This Message was edited by: dk on 2002-02-20 04:38 ]

  3. #3

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another way :-

    On Error Resume Next
    Workbooks("TheWB.Xls").Activate
    If Err <> 0 Then Workbooks.Open("C:TheWB.xls")
    On Error GoTo 0

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I use this function. It's similar to the last one proposed, but I don't like Activating the workbook, because you would have to reactivate your "working" workbook. Try this.

    Code:
    Function WBisOpen(Bk As String) As Boolean
    Dim T As Workbook
    Err.Clear
    On Error Resume Next
    Set T = Workbooks(Bk)
    WBisOpen = Not (Err.Number > 0)
    Err.Clear
    On Error GoTo 0
    End Function
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    SRC
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just went for the simplistic approach...

    Sub TestCall()
    Dim TCount As Integer
    Dim TLoop As Integer

    TCount = Workbooks.Count
    For TLoop = 1 To TCount
    If Workbooks(TLoop).Name = "Calltest.xls" Then
    'code
    End If
    Next TLoop
    End Sub
    On 2002-02-20 05:53, Juan Pablo G. wrote:
    I use this function. It's similar to the last one proposed, but I don't like Activating the workbook, because you would have to reactivate your "working" workbook. Try this.

    Code:
    Function WBisOpen(Bk As String) As Boolean
    Dim T As Workbook
    Err.Clear
    On Error Resume Next
    Set T = Workbooks(Bk)
    WBisOpen = Not (Err.Number > 0)
    Err.Clear
    On Error GoTo 0
    End Function

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, simple, agree, but long and not very efficient...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Winnipeg, Manitoba, CANADA
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This function only works when you have 1 instance of Excel open.
    What if you have 2 or more instances of Excel? How can you check to see if the file is open?
    Thanx.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use GetObject to try to search for the workbook object.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change the directory path and workbook name to suit.


    Sub OpenIt()
    Dim MyDir As String
    MyDir = "C:My DocumentsTempParts1.xls"

    If Dir(MyDir) <> "" Then
    Workbooks.Open ("C:My DocumentsTempParts1.xls")

    End If

    End Sub



    Regards,

    Mike

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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