|  

» Determine if a file is in use using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
With the function below you can determine if a file is in use by another process.
The function returns True if you can't get full access to the file.

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:\FolderName\FileName.xls") Then...
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
        'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
    Else
        FileAlreadyOpen = False
    End If
    On Error GoTo 0
End Function


Rate This Tip
12 34 5
Rating: 3.68     Views: 18207
No comments have been submitted.
Click here to post comment
For Registered Users
Name
Comment Title
Comments