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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cdmac2Author Commented:
Yep, that does it... thanks!

-Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.