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
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 250 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

Technology Partners: 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

739 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