?
Solved

Determine if an excel file is open

Posted on 2006-03-20
2
Medium Priority
?
213 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:cdmac2
2 Comments
 
LVL 2

Accepted Solution

by:
jg0069_2002 earned 2000 total points
ID: 16244258
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
 
LVL 1

Author Comment

by:cdmac2
ID: 16246235
Yep, that does it... thanks!

-Mike
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 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