VBA allows us to delete any file on the computer by utilizing VBA programs. Using simple VBA programs, we may delete any individual file, specified file types, or all files in a specific folder, or even the entire folder. In this tutorial, we’ll show you how to delete files with wildcards with Excel VBA.
4 Effective Ways to Delete Files with Wildcards Using VBA in Excel
In the sections below, we will show you 4 effective ways to delete files with wildcards. The Kill Command is the program that is used to remove any file. To remove any file, first specify the directory of the file, which indicates where the file is placed on the desktop, and then execute the Kill command to remove the file. In the below image, we have a folder named ‘Files’ which will be used to accomplish the task.
1. Delete Excel Worksheet Files with Wildcards Using a VBA Code
First of all, learn how to run VBA codes to delete a file using the kill command. Let’s say you want to delete the file named ‘average-time’ from the given folder. To do so, follow the steps below.
Step 1:
- Firstly, press Alt + F11 to open the VBA Macro.
- Click on the Insert
- Then, choose the Module
Step 2:
- Secondly, click on the marked icon to get the path directory of the file.
Step 3:
- Press Ctrl + C to copy the path directory.
Step 4:
- Paste the following VBA See carefully, we have entered the file name and extension after the path directory.
Sub Delete_Excel_worksheet_Files_1()
'paste the address and type a wildcard character(*)after a backslash
'Enter the File type .xlsx for excel worksheet
Kill "C:\Users\Admin\Desktop\Files\average-time.xlsx"
End Sub
Step 5:
- Save the program and press F5 to run it.
- In the below image the result shows that the ‘average-time’ file is deleted.
Step 6:
- To delete all the Excel worksheets with the .xlsx extension, paste the following VBA
- We have used just an asterisk (*) wildcard character after the file pathway.
Sub Delete_Excel_worksheet_Files_1()
'paste the address and type a wildcard character(*)after a backslash
'Enter the File type .xlsx for excel worksheet
Kill "C:\Users\Admin\Desktop\Files\*.xlsx"
End Sub
Step 7:
- To run the program, save and press F5
- Therefore, results will show that the Excel worksheets with the .xlsx extension are deleted.
Read More: How to Use Excel VBA to Move Files
2. Run a VBA Code to Delete Macro Enabled Files in a Folder with Wildcards in Excel
Similar to the previous method, if we want to delete the Excel Macro-Enabled files, we had to add the extension .xlsm. Follow the instructions below to do so.
Step 1:
- After opening the VBA Macro, paste the following VBA
Sub Delete_Excel_Macro_Files_2()
'Type the file extension .xlsm after the * wildcard character
Kill "C:\Users\Admin\Desktop\Files\*.xlsm"
End Sub
Step 2:
- Then, press F5 to run the program after making sure you saved it.
- As a result, all the Macro–Enabled files are removed.
Notes. Here is an important note that if you want to delete all the excel files both for the different extensions .xlsx and .xlsm you need to just type the extension .xls or .xl as they both match the first part of the extension. Moreover, you can apply it to any other extensions which match with themselves. Follow the steps to see the difference.
- Simply, paste the following codes with the .xls
Sub Delete_All_Excel_Files_Nt()
'type a common extension .xls after the (*) wildcard character
Kill "C:\Users\Admin\Desktop\Files\*.xls"
End Sub
- Therefore, all the excel files are deleted.
Read More: Excel VBA to Loop Through Files in Folder and Rename
3. Delete an Entire Folder with Wildcards Using Excel VBA
Using basic VBA codes and wildcard characters, you may also delete a whole folder with names. To do so, follow the instructions below.
Step 1:
- Paste the VBA code with two wildcards characters as shown in the below image.
Sub Delete_All_Files_and_Folder_3()
'Use two (*) wildcard characters to delete all the files in the folder
Kill "C:\Users\Admin\Desktop\Files\*.*"
End Sub
Step 2:
- Then, save the program and press F5 to run it.
- Therefore, it will remove all files in the ‘Files’
Step 3:
- So, to delete the file name too, paste the following VBA code with the RmDir
Sub Delete_All_Files_and_Folder_3()
'Use two (*) wildcard characters to delete all the files in the folder
Kill "C:\Users\Admin\Desktop\Files\*.*"
'This RmDir Command will delete the folder name from the directory
RmDir "C:\Users\Admin\Desktop\Files\"
End Sub
Step 4:
- Finally, press F5 to run the program after saving.
- Therefore, the ‘Files’ folder will be unavailable on the previous path directory.
Read More: How to Use Excel VBA to List Files in Folder
4. Apply the Conditional IF to Delete Files with Wildcards Using Excel VBA
This is a crucial section since you must read it first if you are uncertain about the file type extension. Furthermore, if the requested file does not exist, you must verify it. As a consequence, we’ll use an IF conditional in VBA code to delete files after reviewing the findings. Follow the outlined steps below to have it done.
Step 1:
- Firstly, paste the following VBA code to delete all excel files (.xls).
Sub IF_Conditional_to_Delete_Files_with_Wildcards_4()
'Declare a variable to define source address
Dim Source_File_Address As String
Source_File_Address = "C:\Users\Admin\Desktop\Files\*.xls"
'Apply IF condition to check whether the file exists
If Dir(Source_File_Address) <> "" Then
'Type the Kill command to delete files if the file exists
Kill "C:\Users\Admin\Desktop\Files\*.xls"
Else
'Type a result to show in a message box if the file does not exist
MsgBox Source_File_Address & " File does not exist."
End If
End Sub
Step 2:
- Secondly, save the program.
- Then, press F5 to run the program.
- As a result, you will see all the excel files are deleted as it meets the condition.
Step 3:
- Furthermore, run the program.
- This time it will show a message box typed with “File does not exist” as there is no file for the (.xls) extension as we deleted it earlier.
Conclusion
To summarize, I hope you now know how to delete files in Excel VBA using wildcards. To teach and practice with your data, all of these ways should be employed. Examine the practice book and apply what you’ve learned. We are inspired to continue giving lectures like this because of your crucial support.
Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.
Stay with us and continue to learn.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
