Delete Excel files in specified folder

Question

I'm new to VBA and I've practiced some code about delete Excel files in specified folder with condition (if cell A2 has no data then delete Excel file). And my code look like this:

Public Sub Deletefile()
    Dim myfolder As String
    Dim myfile As Variant
    Dim i As Variant

    myfolder = "C:\Users\Downloads\AttachmentFolder"
    myfiles = Dir(myfolder & "*.xlsx")
    For i = 1 To UBound(myfiles)
        With Workbooks(i)
            .Open
        End With
        If Workbooks(i).Range("A2").Count = 0 Then
            Kill myfiles(i)
        End If
    Next
End Sub

I took the code above on the Internet and modified it but VBA just said "type mismatch". Please correct and explain where I'm wrong.

There is no slash between `"C:\Users\Downloads\AttachmentFolder"` and `"*.xlsx"`. `Dir` [does not](https://stackoverflow.com/a/12058116/11683) return an array. You cannot delete a workbook that is currently opened. — GSerg, Jun 29 '19 at 13:38
Thanks, i got it, but i think i have a wrong way to understand the code. I'll try another code — Andy Be, Jun 29 '19 at 16:18

Mikku · Accepted Answer · 2019-06-29T14:22:59.480

0

This will work for you:

Change the sheet Name if you want to check for Value of A2 on a different worksheet then the 1st one.

Public Sub Deletefile()

    Dim myfolder As String

    Dim objFSO As Object
    Dim objFolder, sfol As Object
    Dim objFile As Object

    myfolder = "C:\Users\Downloads\AttachmentFolder\"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getFolder(myfolder)

    For Each objFile In objFolder.Files
        If Right(objFile.Name, 5) = ".xlsx" Then
            Workbooks.Open (objFile)

            If Len(ActiveWorkbook.Worksheets(1).Range("A2")) < 1 Then
                ActiveWorkbook.Close False
                Kill objFile
            End If

            ActiveWorkbook.Close False

        End If

    Next objFile

End Sub

edited Jun 29 '19 at 14:22

answered Jun 29 '19 at 13:48

Mikku

6,538
3
15
38

I wouldn't rely on `ActiveWorkbook`. It's safer to use workbook object returned by `Workbooks.Open`. – JohnyL Jun 29 '19 at 14:19
@JohnyL .. That's True. But as we have to close the file anyways, so this would work. – Mikku Jun 29 '19 at 14:23
And what's the connection? – JohnyL Jun 29 '19 at 14:24
Thanks. It works but it didn't kill all at once but one at a time. – Andy Be Jun 29 '19 at 16:15

Delete Excel files in specified folder

1 Answers1