Determine if an excel file is open

Hello,

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.

Thanks!
MikeMc
LVL 1
cdmac2Asked:
Who is Participating?
 
jg0069_2002Commented:
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
Else
    'Run Other Code
End If

Hope this helps, and for more infor check out: http://support.microsoft.com/?kbid=213383

JRG

0
 
cdmac2Author Commented:
Yep, that does it... thanks!

-Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.