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

xcellrodeo

Board Regular
Joined
Oct 27, 2008
Messages
195
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 :)
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,128
Office Version
2007
Platform
Windows
Try this

The result of each file will be in column C

Code:
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

Board Regular
Joined
Oct 27, 2008
Messages
195
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,128
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Oct 27, 2008
Messages
195
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,128
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Oct 27, 2008
Messages
195
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

Board Regular
Joined
Oct 27, 2008
Messages
195
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

Board Regular
Joined
Oct 27, 2008
Messages
195
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,128
Office Version
2007
Platform
Windows
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,133
Messages
5,484,919
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top