VBA Delete Files Macro helps to Delete Files using VBA from a folder. Generally while dealing with the file operations we create or delete files using VBA in the process. For example when we are automating a specific task, we create lot of intermediate files and finally we produce one final output.At end we clean or delete all the intermediate files using VBA.
Sometimes we may regularly monitor outlook and when we receive a new mail with required input files. We will save these files in a specific folder to process these files and produce the final output.And we will check before saving the input files in the folder and delete if there are any existing files are already available in the directory.
- Solution
- Example Cases
- Delete a specific file from a folder
- Delete all files from a folder or directory
- Delete a selected file from a directory using Open File Dialog
- Delete a specific type of files from a directory – Delete all Excel files from a folder
- Delete all files from a directory if file name contains specific string
- Delete a specific file from a folder and Sub folders
- Output
- Instructions
- Example File
Solution(s):
We can use DeleteFile method of FileSystemObject to delete files using VBA.Follwing are the examples to delete a specific file or files from a location.
VBA Delete Files Macro – Example Cases:
Delete a specific file from a folder
Following is the example to delete a specific file from a folder or a directory.You can use DeleteFile method of FileSystemObject to Delete a specific file from a folder.
First it will check whether the file exists in the sourse location or not. If it exists it will delete from the directory ot it will display message.
Code:
'In this Example I am deleting a File 'Sample.xls' which exists in the same location of the macro file
Sub sbDeletetingAFile()
Dim FSO
Dim sFile As String
'Source File Location
sFile = "D:Job" & "Sample.xls" 'You can change this Loaction
'Set Object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check File Exists or Not
If FSO.FileExists(sFile) Then
'If file exists, It will delete the file from source location
FSO.DeleteFile sFile, True
MsgBox "Deleted The File Successfully", vbInformation, "Done!"
Else
'If file does not exists, It will display following message
MsgBox "Specified File Not Found", vbInformation, "Not Found!"
End If
End Sub
Output:
Case 1: If file does not exists in the source location to delete, It will display following message.
Case 2: If file is exists in the source location, It will delete the file from source location. And it will display following message.
I’m trying to delete a previous version of a file that my vba program is creating, but when the code that you describe in this page (I’m using it right as yours, only changing the name and location of file, of course) I get an error: “Run-time error ’70’: Permission denied”. I have to clarify something: I did use your code with another program that I wrote in vba excel 2010 where I used to save a file in Desktop because it was a temporary file, then it worked fine, this time I’m deleting a file in a certain address, not in Desktop and then is when I get the error message mentioned before.
Thanks in advance for your assistance
Fermin Bas
Hi Fermin,
We can not delete the files from the web servers using this method. If you want to to delete the the file from your shared drives, make sure you entered correct server path and you have delete permissions.
Thanks-PNRao!
Hi, what if I want to delete all files in a folder older then 30days, how is this possible?
Is it possible to have a vba procedure/func to close any text based “.dat” type files if they are open from a given directory path.
I have another excel program that runs based on bunch of these dat files as input files from an input folder.
Before running excel program I want to make sure all my text based dat files are closed if they are open in a notepad for example.
There is no need to save these dat files if they are open in notepad before closing them programmatically.
Thanks for your info, this success in my work… thansk very much
I have created a macro which has scan and delete particular drive(ie D: drive). But i need to add one more drive for the below macro (ie P: drive). and i need to add IF CONDITION for checking if P or D drive is not available in system still i need to get the macro run and log should be saved. Kindly help me
Below is my macro for scanning only one drive
‘Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
‘Set a reference to Microsoft Scripting Runtime by using
‘Tools > References in the Visual Basic Editor (Alt+F11)
‘Declare the variables
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim strTopFolderName As String
‘Insert the headers for Columns A through F
Range(“A1”).Value = “File Name”
Range(“B1”).Value = “File Size”
Range(“C1”).Value = “File Type”
Range(“D1”).Value = “Date Created”
Range(“E1”).Value = “Date Last Accessed”
Range(“F1”).Value = “Date Last Modified”
Range(“G1”).Value = “File Deleted”
Range(“H1”).Value = “File Deletion/Scan Time”
Range(“I1”).Value = “File Path”
‘Assign the top folder to a variable
strTopFolderName = “D:”
‘Create an instance of the FileSystemObject
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Get the top folder
Set objTopFolder = objFSO.GetFolder(strTopFolderName)
‘Call the RecursiveFolder routine
Call RecursiveFolder(objTopFolder, True)
‘Change the width of the columns to achieve the best fit
Columns.AutoFit
End Sub
Sub RecursiveFolder(objFolder As Scripting.Folder, _
IncludeSubFolders As Boolean)
‘Declare the variables
Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim NextRow As Long
‘Find the next available row
NextRow = Cells(Rows.Count, “A”).End(xlUp).Row + 1
On Error GoTo Handler
‘Loop through each file in the folder
For Each objFile In objFolder.Files
Cells(NextRow, “A”).Value = objFile.Name
Cells(NextRow, “B”).Value = objFile.Size
Cells(NextRow, “C”).Value = objFile.Type
Cells(NextRow, “D”).Value = objFile.DateCreated
Cells(NextRow, “E”).Value = objFile.DateLastAccessed
Cells(NextRow, “F”).Value = objFile.DateLastModified
Cells(NextRow, “I”).Value = objFile.Path
‘If (DateDiff(“d”, objFile.DateLastModified, Now) > 1) And objFile.Type “Data Base File” And Left(objFile.Name, 2) “~$” Then
If (DateDiff(“d”, objFile.DateLastModified, Now) > 7) And (objFile.Type = “Microsoft Word Document” Or _
objFile.Type = “Microsoft Word 97 – 2003 Document”) And _
Left(objFile.Name, 2) “~$” Then
‘Set FileSys = Nothing
‘Set objFolder = Nothing
‘Set objSubFolder = Nothing
‘Set objFile = Nothing
Kill (objFile.Path)
Cells(NextRow, “G”).Value = “Yes”
Cells(NextRow, “H”).Value = Format(Now, “yyyy-dd-mm hh:nn”)
Else
Cells(NextRow, “G”).Value = “No”
Cells(NextRow, “H”).Value = Format(Now, “yyyy-dd-mm hh:nn”)
End If
NextRow = NextRow + 1
Next objFile
‘Loop through files in the subfolders
If IncludeSubFolders Then
For Each objSubFolder In objFolder.SubFolders
Call RecursiveFolder(objSubFolder, True)
Next objSubFolder
End If
Exit Sub
Handler:
Resume Next
End Sub
‘In this Example I am Copying the File From “C:Temp” Folder to “D:Job” Folder
Sub sbCopyingAFile()
‘Declare Variables
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String
‘This is Your File Name which you want to Copy
sFile = “Files_Deletion Macro.xlsm”
‘Change to match the source folder path
sSFolder = “D:Desktop Scan and Deletion”
‘Change to match the destination folder path
sDFolder = “Z:PMOSDMS Control RoomChethanA5901”
‘Create Object
Set FSO = CreateObject(“Scripting.FileSystemObject”)
‘Checking If File Is Located in the Source Folder
If Not FSO.FileExists(sSFolder & sFile) Then
MsgBox “Specified File Not Found”, vbInformation, “Not Found”
Else
FSO.CopyFile (sSFolder & sFile), sDFolder, True
End If
End Sub
Sub ClearSheet()
Sheet1.Cells.Clear
End Sub
I am getting this same issue and its very difficult to solve this problem without any third party app, i guess the best thing to do is to use a third party app like Long Path Tool. Just download it and use it to solve this issue. I hope this would help.Give it a try..
how about running a .cmd file from execl macro.