Have any Questions? Email us at Admin@desk100.com

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



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.


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

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.


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

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.


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

Tags: , , , ,

Technical Writing – The Difference Between a Flyer and a Brochure


These terms are sometimes used interchangeably, but it’s pretty well established in the tech writing business that there’s a difference.

Read More

Fun Facts About Thailand


DID YOU KNOW THAT… Thailand is bigger than the United Kingdom, Iceland, Belgium and Austria combined. DID YOU KNOW THAT…

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *