Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro code for Closing a File

    I use a simple macro attached to a button to close a file. Sometimes I forget that I have already closed the file and click on the button again, bringing out the MS VBA dialog box "Run-time error '9': Subscript out of range".

    Sub CloseTempFile()
    Windows("_temp.xls").Activate
    ActiveWorkbook.Close
    End Sub

    What code can I insert into the macro to ignore the Activate command if the _temp.xls file is already closed? The only thing I could think of was Application.DisplayAlerts = False which does not do the job.

  2. #2
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Dear Arcturus16a,


    Try this code

    Code:
    Sub CheckWorkbookOpen()
    Dim oWB As Excel.Workbook
    For Each oWB In Application.Workbooks
    If oWB.Name = "_temp.xls" Then
    oWB.Close
    Exit For
    End If
    Next
    Set oWB = Nothing
    End Sub
    Thanks
    Farrukh

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks to you, Farrukh. Works like a charm. I think it may even be faster, too. (Though thats probably just a matter of perception).

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also just use a simple error override:
    Code:
    Sub CloseTempFile()
    On Error Resume Next
    Workbooks("_temp.xls").Close 
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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