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

Thread: VBA for searching a folder and deleting files based on a pre-determined list of file names
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by xcellrodeo; Jun 10th, 2019 at 05:05 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

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

    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
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by xcellrodeo View Post
    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?
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by DanteAmor View Post
    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.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by xcellrodeo View Post
    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?
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by DanteAmor View Post
    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

  8. #8
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by xcellrodeo View Post
    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.

  9. #9
    Board Regular
    Join Date
    Oct 2008
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by xcellrodeo View Post
    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'

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by xcellrodeo View Post
    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
            wFile = Cells(i, "B")
            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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •