To delete all files or a specific type of files from all the folders and sub folders using VBA.Here we are deleting xlsx type of files for demonstration purpose.
Here we have invoked the command prompt using VBA code, using this approach we can delete the files from the folder and sub folders without using kill statement.
- Declare the path of the folder where files are located
- Check if folder exists or not
- If folder exists then delete all the xlsx files using Del command
- If folder does not exist then display the message that folder does not exist
Sub DeleteFiles() Dim MyFolderPath As String 'Path where the folder is located, change the path as per your requirement MyFolderPath = "C:\Users\newuser\Desktop\MyFolderName" 'Check if the folder already exists or not If Dir(MyFolderPath, vbDirectory) <> "" Then 'Delete all xlsx files in the folder and subfolders , to delete any type of files use [*.*] istead of *.xlsx Call Shell("cmd.exe /S /C" & "Del " & MyFolderPath & "\*.xlsx" & " /S /Q") MsgBox "Files deleted" 'Display a message box if folder does not exist Else MsgBox "Folder does not Exist" End If End Sub
Advantages with this approach
- With this approach folder structure remains intact, only files are deleted
- We can delete (1) all files or (2) specific type of a file
- It is simply a one line command,which can also used at command prompt
- All folders and files will not be moved to recycle bin, they will be permanently deleted. [Make sure you already have back up of those files which you are going to delete, if they are important]
- Before running the code ,close all the files which you want to delete.
- Cross check the path where your folder exists, it should be correct
- You can delete any type of file type as per your requirement,for that you need to change the file extension in the code.
Post you may like