VBA Delete All Workbooks

VBA Delete All Workbooks in a Folder in Excel

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.

VBA Delete All Workbooks in a Folder in Excel
VBA Delete All Workbooks in a Folder

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.

VBA Delete All Workbooks in a Folder in Excel using FSO
VBA Delete All Workbooks in a Folder Using FSO

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

Leave a Reply