Setting Up Folders
Did you know you through VBA macros, Excel can add entire folder hierarchies for you? In fact, any Microsoft application that uses VBA can do this. You can set up huge folder structures based on dates, revenue/cost centers, products, customers, vendors, or anything else you want in just seconds! This has saved me a tremendous amount of time when building out weekly or daily folder structures. The XLEV8 Excel add-in contains the Create Subfolders macro that can create folders for you. You just havet to specify the parent folder and the subfolder to create in a settings sheet that is created the first time the macro is run. It will check to ensure the parent folder already exists before adding the subfolder, so make sure you start with an existing parent folder for the first folder to create. After that, the parent folder can be one of the subfolders created if you want to set up a multi-level folder structure – just make sure you think through the order when you set them up.
Ideally, you’ll want to use formulas to build out the folder or subfolder paths, such as in the example files below. For instance, if you need to build out folders for each day of the year, the subfolder text could refer to cells to the side that just start with the first date and increment by one. Just make sure you format the dates so you don’t get the underlying number that Excel uses for dates! The TEXT formula works great for this, and is used in the example files below.
After you’ve set up and created the folders, make sure to save a copy of the workbook containing the settings sheet so you can use it again later. If you’ll be creating the same folders on a recurring basis, consider including an Inputs sheet, where you just have to enter a couple of inputs, such as the start date and year. I also always recommend to include some instructions so you don’t have to rethink how it works many weeks or months later.
If you want to build your own macros to create folders, you’ll want to use the MkDir function. An example that creates a folder is below. This will create the Dogs folder within the existing Pets folder. If any of the path before Dogs does not exist, you will get an error.
Setting Up Shortcuts
Setting up the folders for you is great – you save time and cut down on the risk of mis-key errors. I actually take it a step further and add some _Previous and _Next shortcuts within those new folders to go back and forth between them. Including underscore characters ensures the shortcuts will sort to the top of the folder contents by default. Creating these manually just wouldn’t be worth it, but the XLEV8 Excel add-in contains the Create Shortcuts macro to create the shortcuts for you. You just have to specify where the shortcuts should be saved, where the shortcuts should point to, and the the label for the shortcut in a settings sheet that is created the first time the macro is run. It will check to ensure the save-in and point-to folders exist, so if you use both, you’ll want to run the Create Subfolders macro first.
Aside from giving you shortcuts you can use with the keyboard to save a little time, creating the shortcuts helps you refer back to the previous folder to reference the names used for files. When saving the current period’s file, you can use the _Previous shortcut to jump to the previous period, click the file, use the _Next shortcut to jump back to the current period, and just modify the date portion of the filename. If those files are used in references or macros, you’ll want to make sure they are named exactly right, and this method helps you do that.
If you want to build your own macros to create shortcuts, you’ll want to reference the WScript.Shell object and CreateShortcut function. An example that creates a shortcut is below. This will create a shortcut to the Cats folder within the Dogs folder. If either of the paths does not exist, you will get an error.
Example Files
Extreme Shortcuts Example – Before
Extreme Shortcuts Example – After
Recent Comments