In VBA, you can use the Kill statement to delete a file. For example
Kill "C:\Temp\Test.txt"
You need to remember, though, that Kill permanently deletes the file. There is no way to "undo" the delete. The file is not sent to the Windows Recycle Bin. VBA does not provide a built-in way to send a file to the Recycle Bin, but with a call to a Windows API (Application Programming Interface) procedure, you can send a file to the Recycle Bin.
To delete the contents of a folder (all file and subfolders), but leave the folder intact, see the Empty Folder page. The function below will send the file or folder identified by FileSpec to the Recycle Bin, with following restrictions.
Additionally, the code prevents you from deleting the following folders, although you can delete file or folders within these folders:
The code below will delete a file or folder, and return a result of True or False indicating the success of the operation. If the recycle operation was not successfully carried out, the public variable G_RecycleErrorText will contain an error message describing why the specified file or folder could not be send to the Windows Recycle Bin.
The code is shown below. You can download a bas module file here.
Option ExplicitOption Compare Text‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ modRecycle‘ By Chip Pearson, www.cpearson.com , chip@cpearson.com‘ This contains function for sending files and folder to the Windows Recycle Bin.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Error message text is put in this‘ variable which describes the‘ reason that RecycleFileOrFolder‘ return a False result.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Public G_RecycleErrorText As String‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Windows API functions, constants,and types.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Private Declare Function SHFileOperation Lib "shell32.dll" Alias _"SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As LongPrivate Declare Function PathIsNetworkPath Lib "shlwapi.dll" _Alias "PathIsNetworkPathA" ( _ByVal pszPath As String) As LongPrivate Declare Function GetSystemDirectory Lib "kernel32" _Alias "GetSystemDirectoryA" ( _ByVal lpBuffer As String, _ByVal nSize As Long) As LongPrivate Declare Function SHEmptyRecycleBin _Lib "shell32" Alias "SHEmptyRecycleBinA" _(ByVal hwnd As Long, _ByVal pszRootPath As String, _ByVal dwFlags As Long) As LongPrivate Const FO_DELETE = &H3Private Const FOF_ALLOWUNDO = &H40Private Const FOF_NOCONFIRMATION = &H10Private Const MAX_PATH As Long = 260Private Type SHFILEOPSTRUCThwnd As LongwFunc As LongpFrom As StringpTo As StringfFlags As IntegerfAnyOperationsAborted As BooleanhNameMappings As LonglpszProgressTitle As StringEnd TypePublic Function RecycleFileOrFolder(FileSpec As String) As Boolean‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ RecycleFileOrFolder‘ This procedure sends FileSpec to the Windows Recycle Bin. It returns True if‘ successful or False if an error occurred.‘‘ FileSpec must be the fully qualified file name on a local drive (not a mapped‘ drive or a network share). FileSpec may be either a file name or a folder name.‘‘ An error will occurred and the funciton will return False‘ if any of the following are true:‘ -------------------------------------------------------------‘ FileSpec does not exist‘ FileSpec is not a fully qualified file name (including Drive and Path‘ information)‘ FileSpec contains the wildcard characters ‘*‘ or ‘?‘.‘ FileSpec is in use by another process‘ FileSpec is a System folder (however, files within a system folder‘ may be recycled).‘ FileSpec is ThisWorkbook.‘ FileSpec is ThisWorkbook.Path‘ FileSpec is a root directory of any drive‘‘ Also, the following folders may not be recycled (although a file within‘ these folder may be recycled):‘ -----------------------------------------------------------‘ C:\Windows\System32 ‘ returned by GetSystemDirectory‘ C:\Windows ‘ parent of GetSystemDirectory‘ C:\Program Files ‘ top level folder of Application.Path‘ C:\<user-profile>\My Documents‘ C:\<user-profile>\Desktop‘ Application.Path‘ ThisWorkbook.Path‘‘ A text description of the error is placed in the G_RecycleErrorText variable.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Dim FileOperation As SHFILEOPSTRUCTDim lReturn As LongDim Attr As VbFileAttributeDim SystemFolder As StringDim WindowsFolder As StringDim ProgramFilesFolder As String ‘*Dim MyDocumentsFolder As String ‘*Dim ThisWorkbookFolder As StringDim DesktopFolder As String ‘*Dim ShellObj As ObjectDim Pos As IntegerDim sFileSpec As StringDim ProtectedFolders(1 To 7) As StringConst C_ARR_WINDOWS = 1Const C_ARR_WINDOWS_SYSTEM32 = 2Const C_ARR_PROGRAM_FILES = 3Const C_ARR_MYDOCUMENTS = 4Const C_ARR_DESKTOP = 5Const C_ARR_APP_PATH = 6Const C_ARR_THISWORKBOOK_PATH = 7G_RecycleErrorText = vbNullStringsFileSpec = Trim(FileSpec)‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we weren‘t passed a empty string‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If Trim(sFileSpec) = vbNullString ThenG_RecycleErrorText = "Specified Filename is empty or spaces."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Get rid of trailing slash.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If Len(sFileSpec) > 1 ThenIf Right(sFileSpec, 1) = "\" ThensFileSpec = Left(sFileSpec, Len(sFileSpec) - 1)End IfEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ System Folder, typically "C:\Windows\System32"‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘SystemFolder = String$(MAX_PATH, vbNullChar)GetSystemDirectory SystemFolder, Len(SystemFolder)SystemFolder = Left(SystemFolder, InStr(1, SystemFolder, vbNullChar, vbBinaryCompare) - 1)‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Windows Folder, parent of SystemFolder,‘ typically "C:\Windows"‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Pos = InStrRev(SystemFolder, "\", -1, vbBinaryCompare)If Pos > 0 ThenWindowsFolder = Left(SystemFolder, Pos - 1)End If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Program Files. Top directory of Application.Path.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Pos = InStr(1, Application.Path, "\", vbBinaryCompare)If Pos > 0 ThenPos = InStr(Pos + 1, Application.Path, "\", vbBinaryCompare)If Pos > 0 ThenProgramFilesFolder = Left(Application.Path, Pos - 1)End IfEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Get an instance of WScript.Shell to‘ retreive the "My Documents" and "Desktop"‘ folders.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Set ShellObj = CreateObject("WScript.Shell")‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ My Documents‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘MyDocumentsFolder = ShellObj.SpecialFolders("MyDocuments")‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Desktop‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘DesktopFolder = ShellObj.SpecialFolders("Desktop")‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Load up the array protected folders. None of‘ these folders may be recycled, although folders and‘ files within these folders may be recycled.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ProtectedFolders(C_ARR_WINDOWS) = WindowsFolderProtectedFolders(C_ARR_WINDOWS_SYSTEM32) = SystemFolderProtectedFolders(C_ARR_PROGRAM_FILES) = ProgramFilesFolderProtectedFolders(C_ARR_MYDOCUMENTS) = MyDocumentsFolderProtectedFolders(C_ARR_DESKTOP) = DesktopFolderProtectedFolders(C_ARR_APP_PATH) = Application.PathProtectedFolders(C_ARR_THISWORKBOOK_PATH) = ThisWorkbook.Path‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Check if the file or folder exists.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If Dir(sFileSpec, vbNormal + vbArchive + vbSystem _+ vbHidden + vbDirectory) = vbNullString ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ does not exist."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure that sFile isn‘t a network file name. You‘ can‘t recycle network files.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If PathIsNetworkPath(sFileSpec) <> 0 ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ is on a mapped drive or network share."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we have a fully qualified file name. That‘ means it has a drive specification character (:).‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If InStr(1, sFileSpec, ":") = 0 ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec _& "‘ is not a fully qualified file name."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we don‘t have a root directory.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If (sFileSpec Like "?:\") Or (sFileSpec Like "??:\") Or _(sFileSpec Like "?:") Or (sFileSpec Like "??:") ThenG_RecycleErrorText = "The specified file or folder is a drive root directory."RecycleFileOrFolder = FalseExit FunctionEnd IfIf InStr(1, sFileSpec, "?", vbBinaryCompare) Or _InStr(1, sFileSpec, "*", vbBinaryCompare) ThenG_RecycleErrorText = "The file specification ‘" & sFileSpec & _"‘ contains ‘?‘ or ‘*‘ wildcards."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure sFileSpec is not a protected directory‘ as we define a protected directory.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘For Pos = LBound(ProtectedFolders) To UBound(ProtectedFolders)If StrComp(sFileSpec, ProtectedFolders(Pos), vbTextCompare) = 0 ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ names a folder that this procedure will not recycle."RecycleFileOrFolder = FalseExit FunctionEnd IfNext Pos‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we are not dealing with a system file or‘ folder.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Attr = GetAttr(sFileSpec)If Attr And vbSystem ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ is a system file or folder."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we‘re not deleting our own directory.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If StrComp(sFileSpec, ThisWorkbook.Path, vbTextCompare) = 0 ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ refers to ThisWorkbook‘s Path."RecycleFileOrFolder = FalseExit FunctionEnd If‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ Ensure we‘re not deleting the application‘s‘ folder.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘If StrComp(sFileSpec, Application.Path, vbTextCompare) = 0 ThenG_RecycleErrorText = "The specified file or folder ‘" & sFileSpec & _"‘ refers to Application‘s Path."RecycleFileOrFolder = FalseExit FunctionEnd IfWith FileOperation.wFunc = FO_DELETE.pFrom = sFileSpec.fFlags = FOF_ALLOWUNDO‘‘ OR if you want to suppress the "Do You want‘ to delete the file" message, use‘.fFlags = FOF_ALLOWUNDO + FOF_NOCONFIRMATIONEnd WithlReturn = SHFileOperation(FileOperation)If lReturn = 0 ThenRecycleFileOrFolder = TrueElseRecycleFileOrFolder = FalseEnd IfEnd Function
It should be noted that Kill is much, much, faster than recycling a file, because recycling means copying the file. If you are deleting many files, you may want to stick with Kill. Just be sure you are killing the right file.
NOTE: When you call RecycleFileOrFolder, you must provide the full file name, including the drive name and folder path. Otherwise, the file may be permanently deleted and not sent to the Recycle Bin.
Recycling An Entire Folder
You can recycle an entire folder using the same code as Recycle File. Just substitute the folder name for the file name. If the folder is not empty, all of its contents will, of course, go to the recycle bin in the folder.
NOTE: When you call RecycleFileOrFolder, you must provide the full folder name, including the drive name and folder path. Otherwise, the file may be permanently deleted and not sent to the Recycle Bin.
Emptying The Recycle Bin
You can use another Windows API function to empty the Recycle Bin. Put the following code at the top of a standard code module. Note that if you have separate Recycle Bins for each drive, this will empty all the Recycle Bins. If you want to empty the Recycle Bin for a specific drive, change vbNullString to "C:\" where "C:\" is the root of the drive whose Recycle Bin you want to empty. The following code will empty the Recycle Bin. The code shown below uses the API functions, types, and constants declared at the beginning of the RecycleFileOrFolder code shown above.
Sub EmptyRecycleBin(Optional DriveRoot As String = vbNullString)‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘ EmptyRecycleBin‘ This procedure empties the Recycle Bin. If your system is configured to‘ maintain separate Recycle Bins for each drive, you can specify the DriveRoot‘ of the drive whose Recycle Bin you want to empty. If DriveRoot is omitted,‘ all the drives‘ Recycle Bins will be emptied.‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘Const SHERB_NOCONFIRMATION = &H1Const SHERB_NOPROGRESSUI = &H2Const SHERB_NOSOUND = &H4Dim Res As LongIf DriveRoot <> vbNullString ThenIf PathIsNetworkPath(DriveRoot) <> 0 ThenMsgBox "You can‘t empty the Recycle Bin of a network drive."Exit SubEnd IfEnd IfRes = SHEmptyRecycleBin(hwnd:=0&, _pszRootPath:=DriveRoot, _dwFlags:=SHERB_NOCONFIRMATION + _SHERB_NOPROGRESSUI +SHERB_NOSOUND)End Sub
联系客服