How to identify the file is using bt other people when trying to delete it in Excel Macro

See the part that need help below:

For N = 1 To 25
        FileToRemove = Offices.Cells(N, 2).Text & "\" & Range("OfficeFolder").Text & "\" & FileName
        Kill (FileToRemove)

        If ???File is using by other people??? Then   ' need help for the coding!!

            Comments.Cells(N, 1).Value = "File unremoved"
            GoTo Jump
        End If
Jump:
    Next N
jjxia2001Asked:
Who is Participating?
 
DaveCommented:
So for your code something like this

Cheers

Dave
Sub B()
    For N = 1 To 25
        fileToRemove = Offices.Cells(N, 2).Text & "\" & Range("OfficeFolder").Text & "\" & FileName
        If Not IsFileOpen(fileToRemove) Then
            Kill (fileToRemove)
            Comments.Cells(N, 1).Value = "File removed"
        Else
            Comments.Cells(N, 1).Value = "File was open - not deleted"
        End If
    Next N
End Sub


Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long

    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0

    Select Case iErr
    Case 0: IsFileOpen = False
    Case 70: IsFileOpen = True
    Case Else: Error iErr
    End Select

End Function

Open in new window

0
 
DaveCommented:
The normal way is to try to access the file

See Bob Phillips's sample here
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

Cheers

Dave
Function IsFileOpen(FileName As String) 
    Dim iFilenum As Long 
    Dim iErr As Long 
     
    On Error Resume Next 
    iFilenum = FreeFile() 
    Open FileName For Input Lock Read As #iFilenum 
    Close iFilenum 
    iErr = Err 
    On Error Goto 0 
     
    Select Case iErr 
    Case 0:    IsFileOpen = False 
    Case 70:   IsFileOpen = True 
    Case Else: Error iErr 
    End Select 
     
End Function 
 
Sub test() 
    If Not IsFileOpen("C:\MyTest\volker2.xls") Then 
        Workbooks.Open "C:\MyTest\volker2.xls" 
    End If 
End Sub

Open in new window

0
 
jjxia2001Author Commented:
Working well!  Thanks!
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.