Access Excel VBA delete all workbooks in folder

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA.

Access Excel VBA delete files in folder

Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder.

In order to delete files, you can use FSO File Methods together with FSO Folder Methods

Delete all workbooks in folder

In the below example, Macro deletes all Excel workbooks under folder C:\test\

Public Sub delWB()
    Dim FSO As Object
    Dim folder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\test"
    Set folder = FSO.GetFolder(folderPath)
    
    For Each wb In folder.Files
        If lcase(Right(wb.Name, 3)) = "xls" Or lcase(Right(wb.Name, 4)) = "xlsx" Or lcase(Right(wb.Name, 4)) = "xlsm" Then
           FSO.DeleteFile wb, True
        End If
    Next
End Sub

You can also modify the code a bit to delete other file types, such as PDF, DOC.

For example,

lcase(Right(wb.Name, 3)) = "pdf"

Delete all workbooks in folder and subfolders

The below example deletes all workbooks under folder C:\test\ and all workbookers in all 1st level subfolder.

Public Sub delWB()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\test"
    Set folder = FSO.GetFolder(folderPath)
    
    For Each wb In folder.Files
        If LCase(Right(wb.Name, 3)) = "xls" Or LCase(Right(wb.Name, 4)) = "xlsx" Or LCase(Right(wb.Name, 4)) = "xlsm" Then
           FSO.DeleteFile wb, True
        End If
    Next
    For Each subfolder In folder.SubFolders
        For Each wb In subfolder.Files
            If LCase(Right(wb.Name, 3)) = "xls" Or LCase(Right(wb.Name, 4)) = "xlsx" Or LCase(Right(wb.Name, 4)) = "xlsm" Then
                FSO.DeleteFile wb, True
             End If
        Next
    Next
End Sub

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *