Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I determine if the number of files in a series of folders exceeds a specified value?

-- DP

SpacerHey, Scripting Guy! AnswerScript Center

Hey, DP. Buongiorno. Le come sta? Tutto bene?

What does all that mean? Io non so; Io non parlo Italiano. In other words: I don’t know; I don’t speak Italian.

No, really: “Io non so; Io non parlo Italiano” actually does mean “I don’t know; I don’t speak Italian.” As for the first sentence, that can be translated (at least by us) as this: “Good day. How are you? Everything OK?”

So why the sudden interest in Italian? Well, even as you’re reading this the Scripting Guy who writes this column is getting ready to wing his way towards Italy, off for a 10-day vacation in Rome and Venice. A couple of years ago the Scripting Guy who writes this column escorted several family members to London and Paris. He had a great time, but found it absolutely exhausting trying to get assorted and diverse family members to agree on where to go, what to do, when to eat, etc. “That was fun,” he said upon returning home. “But I’m never doing that again.”

And now, needless to say, he’s doing that again. And, along the way, he’s also trying to learn a few words of Italian, the better not to look like a typical clueless American tourist. (Obviously he is a typical clueless American tourist. He just doesn’t want to look like one.)

Originally, the Scripting Guy who writes this column hoped to write today’s entire column in Italian; as it turns out, however, the teach-yourself-Italian book he picked up at the bookstore doesn’t include the Italian equivalent for phrases like “Use an Associators Of query to retrieve a collection of all the subfolders in a specified folder.” But maybe that’s just as well; after all, this column can be difficult enough to understand when he writes it in English, let alone if he tried writing it in Italian.

So, in English, here’s a script that should solve your problem, DP:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colSubfolders = objWMIService.ExecQuery _
    ("Associators of {Win32_Directory.Name='C:\Absentee Reports'} " _
        & "Where AssocClass = Win32_Subdirectory " _
            & "ResultRole = PartComponent")

For Each objFolder in colSubfolders
    Set colFiles = objWMIService.ExecQuery _
        ("ASSOCIATORS OF {Win32_Directory.Name='" & objFolder.Name & "'} Where " _
            & "ResultClass = CIM_DataFile")

    If colFiles.Count => 4 Then 
        Select Case colFiles.Count
            Case 4
                Wscript.Echo objFolder.Name & " has 4 files in it."
            Case 5
                Wscript.Echo objFolder.Name & " has 5 files in it."
            Case 6
                Wscript.Echo objFolder.Name & " has 6 files in it."
            Case 7
                Wscript.Echo objFolder.Name & " has 7 files in it."
            Case 8
                Wscript.Echo objFolder.Name & " has 8 files in it."
        End Select
    End If
Next

As you can see, we start out by connecting to the WMI service on the local computer. But have no fear: we can just as easily run this script against a remote computer. To do so, all we have to do is assign the name of the remote computer the variable strComputer, like this:

strComputer = "atl-fs-01"

According to DP, one of their servers houses a folder (in our example, C:\Absentee Reports) that features a subfolder for each employee in the organization. In other words, a folder structure similar to this one:

C:\Absentee Reports
C:\Absentee Reports\Gail Erickson
C:\Absentee Reports\Jonathan Hass
C:\Absentee Reports\Ken Myer
C:\Absentee Reports\Pilar Ackerman

Each time an employee is absent from the job a new document detailing the absence is added to the appropriate subfolder; for example, if Ken Myer is absent a new document will be added to the subfolder C:\Absentee Reports\Ken Myer. What we need to do is look inside each of these subfolders, count the number of documents in each one, and then take action based on that number. And that’s a good question: how in the world are we going to do all that?

Well, for starters, we’re going to use this line of code to return a collection of all the subfolders found in C:\Absentee Reports:

Set colSubfolders = objWMIService.ExecQuery _
    ("Associators of {Win32_Directory.Name='C:\Absentee Reports'} " _
        & "Where AssocClass = Win32_Subdirectory " _
            & "ResultRole = PartComponent")

And no, that’s not Italian; Italian – even the Scripting Guy who writes this column’s Italian – would make more sense. Instead, this crazy-looking code is an Associators Of query and, for better or worse, it’s supposed to look like that. But you don’t have to worry about the details; just note that we’re retrieving a collection of subfolders (instances of the Win32_Subdirectory class) for the parent folder C:\Absentee Reports. Note, too that this returns only the “top-level” subfolders; if you have nested subfolders (subfolders inside of subfolders) then you’ll need to use a different approach to get at all the subfolders and sub-subfolders.

Once we issue our query and get back a collection of all the subfolders in C:\Absentee Reports, our next step is to set up a For Each loop to walk through all the folders in that collection. The first thing we do inside that loop is issue yet another query, this one returning a collection consisting of all the files found in the first subfolder:

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='" & objFolder.Name & "'} Where " _
        & "ResultClass = CIM_DataFile")

Yes, another crazy (pazzesco) Associators Of query. But let’s assume that the first subfolder in our collection is a folder named Gail Erickson; that means that the folder’s Name property (something we more commonly refer to as the file path) is equal to C:\Absentee Reports\Gail Erickson. That means that our Associators Of query is asking for all the instances of the CIM_DataFile class that are associated with (stored in) the folder C:\Absentee Reports\Gail Erickson (represented by the variable objFolder.Name). In other words, connect to the C:\Absentee Reports\Gail Erickson folder and give us back a collection of all the files (files being instances of the CIM_DataFile class) that you find there.

The rest is easy (facile). All WMI collections have a property (Count) that tells us the number of items in the collection. We want to take a different action based on whether a folder has 4, 5, 6, 7, or 8 files in it; if a folder has less than 4 files in it then we don’t want to do anything at all. Consequently, as soon as we get back the collection we examine the value of the Count property to see if that value is equal to or greater than 4:

If colFiles.Count => 4 Then

If it is, we then set up a Select Case statement based on the value of the Count property:

Select Case colFiles.Count
    Case 4
        Wscript.Echo objFolder.Name & " has 4 files in it."
    Case 5
        Wscript.Echo objFolder.Name & " has 5 files in it."
    Case 6
        Wscript.Echo objFolder.Name & " has 6 files in it."
    Case 7
        Wscript.Echo objFolder.Name & " has 7 files in it."
    Case 8
        Wscript.Echo objFolder.Name & " has 8 files in it."
    Case Else
        Wscript.Echo objFolder.Name & " has more than 8 files in it."
End Select

As you can see, we aren’t doing anything very fancy here. If, for example, the Count is equal to 4 we simply echo back a message stating that the folder has 4 files in it. That’s not very exciting, but that’s not the point; the point is to show you how you can determine the number of files in a folder and then take specific action based on the number of files. Needless to say, to do something a little fancier (and a little more meaningful), all you have to do is replace our sample code with something a little fancier (and a little more meaningful).

From there we simply loop around and repeat the process with the next subfolder in the collection. When all is said and done we’ll get back a report similar to this:

C:\Scripts\Ken Myer has 6 files in it.
C:\Scripts\Pilar Ackerman has 6 files in it.

At that point we’re done (fatto).

Or at least we think that fatto means “done”. No doubt the Scripting Son, who fancies himself as being a bit of a comedian, would consider fatto to be a description of the Scripting Dad’s waistline instead.

Right, Scripting Son. Ha-ha.

We hope that answers your question, DP. We’d actually love to stay and discuss this in more detail, but it’s time for the Scripting Guy who writes this column to head to the airport (aeroporto). But don’t worry: he’ll make sure that there’s a new Hey, Scripting Guy! each and every day while he’s gone. Why is he doing all that work even though he’s on vacation? That’s easy: it’s because he – hmmm .…

You know, now that we think about it, there’s really only one answer to that question: because the Scripting Guy who writes this column isn’t particularly astuto (smart).

As the Scripting Son (not to mention the Scripting Editor) could have told you years ago.

Oh, well; que sera sera. Arrivederci, everyone!