Solved

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

Posted on 2011-02-10
3
157 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:jjxia2001
  • 2
3 Comments
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:jjxia2001
Comment Utility
Working well!  Thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now