There might be several unwanted and unused files eating your disk space. Cleaning these files might be a huge task. With excel, you can easily find such unused files and delete them instantly to free disk space.
Download Now
If you want to start to use the software as soon as possible, then you can:
Otherwise, if you want to DIY, you can read the contents below.
Let’s Prepare the GUI
Remove unwanted sheets from the workbook and rename the Sheet 1 as GUI and save the workbook as macro enabled workbook. As shown in the attached image, create two buttons on the sheet and rename the Button 1 as “Browse” and rename the Button 2 as “Delete Files”. You can either merge multiple columns to create a bigger column to accommodate the file path or you can increase the width of a single column.
Let’s make it functional
Copy the script to a new module in your macro enabled workbook. Please ensure that the module name should be Module1. Otherwise, the script will throw an error. Attach the script “Scan_This_Folder” to the button “Browse” and the procedure “Delete_Files” should be attached to the button “Delete Files”.
Sub filefordelete()
Dim v_var1 As Scripting.FileSystemObject
Dim v_var2 As Scripting.Folder
Dim v_var3 As Scripting.File
scanthis = Range("B3").Text
Set v_var1 = New Scripting.FileSystemObject
Set v_var2 = v_var1.GetFolder(scanthis)
i = 7
For Each v_var3 In v_var2.Files
Cells(i, 2) = v_var3
Cells(i, 11) = v_var3.DateLastModified
i = i + 1
Next v_var3
Set v_var1 = Nothing
End Sub
Sub Delete_Files()
Dim lr As Long
Dim r As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For r = 7 To lr
Kill Range("B" & r).Value
Next r
End Sub
Function scanthisfolder() As String
Dim v1 As FileDialog
Dim v2 As String
Set v1 = Application.FileDialog(msoFileDialogFolderPicker)
With v1
.Title = "Folder to scan for files"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
v2 = .SelectedItems(1)
End With
NextCode:
scanthisfolder = v2
Set v1 = Nothing
End Function
Sub Scan_This_Folder()
Range("B3").Value = scanthisfolder()
Call Module1.filefordelete
End Sub
How does it work?
When the user selects a folder, the script reads all files within the selected folder and displays the file name along with the last modified date. Verify the list, remove rows if you want to retain the file and then click the “Delete Files” button. The script will now identify the last used row and delete files reading the file path from the sheet.
Tweak it
As of now, the script scans for files within the selected folder. If there are subfolders within the selected folder, the script cannot scan it. You can tweak the script to scan all subfolders within the root folder and also create a new column to compare the “Last Modified Date” of files with the current date and display the Days. You can then tweak the script to delete files automatically based on the count of days. Though the script uses Microsoft’s file scripting technique, file properties of damaged xlsx files cannot be retrieved.
Author Introduction:
Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover word and outlook recovery software products. For more information visit www.datanumen.com.