Delete A File

VBA provides the ability to delete a workbook, or any other file using the Kill statement. You can use this statement to delete any file, as long as the user has permission to delete it. The following code illustrates the use of the Kill statement: Kill(pathname).

The Kill statement requires one argument, the pathname. The pathname argument is a string referencing the files that you want to delete. To assure that Excel locates the files, the pathname argument must include not only the filename but also the folder and drive specification. If you do not specify the path, Excel looks for the specified files within the current directory. Make sure you enclose the path statement within quotes.

You can specify the name of a single file by typing the complete filename, including the extension. You can also remove multiple files at once using the wildcard symbols supported by VBA to specify multiple characters. You can use an asterisk (*) to represent multiple characters or a question mark symbol (?) to specify a single character. For example, you can remove the entire contents of a folder using the *.* specification. For example, the statement Kill ("c:\Excel Files\*.*") matches the string to the files within the folder. Because *.* matches all filenames, Excel removes all files within the folder. If you only want to remove the Excel workbooks within the folder, you use *.xls.

Keep in mind that you cannot delete files that are open. If you attempt to do so, a Permission Denied error appears and tells you that you cannot delete the file. You also cannot delete files that have a read-only property. If you attempt to delete a read-only file, Excel displays a Path/File access error message.

DELETE A FILE

DELETE A FILE

—n Create a new subroutine.

0 Type Dim DeleteWb As String, replacing DeleteWB with the name of the workbook variable.

0 Type additional VBA Code to determine the name of the file(s) to delete.

—n Create a new subroutine.

0 Type Dim DeleteWb As String, replacing DeleteWB with the name of the workbook variable.

0 Type additional VBA Code to determine the name of the file(s) to delete.

0 0

Post a comment