Determine if an excel file is open

Posted on 2006-03-20
Last Modified: 2008-02-01

My Access 2000 VBA code opens up an Excel Spreadsheet, edits some data, then closes and saves the spreadsheet.  How can I determine if the Excel file is already in use (So I can abort) before I open it.

Question by:cdmac2
    LVL 2

    Accepted Solution

    To see if the file is open you could use the subroutine below:

    Public Function IsFileOpen(filename As String)
        Dim filenum As Integer, errnum As Integer

        On Error Resume Next   ' Turn error checking off.
        filenum = FreeFile()      ' Get a free file number.
        ' Attempt to open the file and lock it.
        Open filename For Input Lock Read As #filenum
        Close filenum                ' Close the file.
        errnum = Err                ' Save the error number that occurred.
        On Error GoTo 0            ' Turn error checking back on.

        ' Check to see which error occurred.
        Select Case errnum

            ' No error occurred.
            ' File is NOT already open by another user.
            Case 0
             IsFileOpen = False

            ' Error number for "Permission Denied."
            ' File is already opened by another user.
            Case 70
                IsFileOpen = True

            ' Another error occurred.
            Case Else
                Error errnum
        End Select

    End Function

    You would then just call it like this:

    If IsFileOpen("C:\Myfile.xls\") then
         'Abort Code
        'Run Other Code
    End If

    Hope this helps, and for more infor check out:


    LVL 1

    Author Comment

    Yep, that does it... thanks!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now