Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open File If Not Already Open (Office97 SR2)

    Hi:
    I have another small question. I know that the following code works for closing a file, if it is open:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    WorkBooks("MyWkBook").Close
    On Error GOTO 0
    End Sub

    How would I apply this to Opening a File If it is Not Already Open. Like what is missing from the following:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Windows("MyWkBk.xls").Activate
    ChDir "S:ShareMyDirectory"
    WorkBooks.Open FileName:="DATA1ShareMyDirectoryMyWkBk.xls"
    Windows("MyOtherWkBook.xls").Activate
    End Sub

    Do I need " On Error GOTO 0" before the End Sub OR am I not even on the right track??
    Thanks <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: Open File If Not Already Open (Office97 SR2)

    I would do something like this:

    <pre>Dim oWB As Workbook
    On Error Resume Next
    Set oWB = Workbooks("MyWkBk.xls")
    On Error GoTo 0
    If oWB Is Nothing Then
    Workbooks.Open "S:ShareMyDirectoryMyWkBk.xls"
    End If
    </pre>

    Legare Coleman

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

    Re: Open File If Not Already Open (Office97 SR2)

    In messing with this I note that if you open an Excel workbook through VBA, even when it is already open, it becomes the active workbook. Therefore, wouldn't:

    On Error Resume Next
    Workbooks.Open ("d:mypath" & "myfile.xls")
    If Err <> 0 Then
    MsgBox "No such file", vbInformation ' or other action
    End If

    be all you need, and what are the dangers of doing it this way? Regards,
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open File If Not Already Open (Office97 SR2)

    John,

    When you try to open file that is already open by use of the menu commands File Open, Excel actually executes the command Windows("FileName").Activate. However if you are using Workbooks.Open in VBA code Excel does actually try to open the file causing a possible problem to arise if alterations had been made to the workbook, as it would prompt if it should revert to the saved version. If DisplayAlerts is set to false then it would revert without notification, and any changes would be lost.

    Andrew

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

    Re: Open File If Not Already Open (Office97 SR2)

    <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15> Thanks, good to know.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Open File If Not Already Open (Office97 SR2)

    Andrew has already answered this better than I could.
    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
  •