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.
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.
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.
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.
Leave a Reply