Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
167 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
ID: 34866753
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 1000 total points
ID: 34866769
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
ID: 34866823
Working well!  Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

885 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