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
161 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

Suggested Solutions

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

756 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