VBA for searching a folder and deleting files based on a pre-determined list of file names

[xcellrodeo]

Hi all, I would very much appreciate your help with the following:
Description of problem:
I have 1 tab in my Workbook ('File Names List') which has a list file names and the corresponding path listed. File Names are listed in Col. A starting row 3 and File Path are listed in Col,B also starting row 3.
Please note this file list can change in terms of nr of files to be searched but they will always be either .JPG or .BMP files.
i.e File Name = DSC123.jpg
File Path = C:\Desktop\Test\DSC123.jpg

What I would like to do is write a VBA script which takes this list of file names and checks if these exist in the C:\Desktop\Test folder.
And if they do exist then delete them and if they dont exist then bring up a box which prints the file name which cannot be found.

Hope this makes sense.
thanks alot for your help

 

[DanteAmor]

Try this

The result of each file will be in column C

Sub deleting_files()
    On Error Resume Next
    For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
        wpath = IIf(Right(Cells(i, "B"), 1) <> "\", Cells(i, "B").Value & "\", Cells(i, "B").Value)
        wfile = wpath & Cells(i, "A")
        If Dir(wfile) <> "" Then
            Kill wfile
            If Err.Number = 0 Then
                Cells(i, "C").Value = "file deleted"
            Else
                Cells(i, "C").Value = "Error : " & Err.Description
            End If
        Else
            Cells(i, "C").Value = "not found"
        End If
    Next
    MsgBox "End"
End Sub

 

[xcellrodeo]

Hi Dante Amor
thanks for your reply.
i just need to check that we are deleting the records in the correct Folder.
it should be the actual images held in the C:\Desktop....folder I mention above which are deleted and not the file reference list in my workbook.
if you could confirm that wld be much appreciated.

thanks

 

[DanteAmor]

Hi Dante Amor
thanks for your reply.
i just need to check that we are deleting the records in the correct Folder.
it should be the actual images held in the C:\Desktop....folder I mention above which are deleted and not the file reference list in my workbook.
if you could confirm that wld be much appreciated.

thanks

Now I do not understand, do you want to delete files?

 

[xcellrodeo]

Now I do not understand, do you want to delete files?

Hi Dante Amor,
yes it is the actual image file that needs deleting.
the file path record in the workbook only serves as a reference or instructions to VBA
so it knows what name of file is and the location of it hence the full path address. Hope this makes more sense.

 

[DanteAmor]

Hi Dante Amor,
yes it is the actual image file that needs deleting.
the file path record in the workbook only serves as a reference or instructions to VBA
so it knows what name of file is and the location of it hence the full path address. Hope this makes more sense.

Did you test the macro?

 

[xcellrodeo]

Did you test the macro?

Hi Dante Amor
I have tested your macro and it does nothing.
I notice you have not specified a folder location path or made any reference to the Workbook sheet.
Can you pls clarify? Thanks

 

[xcellrodeo]

Hi Dante Amor
I have tested your macro and it does nothing.
I notice you have not specified a folder location path or made any reference to the Workbook sheet.
Can you pls clarify? Thanks

hi Dane Amor, sorry I have just re-run and it does do something. it returns a message in Col.C saying 'Error: not found'.
I have checked the path in spreadsheet matches the location of the actual image in the C: .... folder and it does.

 

[xcellrodeo]

hi Dane Amor, sorry I have just re-run and it does do something. it returns a message in Col.C saying 'Error: not found'.
I have checked the path in spreadsheet matches the location of the actual image in the C: .... folder and it does.

The resuls in the XL Worksheet looks like this:
Col. A
DSC123.jpg
Col. B
C:\Desktop\Test\DSC123.jpg
Col. C
'Error:not found'

 

[DanteAmor]

The resuls in the XL Worksheet looks like this:
Col. A
DSC123.jpg
Col. B
C:\Desktop\Test\DSC123.jpg
Col. C
'Error:not found'

Try this please and tell me:

Sub deleting_files()
    Dim wFile As String, desc As String
    On Error Resume Next
    For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
[COLOR=#0000ff]        wFile = Cells(i, "B")[/COLOR]
        If Dir(wFile) <> "" Then
            Kill wFile
            If Err.Number = 0 Then
                Cells(i, "C").Value = "file deleted"
            Else
                desc = Err.Description
                If Dir(wFile) = "" Then
                    Cells(i, "C").Value = "file deleted"
                Else
                    Cells(i, "C").Value = "Error : " & desc
                End If
            End If
        Else
            Cells(i, "C").Value = "not found"
        End If
    Next
    MsgBox "End"
End Sub