Delete all the files from folder and sub folder using VBA Excel

Delete all the files from folder and sub folder using VBA Excel

Objective

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.

Approach

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.

Steps

  1. Declare the path of the folder where files are located
  2. Check if folder exists or not
  3. If folder exists then delete all the xlsx files using Del command
  4. If folder does not exist then display the message that folder does not exist

Code

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

  1. With this approach folder structure remains intact, only files are deleted
  2. We can delete (1) all files or (2) specific type of a file
  3. It is simply a one line command,which can also used at command prompt

Notes

  1. 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]
  2. Before running the code ,close all the files which you want to delete.
  3. Cross check the path where your folder exists, it should be correct
  4. 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

Find , List ,Export and save the name of files in a folder using VBA excel

1 comment on “Delete all the files from folder and sub folder using VBA Excel

Comments are closed.