If the organization of files on your computer is not your strong point, you're not alone.
Most of us save everything in one or two folders but as the number of files grow, why not utilise the power of Excel and VBA to organize your files a little better.
Using The File System Object To List Folder Files In Excel
A typical scenario might be photos you have saved on your computer. We'll assume the files are stored in the one folder and you'd like to organize your photos into different folders, perhaps based on the year the image was first saved on your computer. The File System Object (FSO) can list the contents of a folder with a few lines of VBA code.
First, define the FSO and the specified folder. We'll assume all the images are in a folder called "files" which is directly under the folder of the Excel file you're working with.
Because VBA can identify the date when the file was first created, the code can extract the year which can be used as a folder name.
The output of the code might look something like this in your worksheet
The next thing to do is loop through the listing, create a new folder based on the year and copy the image to the new location.
If the folder has already been created, we copy the new image to that location; if it is a new "year" then we create a new folder.
In this example the code copies files based on the year of file creation, but could easily be adapted to use the month or other date-specific variables. Alternatively, classifications which would be treated as new folder names could be added manually into the spreadsheet before the copying process.
Summary
Excel has many features which boost your productivity. This code snippet has shown how you can use VBA in a unique way to interact with the file system and folder organisation.
Most of us save everything in one or two folders but as the number of files grow, why not utilise the power of Excel and VBA to organize your files a little better.
Using The File System Object To List Folder Files In Excel
A typical scenario might be photos you have saved on your computer. We'll assume the files are stored in the one folder and you'd like to organize your photos into different folders, perhaps based on the year the image was first saved on your computer. The File System Object (FSO) can list the contents of a folder with a few lines of VBA code.
First, define the FSO and the specified folder. We'll assume all the images are in a folder called "files" which is directly under the folder of the Excel file you're 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 & "\files")
Because VBA can identify the date when the file was first created, the code can extract the year which can be used as a folder name.
Worksheets("example").Activate Range("a1").Activate For Each f In fld.Files yr = Year(f.DateCreated)
With ActiveCell .Offset(1, 0).Activate .Value = f.Name .Offset(0, 1) = CStr(yr) End With Next
The output of the code might look something like this in your worksheet
photo1.jpg,2010
photo2.jpg,2008
photo3.jpg,2007
The next thing to do is loop through the listing, create a new folder based on the year and copy the image to the new location.
range("a1").activate
set rng=activeCell.currentRegion
For Each f In rng.Rows
fromPath = ActiveWorkbook.Path & "\files\" & f.Columns(1)
toPath = ActiveWorkbook.Path & "\" & f.Columns(2) & "\"
If the folder has already been created, we copy the new image to that location; if it is a new "year" then we create a new folder.
If Not fso.FolderExists(toPath) Then
fso.CreateFolder toPath
End If
fso.CopyFile Source:=fromPath, Destination:=toPath
Next
In this example the code copies files based on the year of file creation, but could easily be adapted to use the month or other date-specific variables. Alternatively, classifications which would be treated as new folder names could be added manually into the spreadsheet before the copying process.
Summary
Excel has many features which boost your productivity. This code snippet has shown how you can use VBA in a unique way to interact with the file system and folder organisation.
Andy L Gibson is a an Excel developer and writer keen to bring the magic of VBA to new and experienced users of Excel. He is developing an Excel application called "VBA Assistant" containing VBA code and working examples of many of his articles. Enquiries from Excel users interested in testing the application are welcome. Andy can be contacted on his blog at http://solutions4business.wordpress.com/
By Andy L Gibson
By Andy L Gibson
No comments:
Post a Comment