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
Dim f
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.
Worksheets("instance").Activate
Vary("a1").Activate
For Every f In fld.Information
yr = Yr(f.DateCreated)With ActiveCell
.Offset(1, zero).Activate
.Worth = f.Title
.Offset(zero, 1) = CStr(yr)
Finish With
Subsequent
The output of the code may look one thing like this in your worksheet
photo1.jpg,2010
photo2.jpg,2008
photo3.jpg,2007
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.
vary("a1").activate
set rng=activeCell.currentRegion
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.CreateFolder toPath
Finish If
fso.CopyFile Supply:=fromPath, Vacation spot:=toPath
Subsequent
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.