How To Use VBA And Excel To Organize Your Files And Folders

Posted on Mar 4 2017 - 7:21am by Techy Hints

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.

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.



Source by Andy L Gibson

About the Author

Geeky Magazine provides you with the daily latest technical news, reviews, startups, and every new in the internet world.