VBA Delete All Workbooks in a Folder in Excel. Delete all Workbooksin a folder in Excel VBA. We can delete all Excel Workbooks using Kill statement or DeleteFile method of the file system object(FSO). Find example macros, screenshots and instructions to use macros for your reference in the following chapter. In the following examples before deleting files, check whether deleting files are exists or not.
Using Kill Statement Delete all Workbooks in a folder
Let us see the example macro to delete all Excel Workbooks using VBA Kill function.
'VBA Delete all Workbooks in a folder in Excel using Kill Statement Sub VBA_Delete_All_Workbooks_Excel_Using_Kill1() 'Variable declaration Dim sWorkbook As String sWorkbook = "C:\Someswari\VBAF1\Test\*.xl*" Application.DisplayAlerts = False 'Delete Workbook Kill sWorkbook Application.DisplayAlerts = True MsgBox "Deleted all Workbooks in a folder successfully.", vbInformation, "VBAF1" End Sub
Output: Here is the sample output screenshot.
Using FSO: DeleteFile method to Delete all Workbooks in Excel VBA
Let us see the example macro to delete all Workbooks using VBA DeleteFile method of File System Object(FSO). Here first we are checking whether folder exists or not before deleting. If folder exists, then delete files in a folder. Otherwise it displays warning message.
'VBA Delete all Workbooks in a folder in Excel using FSO Sub VBA_Delete_Workbook_Excel_Using_FSO2() 'Variable declaration Dim FSO Dim sWorkbookPath As String 'File Name to delete sWorkbookPath = "C:\Someswari\VBAF1\Test\" 'Set Object for file system object Set FSO = CreateObject("Scripting.FileSystemObject") 'Before deleting check file exists or not If FSO.FolderExists(sWorkbookPath) Then 'If file exists, It will delete the file from source location FSO.deletefile sWorkbookPath & "*.xl*", True MsgBox "All Workbooks are deleted successfully.", vbInformation, "VBAF1" Else 'If file does not exists, It will display following message MsgBox "Specified folder doesn't exists.", vbCritical, "VBAF1" End If End Sub
Output: You can notice all Workbooks are deleted from the specified folder. Here is the sample output screenshot.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers