Results 1 to 5 of 5
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    VBA to see if a Workbook is Open (97+)

    It's my annual "try to do some coding" time. Is there a better way to determine if a particular WB is open other than this snippet?

    Dim wbOpen As Workbook
    Dim boolAlreadyOpen As Boolean
    Dim strFileName As String
    Dim strFilePath As String
    strFileName = "MyFile.xls"
    strFilePath = "C:wherever"
    If Len(Dir(strFilePath & strFileName, vbDirectory)) > 0 Then ' file exists
    For Each wbOpen In Application.Workbooks
    If wbOpen.Name = strFileName Then boolAlreadyOpen = True
    Next wbOpen
    If Not boolAlreadyOpen Then _
    Workbooks.Open strFilePath & strFileName, ReadOnly:=True
    ...etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to see if a Workbook is Open (97+)

    One alternative is to let Excel figure it out. Costs you an On Error wich some of our "experts" won't like, but it is nice and modular and reusable. --Sam
    <pre>Option Explicit

    Sub test()
    Dim s As Variant
    For Each s In Array("foo", "Book2", "Book3", "Junk")
    If isWorkbookOpen(s) Then
    Workbooks(s).Activate
    MsgBox "Press OK to continus"
    End If
    Next s
    End Sub

    Function isWorkbookOpen(s) As Boolean
    Dim test As String
    On Error Resume Next
    test = Workbooks(s).Name
    If Err Then
    isWorkbookOpen = False
    Else
    isWorkbookOpen = True
    End If
    End Function</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to see if a Workbook is Open (97+)

    Here is how I do it:

    <pre>Dim oWB As Workbook
    Set oWB = Nothing
    On Error Resume Next
    Set oWB = Workbooks("Test.xls")
    On Error GoTo 0
    If oWB Is Nothing Then
    Set oWB = Workbooks.Open("C:WorkTest.xls")
    End If
    If oWB Is Nothing Then
    MsgBox "Can't open Test.xls"
    Exit Sub
    End If
    </pre>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to see if a Workbook is Open (97+)

    Well that's a relief, I figured Legare would holler at me for using an On Error. I guess us old guys think alike. <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to see if a Workbook is Open (97+)

    You would never see me holler about using On Error. I use it frequently in my code. If you understand how it works (somewhat complicated and now explained well in the help files), it is a great tool.
    Legare Coleman

Posting Permissions

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