So, I am not really going to discuss the steps on how to duplicate a folder structure using VBA because there are already plenty of articles available on how to do it. Instead, I am just going to share a little bit of information I discovered yesterday as I was working on a utility for backing up my files from one drive to another.
In this utility, I needed a way to keep the folder structure the same on both the source and the target drives, so I can automatically compare the files between them and make sure I have the latest copy in the backup location. Listing the files and comparing them was easy enough to do. It was when I have a new source file to copy to the backup location where I ran into the issue of having to create a folder or subfolder on the other drive. (I might have a new source file located in a folder a few levels deep because I don't necessarily do a backup at a regular interval.)
We all know we can use the VBA MkDiR() function to create a folder or the CreateFolder method of the File System Object (FSO) to do the same. However, both of these methods suffer from the same issue; which is, they can only create a folder one level deep at a time. For example, using MkDir("C:\MyNewFolder") will create a folder called "MyNewFolder" under the root folder of the C: drive. Issuing the following command: MkDir("C:\MyOtherFolder\MySubFolder") will get a runtime error "76: Path not found" if C:\MyOtherFolder does not yet exist.
As a result, most articles on copying a folder structure use a recursive approach where the procedure walks the directory tree, one level at a time, creating each branch on its way to the end. So, in the above example, a recursive function might first check if C:\MyOtherFolder exists and creates it if necessary. Then, it will go down and check for the existence of the MySubFolder folder and then create it as needed. Both the VBA and FSO methods can be used in this recursive approach. If you want to see an example of a recursive approach for copying a folder structure, please let me know in the comments below, and I will post some links to a few articles showing how to do it (or I might post an example code).
So, back to the topic I wanted to share... Although there is nothing wrong with using a recursive approach to create a folder structure (I like using recursion, myself, on some of my projects), I thought it would have been nice to create the entire tree (actually, just one branch, but all the way to the leaf) in one instance (as in, one command or one line of code). This is exactly what I discovered yesterday and wanted to share with all of you.
As it turns out, the DOS command MKDIR or MD can do exactly what I was looking for. So, if you open up a DOS prompt and enter the command MD C:\Folder1\Folder2\Folder3, DOS will create this folder structure, if it doesn't already exist. I thought, how cool was that? So, how can I use this in VBA? I'm glad you asked. Here's the one line command I ended up using in my backup utility.
ws.Run "cmd /c mkdir """ & strTargetFolder & """"
where ws is a Shell object I created at the beginning of my procedure as follows:
Set ws = CreateObject("WScript.Shell")
and strTargetFolder is the folder structure for the new file. For example, "C:\MyFiles\ProjectName\Phase1\StatusUpdates\"
So, although recursion is a perfectly valid approach for copying a folder structure, I was just happy to find out another way to do the same without recursion and wanted to share it with everyone, in case some of you might also find it interesting and decide to use this approach in your projects.
Of course, we can discuss the pros and cons of each approach, but that's what the comments section is for. So, please let me know your thoughts, and I will be happy to discuss them below.
As always, thank you for reading, and I hope some of you will find this article useful.
Cheers!
