Deleting files in a network drive using a list in Excel

[eric5605]

Hello,
I have a large shared network drive that I exported the file names and folder path into Excel. I identified about 10,000 files as duplicates needing deletion and marked them as such in the spreadsheet. Is there a way I can automate deleting the files I marked "delete" in Excel from the shared network drive or do I need to find each in Windows Explorer and delete manually? Here is an image of the spreadsheet. I am an intermediate Excel user so I'm not sure how to use VBA or macros to accomplish this task... if possible. Thank you!

 

[johnnyL]

Glad to help.

 

[eric5605]

Hello, I've gone through the moves and am now using the delete VBA code (the first response in this string) for mass deletions. It has been working, but I have been running into an occasional Run-time error '53': File not found. The file is actually in the location when I've cross checked. I can't see any commonality amongst the files that are stopping the script from running. I've been running the macro in chunks to find the files that are causing the error. Any suggestions as to what could be causing this or something I can do differently? Is there a way the to keep the script running and have it highlight the problem files? Thank you.

 

[johnnyL]

What line of the code is highlighted when you get that error?

 

[eric5605]

Sorry, I should have included that:

 

[johnnyL]

Try this version:

Sub DeleteFilesV2()
'
    Dim ArrayRow            As Long
    Dim LastRowColumnA      As Long
    Dim FileToDelete        As String
    Dim InputArray          As Variant
'
    LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                      ' Find last row used in Column A
'
    InputArray = Range("A1:H" & LastRowColumnA)                                 ' Load all data from sheet into InputArray
'
    For ArrayRow = 1 To LastRowColumnA                                          ' Loop through all used rows of the sheet
        If LCase(Trim(InputArray(ArrayRow, 2))) = "delete" Then                 '   If File in the row is marked for deletion then ...
            FileToDelete = InputArray(ArrayRow, 8) & InputArray(ArrayRow, 1)    '       Combine data columns to create full path of file to delete
'
            If Dir$(FileToDelete) <> "" Then                                    '       If FileToDelete exists then ...
                SetAttr FileToDelete, vbNormal                                  '               Set FileToDelete to a deletable status
                Kill FileToDelete                                               '               Permanently delete the FileToDelete
            End If
        End If
    Next                                                                        ' Loop back to check for next FileToDelete
End Sub

 

[eric5605]

Thank you! I'm still getting error 53, but I think I found the commonality with the files that are stopping the process. With all but one, I cannot open the file if I navigate to it and try to open it. Word docs start to open then Word shuts down and the PDF files that stop the script don't open either. They try to open then I get an access denied error. There KB's for the file so something is there, but some of these files have been moved so much and the libraries so poorly organized, perhaps a few files have gotten corrupt. I've gotten about 10 corrupt files that have stopped the script out of the first 4000 files. I'm trying to delete about 200 files at at time and if I get an error in that range and keep narrowing it down until I find the file(s) that is holding it up.

Is it possible to have it continue past files that the script sees an error with??

 

[johnnyL]

How about this:

Sub DeleteFilesV3()
'
    Dim ArrayRow                As Long
    Dim LastRowColumnA          As Long
    Dim UndeletedFileCounter    As Long
    Dim FileToDelete            As String
    Dim UndeletedFileArray()    As String
    Dim InputArray              As Variant
'
    LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                      ' Find last row used in Column A
'
    InputArray = Range("A1:H" & LastRowColumnA)                                 ' Load all data from sheet into InputArray
    UndeletedFileCounter = 0                                                    ' Initialize UndeletedFileCounter
'
    For ArrayRow = 1 To LastRowColumnA                                          ' Loop through all used rows of the sheet
        If LCase(Trim(InputArray(ArrayRow, 2))) = "delete" Then                 '   If File in the row is marked for deletion then ...
            FileToDelete = InputArray(ArrayRow, 8) & InputArray(ArrayRow, 1)    '       Combine data columns to create full path of file to delete
'
            On Error GoTo ErrorHandler                                          '       Enable our error-handling routine.
            If Dir$(FileToDelete) <> "" Then                                    '       If FileToDelete exists then ...
                SetAttr FileToDelete, vbNormal                                  '               Set FileToDelete to a deletable status
                Kill FileToDelete                                               '               Permanently delete the FileToDelete
            End If
        End If
CheckNextFile:
        On Error GoTo 0                                                         '   Return Error handling back over to Excel
    Next                                                                        ' Loop back to check for next FileToDelete
'
    If Not Not UndeletedFileArray Then                                          ' If any files couldn't be deleted then ...
'
        Sheets.Add(Before:=Sheets(1)).Name = "Undeleted Files"                  '   Add a sheet called 'Undeleted Files' to store any files that weren't deleted
'
        Sheets("Undeleted Files").Range("A1").Resize(UBound(UndeletedFileArray)) _
                = Application.Transpose(UndeletedFileArray)                     '   Display the undeleted file names to the added sheet
        Sheets("Undeleted Files").Columns(1).AutoFit                            '   Adjust the column width of column A to display entire file names
    End If
'
    MsgBox "Script has completed."                                              ' Notify the user that the script has completed
    Exit Sub                                                                    ' exit the sub
'
ErrorHandler:
    UndeletedFileCounter = UndeletedFileCounter + 1                             ' Increment UndeletedFileCounter
    ReDim Preserve UndeletedFileArray(1 To UndeletedFileCounter)                ' Increase the size of the UndeletedFileArray
    UndeletedFileArray(UndeletedFileCounter) = FileToDelete                     ' Save the file path and name of the file that wasn't deleted into UndeletedFileArray
    Resume CheckNextFile                                                        ' Remove error encountered and return back to check for the next file
End Sub

See if that handles the error. It should catch the error, save the filename that wasn't deleted, and then continue on. At the end of the script it will display to another sheet any file names that weren't deleted.

 

[eric5605]

So far, so good! You have amazing talent. Thanks so much. Wow, I wish I had your skills!!

 

[johnnyL]

Glad to help.

 

[eric5605]

Hi, this project has been going very well with the macros, thanks again. As I get close to the end, I'm now shifting from deleting files to archiving some of the remaining files. Would there be a way to modify the code from Sub DeleteFilesV3, which is in this thread from May 12th. It works great deleting the files and handling the corrupt files. Now I am trying to mark files Archive and move them to a folder labeled Archive so everything is in one folder. The macro can run the same except it would move the file to a folder called Archive (exact file path TBD) rather than permanently delete the file.