VBA Kill function in Excel or statement is categorized as File and Directory function. This built-in VBA Kill statement deletes a file or files based on the specified path name in Excel VBA. You can specify wildcard characters either * and ? to delete multiple files.
This function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Kill Function in any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Kill function, where we can use this Kill Function and real-time examples in Excel VBA.
- Overview
- Syntax of VBA Kill Function
- Parameters or Arguments
- Where we can apply or use VBA Kill Function?
- Example 1: Delete a File
- Example 2: Delete a File(Returns an Error)
- Example 3: Delete all Files using Wild card Character(* and ?)
- Example 4: Delete all .xls Files from the C-Drive
- Example 5: Delete all .ppt Files from the Current Directory
- Instructions to Run VBA Macro Code
- Other Useful Resources
Syntax of VBA Kill Function
The syntax of the Kill Function in VBA is
Kill(PathName)
The Kill Function returns doesn’t return any file. It deletes a file or files. The Path-name argument represents the file path name. If path-name doesn’t include a folder name, the specified file considers current folder.
Parameters or Arguments:
The Kill function has one argument in Excel VBA.
where
PathName:It is a mandatory string type parameter. The PathName argument represents the path of a file, folder, or directory. It helps to delete a file or files. It contains wild card characters either *(multiple characters) and ? (single Character).
Wild Card Character | Description |
---|---|
* | Allows you to match any string of any length (including zero length) |
? | Allows you to match on a single character |
Where we can apply or use VBA Kill Function?
We can use this Kill Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.
Example 1: Delete a File
Here is a simple example of the VBA Kill function. This below example deletes a file and displays message.
'Delete a File Sub VBA_Kill_Function_Ex1() 'Variable declaration Dim sPath As String sPath = "C:\Someswari\VBAF1\VBA Functions\VBA Text Functions\Temp.xlsm" Kill (sPath) MsgBox "File Deleted Successfully : " & vbCrLf & sPath, vbInformation, "VBA Kill Function" End Sub
Output: Here is the screen shot of the first example output.
Example 2: Delete a File(Returns an Error)
Here is a simple example of the VBA Kill function. This below example returns an error. Because the specified is not available.
'Delete a File(Returns an Error) Sub VBA_Kill_Function_Ex2() 'Variable declaration Dim sPath As String sPath = "C:\VBAF1\VBA Functions\VBA Text Functions\Temp.xlsx" Kill (sPath) MsgBox "File has deleted : " & vbCrLf & sPath, vbInformation, "VBA Kill Function" End Sub
Output: Here is the screen shot of the second example output.
Example 3: Delete all Files using Wild card Character(* and ?)
Here is a simple example of the VBA Kill function. This below example deleted all .doc files from the ‘Temp’ folder. Here we used wild card characters(* and ?) to delete all .doc files.
'Delete all Files using Wild card Character(* and ?) Sub VBA_Kill_Function_Ex3() 'Variable declaration Dim sPath As String Dim iOutput As Integer sPath = "C:\Someswari\VBAF1\VBA Functions\VBA Text Functions\Temp\*.doc?" Kill (sPath) MsgBox "Deleted all files from the Temp Folder: " & vbCrLf & sPath, vbInformation, "VBA Kill Function" End Sub
Output: Here is the screen shot of the third example output.
Example 4: Delete all .xls Files from the C-Drive
Here is a simple example of the VBA Kill function. This below example deleted all .xls files from the ‘C’ Drive. Here we used wild card character(*) to delete all .xls files from the C-Drive.
'Delete all .xls Files from the C-Drive Sub VBA_Kill_Function_Ex4() 'Variable declaration Dim sPath As String sPath = "C:\*.xls" If Len(Dir(sPath)) > 0 Then SetAttr sPath, vbNormal Kill sPath End If Kill (sPath) MsgBox "Deleted all .xls files from the C-Drive: " & vbCrLf & sPath, vbInformation, "VBA Kill Function" End Sub
Output: Here is the screen shot of the fourth example output.
Example 5: Delete all .ppt Files from the Current Directory
Here is a simple example of the VBA Kill function. This below example deleted all .ppt files from the current directory. Here we used wild card character(*) to delete all .ppt files from the current directory.
'Delete all .ppt Files from the Current Directory Sub VBA_Kill_Function_Ex5() 'Variable declaration Dim sPath As String sPath = "*.ppt" If Len(Dir(sPath)) > 0 Then SetAttr sPath, vbNormal Kill sPath End If Kill (sPath) MsgBox "Deleted all .ppt files from the current Directory: " & vbCrLf & sPath, vbInformation, "VBA Kill Function" End Sub
Output: Here is the screen shot of the fifth example output.
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