Moving files across folders is a time-consuming task. That is why most of the PC users, leave their files on the desktop and other critical places which highly impacts the PC’s performance. However, with this tool moving files is very easy.
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
Rename Sheet1 as “ControlPanel”. As shown in the image, create headers “Places to scan”, “File Type” and “Move these files to”. As the name implies, under the header “Places to scan”, users can mention folders that have to be scanned for files. They can mention as many folders as they want. There is no restriction. Headers “File Type” and “Move these files to” is the mapping between a certain file type and its destination folder.
Using Shapes, create a button and name it as “Move Files”
Let’s create the mapping
For demo purpose, I have listed various extensions of Microsoft word, excel, image files and zip files. You can add any extension to this list and the macro can handle it.
Let’s make it functional
Copy this script to a new module in the workbook. Save the workbook as a macro-enabled file. Attach this macro to the button we created on the sheet “ControlPanel”.
Sub Move_Files() Dim rf As Long Dim rfend As Long Dim rfolder As String Dim finfolder As String Dim fext As String Dim dfolder As String Dim sfile As String Dim dfile As String rfend = cp.Range("B" & Rows.count).End(xlUp).Row For rf = 5 To rfend rfolder = cp.Range("B" & rf).Value finfolder = Dir(rfolder) Do While finfolder <> "" fext = Right(finfolder, Len(finfolder) - InStrRev(finfolder, ".") + 1) dfolder = Application.WorksheetFunction.VLookup(fext, cp.Range("D5:E20"), 2, False) FileCopy rfolder & finfolder, dfolder & finfolder Kill rfolder & finfolder finfolder = Dir() Loop Next rf End Sub
How does it work?
Soon as the button “Move Files” is clicked, the macro “Move_Files” is triggered. The macro identifies the last row in Column B and each folder mentioned in this column is scanned for files. For each file in the scanned folder, the macro first identifies the file extension. The extension is compared to the mapping table and the destination folder for that extension is identified. The file is then moved to the destination folder.
This tool helps you to manage files of all extension and size. But you have to manually run the macro. If you are using this tool on a server, you can modify the macro to run at regular interval. The Excel file has to be open for the macro to run at intervals. You can also modify the macro to ignore the file extension and move all files from one folder to another. This will help a lot to windows administrators or database administrators for whom moving database files from one folder to another folder is a common task.
Do not overload
Though this tool can handle files of any size, it is recommended to run the macro with few folders to scan. Too many folders with big files will make the macro to run for a long time and end in a non-responsive state which might even lead to Excel corruption.
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