Using Excel to see inside your folders
Microsoft Excel is certainly one of the most used programs in the business world, but very few people take full advantage of its capabilities using Visual Basic for Applications (VBA). Most programming languages are too difficult and time consuming for many business professionals to learn, but given the tight integration of Excel and VBA, you can get up and running with some useful scripts in no time. This article is an example of how I used VBA to make part of my job easier.
Rather than keeping paper copies of important documents, my company keeps everything stored in PDFs in the cloud. We have a folder for each job where all of the pertinent files are kept. The existence of a particular file in a job folder lets us know quickly that the associated administrative task has been completed. Unfortunately it is extremely tedious to open each job folder to check which files are present to know what has already been done, so I decided to create a little tool in Excel to visualize it for us.
The image for this post is a snapshot of that tool. The job names on the left are dynamically created based on what job folders exist and it searches each of those folders for the file names listed at the top. If we add a new file to one of the folders, the spreadsheet will automatically reflect the change. Likewise, if a new job folder is added, a new row will appear in the sheet.
As Excel macros go, this is a fairly complex one. I've added several other features such as sorting and filtering tools, but I will just show you a simplified version.
I set up a folder called "MainFolder" on the desktop where I will store a few sample sub folders with a few sample files inside them.
Inside the main folder, there are three sub folders:
- "SubFolder A"
- "SubFolder B"
- "SubFolder C"
Within those three sub folders, let's say there are three files you are interested in:
- "FileA"
- "FileB"
- "FileC"
What we will end up with is a spreadsheet that looks like this:
"1" means the file was found.
"0" means the file was not found.
You can use conditional formatting to make the results look more appealing, but all the macro is concerned with is the data.
The list of folders on the left hand side will generate automatically, but the list of files at the top need to be entered in manually. You could also create a version that adds the file names automatically based on what is in the folders, but in my case, I already know what items I need to be checking for so it is unnecessary.
The following code, entered into a module in the Visual Basic Editor, will generate the desired results.
You can use the following link to download a copy of the commented code:
Just paste the text into a new module and try it out! You will need to change the folder path to match a sample folder you create on your desktop. You'll also need to add the names of the files you're looking for in the first row (skip column 1).
If you would like my help setting up something like this or want any additional information, please let me know. You can e-mail me at: phillipsma3@gmail.com or send me a message on Linkedin.
Thanks for reading!