Deleting many Files at once from a folder, which are listed in excel - makro?

Hallo,
I am looking for a solution how to delete my files in a depository at once. I made a list in excel (column A) with files with their full names, which I want to delete from this depository:
H:\Dokument\ToClean\12354-aabb.pdf
H:\Dokument\ToClean\12222-aaggh.zip
My files for deleting are in various forms (pdf, word, zip, doc) and have various names.

Until now I could find makro, vba in Excel only for deleting 1. all files from the depository, 2.files with a specific format 3. a specific file. Non of these solutions are appropriate for me.
One solustion was supposed  to be suitable, but it does not work:

Sub DeleteButton()
Dim i As Long
 
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Kill Cells(i, 1).Value
    Next i

End Sub

This makro has a problem with the "Kill Cells(i, 1).Value". I would be thankfull if you could help.
Thank you.
Jana KulataData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Welcome to Experts-Exchange Jana. You have created this question with a topic of Visual Basic Classic, which is VB6 a quite old stand-alone language, but the code contains a reference to 'Cells', so is this about Excel?

If it is Excel then your code should work as long as you have the required permissions to delete the files. You say "This makro has a problem...". What error message do you get? I also see that in the question's title you say "C://Documents/ToClean)" but that doesn't match the format of the file names in your cells. Why is that?
0
Roy CoxGroup Finance ManagerCommented:
Try this
Option Explicit

Public Sub KillFiles(Killfile As String)
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Sub

Sub KillFilesInList()
''///To delete all the files in a in a list
    Dim oWs As Worksheet
    Dim iX As Integer
    Dim MyFile As String

    Set oWs = ActiveSheet    ''/// change to actual sheet's name


    For iX = 1 To oWs.Cells(oWs.Rows.Count).End(xlUp).Row
        MyFile = oWs.Cells(iX, 1).Value
        If Not IsEmpty(MyFile) Then
            KillProperly MyFile
            ''///need to specify full path again because a file was deleted 1
            MyFile = Dir(MyFile)
        End If
    Next iX
End Sub

Open in new window


This assumes files full path are listed
0
Jana KulataData AnalystAuthor Commented:
Hallo Martin Liss,
thank you for your answer. You are right, it is an excel. It gives me an error: Runtime Error '13', Types incompatible.

You noticed well my mistake. The way ,the link (H:\Dokument\ToCleant) where I have my files is correct as I alwas use "copy Path" of the file to be sure, it is right.
Thank you.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Roy CoxGroup Finance ManagerCommented:
Do you include the filepath in the cell entry?

Note: you can delete this line in my code above if you d. If not I will need to amend the code for you to add the filepath
0
Roy CoxGroup Finance ManagerCommented:
This inclyudes the File Path
Option Explicit

Public Sub KillFiles(Killfile As String)
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Sub

Sub KillFilesInList()
''///To delete all the files in a in a list
    Dim oWs As Worksheet
    Dim iX As Integer
    Dim MyFile As String
    
    Const FilePath As String = "H:\Dokument\ToClean\"

    Set oWs = ActiveSheet    ''/// change to actual sheet's name


    For iX = 1 To oWs.Cells(oWs.Rows.Count).End(xlUp).Row
        MyFile = oWs.Cells(iX, 1).Value
        If Not IsEmpty(MyFile) Then
            KillFiles FilePath & MyFile
            ''///need to specify full path again because a file was deleted 1
            MyFile = Dir(MyFile)
        End If
    Next iX
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jana KulataData AnalystAuthor Commented:
Hallo Roy,

Do you have to write a specific full path in 24th row?

Anyway, I found a mistake I made with my script. Actually it works, but in my "Delete List " in Excel I may have only those file-names which exists in my file H:\Dokument\ToClean. Stupid mistake. After that it worked!

Thank you so much for your help!! I am very happy!
0
Roy CoxGroup Finance ManagerCommented:
Hi Jana

My last code  has the directory path declared as a constant value, the file name & extension are picked up from the cells
0
Jana KulataData AnalystAuthor Commented:
Thank you Roy!!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help.

I'm not sure if you can still do this, but can you see if  it is possible for you to change Visual basic Classic to VBA and/or Excel. It helps other users when searching for answers.
0
Jana KulataData AnalystAuthor Commented:
Trueth! Thank you for the notice!
0
Roy CoxGroup Finance ManagerCommented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.