Code to delete a list of files from a directory

[Talat]

Hi

I have a file which will have a differing number of rows of file names in Column A, say in File "Numbers.xls" In another directory say "InvDirectory" there are the corrresponding files to this list. I need to run a code which will pick up the file names from "Numbers.xls" and delete the files from "InvDirectory" and clear ( ie delete all the rows) in column A in "Numbers.xls

Can anyone help?

 

[John_w]

Try this. I've made two assumptions about the data:

1. The first file name is in A2.
2. The file names are just file names (e.g. 1234.dat) without any preceding folder path.

Put the code in a new module in Numbers.xls.

Option Explicit

Sub Delete_Files()

    Dim folderPath As String
    Dim lastRow As Long
    Dim cell As Range
    
    'Folder containing files to be deleted - CHANGE AS REQUIRED
    
    folderPath = "C:\path\to\InvDirectory\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each cell In Range("A2:A" & lastRow)
        If Dir(folderPath & cell.Value) <> "" Then
            Kill folderPath & cell.Value
        End If
    Next
    
    Rows("2:" & lastRow).Delete

    MsgBox "Finished"
    
End Sub

 

[Talat]

Hi John,

Many thanks for the response. I have tried the code, but it does not do what I wanted from it.

It deletes the invoice numbers from the batchfile which are in Column A row 1 to i , which is fine. But it does not delete the actual files in the other directory, say Directory B, from where I want them to be deleted.

The numbers in the batchfile are only there to identify which of the files in the Directory B are to be deleted.


I hope this is clear.

Thanks.

Talât


_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

 

[John_w]

Your description of the problem has changed and it's now unclear to me how the data is arranged. You said the file names are in column A and these files are in a folder called InvDirectory, and that is what I coded for.

What is this batch file and Directory B you now mention?

Perhaps a screenshot of your data would help, and a description of how this relates to the files to be deleted.

 

[Talat]

Hi John,

I am sorry if I am not making myself clear.

I do not think I have changed my specification. All I did extra is to talk about a Batchfile where the the file names are in Column A and anotehr directory, here I just called it directory B.

Every time teh code is run, column A will have a differing number of rows with data in it. The data, ie the invoice numbers corespond to file names in another directory (B). I want these files to be deleted and then the Column A rows cleared so that new numbers can be added to it later. I have another script that does that.

If you still need screen shots , samples let me know. I am not sure how I attach these here, but in teh meanwhile I will search teh forum for the "how"

Thanks.

Talât

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

 

[Talat]

Hi John,

I have worked it out now. It needed the addition of the .xls to the file name. In Column A teh file name is a pure number, whereas the file to be deleted has the extension .xls


Thsi is how I have it now and it works. I will just tidy it now by remowing the message box lines.

Option Explicit

Sub Delete_Files()

    Dim folderPath As String
    Dim lastRow As Long
    Dim cell As Range
    Dim StrFN As String
    
    'Folder containing files to be deleted - CHANGE AS REQUIRED
    
    folderPath = "C:\Users\Talât\Desktop\"
    
    'If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each cell In Range("A1:A" & lastRow)
        StrFN = (folderPath & cell.Value & ".xls")
        MsgBox StrFN
        If Dir(folderPath & cell.Value & ".xls") <> "" Then
        MsgBox StrFN
        Kill StrFN
        End If
    Next
    
    Rows("1:" & lastRow).Delete

    MsgBox "Finished"
    
End Sub

Many thanks for your input. Much appreciated.

Talât

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb