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
