How To Use VBA And Excel To Organize Your Files And Folders
If the group of recordsdata in your laptop will not be your robust level, you are not alone.
Most of us save the whole lot in a single or two folders however because the variety of recordsdata develop, why not utilise the facility of Excel and VBA to prepare your recordsdata a bit higher.
Utilizing The File System Object To Record Folder Information In Excel
A typical situation is likely to be pictures you’ve gotten saved in your laptop. We’ll assume the recordsdata are saved within the one folder and you would like to prepare your pictures into completely different folders, maybe primarily based on the 12 months the picture was first saved in your laptop. The File System Object (FSO) can checklist the contents of a folder with just a few strains of VBA code.
First, outline the FSO and the required folder. We’ll assume all the photographs are in a folder referred to as “recordsdata” which is instantly below the folder of the Excel file you are working with.
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Set fso = New Scripting.FileSystemObject
Set fld = fso.GetFolder(ActiveWorkbook.Path & "recordsdata")
As a result of VBA can determine the date when the file was first created, the code can extract the 12 months which can be utilized as a folder title.
For Every f In fld.Information
yr = Yr(f.DateCreated)
.Worth = f.Title
.Offset(zero, 1) = CStr(yr)
The output of the code may look one thing like this in your worksheet
The following factor to do is loop by means of the itemizing, create a brand new folder primarily based on the 12 months and replica the picture to the brand new location.
For Every f In rng.Rows
fromPath = ActiveWorkbook.Path & "recordsdata" & f.Columns(1)
toPath = ActiveWorkbook.Path & "" & f.Columns(2) & ""
If the folder has already been created, we copy the brand new picture to that location; if it’s a new “12 months” then we create a brand new folder.
If Not fso.FolderExists(toPath) Then
fso.CopyFile Supply:=fromPath, Vacation spot:=toPath
On this instance the code copies recordsdata primarily based on the 12 months of file creation, however may simply be tailored to make use of the month or different date-specific variables. Alternatively, classifications which might be handled as new folder names could possibly be added manually into the spreadsheet earlier than the copying course of.
Excel has many options which enhance your productiveness. This code snippet has proven how you should use VBA in a singular method to work together with the file system and folder organisation.