Hey, Scripting Guy! How Can I Move Files Based on Their Three-Part File Name?

ScriptingGuy1

Hey, Scripting Guy! I have a folder filled with invoice files. Each of these files has been given a file name based on a specific three-part naming convention: a three-letter abbreviation of the vendor name; the invoice date (e.g., 06032008) ; and the invoice number (which can be of varying length). In other words, all the files have names like this: ABC06032008I5963.pdf. I also have a series of folders for each vendor, a series of subfolders for each year, and a folder for each month of each year for each vendor. I need a script that can go through the invoice folder and move each file to the appropriate folder. Can you help me with this?
— JC

Hey, JC. Great question! Here’s the script:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Invoices")

For Each objFile in objFolder.Files
    strVendorName = Left(objFile.Name, 3)
    strMonth = MonthName(Mid(objFile.Name, 4, 2))
    intYear = Mid(objFile.Name, 8, 4)

    strFolderPath = "C:\Archive\" & strVendorName & "\" & intYear & "\" & strMonth & "\"

    If Not objFSO.FolderExists(strFolderPath) Then
        Set objFolder2 = objFSO.CreateFolder(strFolderPath)
    End If
  
    objFSO.MoveFile objFile.Path, strFolderPath
Next

Before we explain how this script works let’s review the scenario. JC has a folder named Invoices which, not too surprisingly, is filled to the brim with invoice files. Each of these files has a name similar to this one:

ABC06032008I5963.pdf

As it turns out, ABC06032008I5963.pdf happens to be the Scripting Editor’s real first name. (She goes by Jean, but, then again, if your first name was ABC06032008I5963.pdf you’d probably go by Jean, too.) However, this isn’t why JC chose the name; that’s just a coincidence. Instead, there’s method to JC’s seeming-madness:

In addition to the folder C:\Invoices, JC also has a second folder: C:\Archive. Inside this folder are subfolders for each of his vendors; for example, there’s a folder named C:\Archive\ABC and another named C:\Archive\WSH. In turn, each of those subfolders has its own set of subfolders, one for each year. For example, C:\Archive\ABC might have the following subfolders:

Finally, each of these sub-subfolders has as many as 12 sub-sub-subfolders, one for each month of the year. For example, there’s a subfolder named C:\Archive\ABC\2008\June. Is that important to us? You bet it is. What JC needs is a script that can parse the file name ABC06032008I5963.pdf and then, based on the information contained in that file name, move this invoice to the folder C:\Archive\ABC\2008\June.

Got all that? Good. At least someone is having some success today.

So how does the script work? Well, because the script only has to work locally, we start out by creating an instance of the Scripting.FileSystemObject.

After creating an instance of the FileSytsemObject we then use the GetFolder method to bind to the folder C:\Invoices:

Set objFolder = objFSO.GetFolder("C:\Invoices")

And once we’ve done that, well, all we can say is, “Hold on to your hats; things are about to get a little bit exciting.”

Well, OK. But we did say a little bit exciting.

Our first step on the road to our excitement – heck, the first step on any road to excitement – is to set up a For Each loop to loop us through all the items in the folder’s Files collection. Inside that loop, we immediately execute these three lines of code:

strVendorName = Left(objFile.Name, 3)
strMonth = MonthName(Mid(objFile.Name, 4, 2))
intYear = Mid(objFile.Name, 8, 4)

This, as you might have guessed, is where we tease apart the file name. In line 1, we use the Left function to grab the first three characters from the file name (the Name property) and store those characters in the variable strVendorName. Why? You got it: because the first three characters in the file name represent the vendor abbreviation.

In line 2, we actually do a couple things. To begin with, we use the Mid function to grab two characters from the file name. Which two characters? Characters 4 and 5. (The parameter 4 means we should start with the fourth character in the string; the 2 means that we want to grab two characters.) As soon as we have those two characters we then use the MonthName function to return the name of the month that corresponds to the numeric value; for example, if characters 4 and 5 are 06 (which they are) then MonthName returns June. Why? Because June is the sixth month of the year.

No, really; you can look it up.

Finally, we use the Mid function again, this time starting with the 8^th character and taking a total of 4 characters. And because characters 8,9, 10, and 11 correspond quite nicely with the year, we stash this information in a variable named intYear.

That brings us to this line of code:

strFolderPath = "C:\Archive\" & strVendorName & "\" & intYear & "\" & strMonth & "\"

What we’re doing here is constructing the path to the folder where the first file in the collection needs to be moved. To construct that path we simply combine the following items:

By the way, don’t leave that final \ off. If you do, the FileSystemObject will try to replace the folder C:\Archive\ABC\2008\June with the file itself. Needless to say, that’s not going to work.

After we construct the path we use this line of code to determine whether or not the folder C:\Archive\ABC\2008\June already exists (note the somewhat-clumsy syntax If Not Exists, a goofy way of saying “If this doesn’t exist …”):

If Not objFSO.FolderExists(strFolderPath) Then

And what if the folder doesn’t exist? Why, then we create it, of course:

Set objFolder2 = objFSO.CreateFolder(strFolderPath)

Once that’s done we can then use the MoveFile method to move the file to its new home:

objFSO.MoveFile objFile.Path, strFolderPath

As you can see, we simply pass MoveFile two parameters: objFile.Path, the complete path to the file; and strFolderPath, the variable containing the path to the appropriate Archive folder.

And then from there it’s back to the top of the loop, where we repeat the process with the next file in the collection. Etc., etc.

Good point: guess we do owe you an apology, don’t we? After all, this turned out to be more than just a little bit exciting. A lot more.

ScriptingGuy1

Follow