Let's Get Together

  • Article

By The Microsoft Scripting Guys

Sesame Script

Welcome to Sesame Script, the column for beginning script writers. The goal of this column is to teach the very basics of Windows scripting for system administration automation. We’ll provide you with the information you’ll need to begin reading and understanding scripts and to start modifying those scripts to suit your own needs. If there’s anything in particular about scripting you’re finding confusing, let us know; you’re probably not alone.

Check the Sesame Script Archive to see past articles.

On This Page

Let’s Get Together
Piecing Together a Script
One Step at a Time
Always Start with Step One
Take the Next Step
Put the First Couple of Pieces Together
Step into Excel
The Final Step

Let’s Get Together

The Scripting Guys are all about togetherness - we work together, eat together, … okay, that’s about it. And we only eat lunch together. Sometimes. But we still believe in togetherness. As a matter of fact, we believe in it so strongly that we created thousands of scripts that are made to fit together. Kind of like a giant jigsaw puzzle, one where you can put the pieces together in almost any order you want and they’ll fit. And since we’re also notoriously lazy, in our opinion that’s just about the best kind of jigsaw puzzle there is.

Piecing Together a Script

In this article we’re going to talk about piecing together existing scripts to create the script you want. Sounds simple enough, right? Unless of course you’re one of the thousands of people who’ve written to us asking something like this:

“I loved the Hey, Scripting Guy! article you did on x, but how do I get that script to do y, too?”

Or this:

“How do I write a script that will do x, then y, and then output it to z?”

Sure, it’s easy enough to copy a script out of the Script Repository and run it, but what happens when you need it to do a little bit more? Do you have to run script x, then run script y? Sounds pretty inefficient, and it probably won’t work the way you want it to anyway.

The best way to go about learning how to do this is to just give it a try. Nevertheless, we’re going to take you through some basic steps to help get you started.

One Step at a Time

Step one is always the best place to start (unless you’re like a certain Scripting Guy who, when climbing the stairs, always skips the first step, and every other step thereafter). But assuming you climb stairs like a regular person, you start with the first step. Step one here is to figure out what problem you’re trying to solve. This is typically the easiest step.

For example, let’s say you want to find all the files in the C:\Scripts folder created before June 1, 2006; write the names and dates of those files to a Microsoft Excel spreadsheet for your records; then delete those files.

You’ve now completed your first step: you know what it is you want your script to do. At this point the step-skipping Scripting Guy would just start writing a script, but the rest of us aren’t quite ready for that yet. We need to go to the next step and break our problem down into mini problems. Here’s what we really want to do:

  • Retrieve all the files in the C:\Scripts folder

  • Determine the creation date of those files

  • Compare that date to June 1, 2006

  • Write the file names and dates to an Excel spreadsheet

  • Delete the files from a folder

It looks like a lot of steps, but if you take them one at a time none of them seem too daunting. Let’s start with the first one: Retrieve all the files in a folder.

Always Start with Step One

When you work on a jigsaw puzzle, do you dump all the pieces out of the box and put the box away without ever really looking at it? Well, we certainly don’t. We look at the picture on the box, then leave the picture sitting in front of us while we work on the puzzle. This helps us determine where in the puzzle certain pieces will end up. Is this cheating? A few slightly-crazy people might say yes, but we say that anything that makes solving the problem a little easier is perfectly fine. (We might even leave some pieces stuck together if they happen to fall out of the box that way.)

The same thing goes for scripting. If you can find a good script to start with, by all means use it. (And if you can find a script that does everything you want, congratulations.) That said, if you look in the Script Repository, you’ll find a script called List All the Files in a Folder:

strComputer = "."

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In colFileList
    Wscript.Echo objFile.Name
Next

This script connects to the WMI service on the local computer (that’s what the first two lines do). It then calls the ExecQuery method on the WMI service. It’s not vital that you completely understand the query at this point. What we’re trying to do here is pick out the parts of the script we need and that we know work and use them in our own scripts. But there are two things you should note in this query. The first is that we’re looking for files, which are represented in WMI by the CIM_DataFile class. That’s what this part of the query is telling us:

"ResultClass = CIM_DataFile"

This means that the results of our query will be data files, or CIM_DataFile objects. The second is that we’re looking only in the folder C:\Scripts. That’s what this part of the query is for:

Win32_Directory.Name='C:\Scripts'

The result is a query that returns all the files in the C:\Scripts folder. This seems like a really good start, doesn’t it? But where do we go from here?

Take the Next Step

We found a script in the Script Repository that did part of what we were looking for, but not everything. Seeing as how, as of July 21, 2006, there will be 500 Hey, Scripting Guy! articles available, the Hey, Scripting Guy! archives seem like a great place to look. And what do you think we found? How Can I Delete All Files Older Than a Specified Date?:

strDate = "20031102000000.000000+000"

strComputer = "."
Set objWMIService = GetObject _
    ("winmgmts:\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
    ("Select * From CIM_DataFile Where CreationDate < '" & strDate & "'")
For Each objFile in colFiles
    Wscript.Echo objFile.Name
'    objFile.Delete
Next

Wow, did we ever luck out on that one? That’s almost everything we want to do right there!

Note: If you look closely you’ll notice that we put a comment mark before the actual Delete statement. This is so no one inadvertently copies this script and deletes a lot of files they didn’t intend to. We get enough angry emails as it is.

We’re not going to repeat the explanation of how this script works. For that, see the Hey, Scripting Guy! article. (The article also includes a couple variations on this script if you’re interested.) But to keep you from being completely confused, we’ll at least explain the first line in the script, which looks a little strange. The value assigned to strDate is the date in UTC (Universal Time Coordinate) format. Yes, it’s ugly, but that’s how WMI likes to see dates. The date shown in this script is November 2, 2003. It’s actually pretty easy to figure out once you know how. The first four characters are the year, the next two are the month, and the two after that are the day. The rest of the string represents the time, but we’re not going to worry about what time the files were created, we only care about dates. So to change the date this script is looking for to June 1, 2006, you would change it to this:

strDate="20060601000000.000000+000"

Hey, look at that, we just made our first modification to an existing script. It was so painless you didn’t even notice, did you?

Put the First Couple of Pieces Together

We now have a script that finds all the files in the C:\Scripts folder, and another script that deletes all files on a computer created before June 1, 2006. How do we combine them to do what we need?

Getting started is pretty easy. The first two lines of both scripts are identical:

strComputer = "."

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

That’s a pretty good sign that you still need those two lines. (Remember, those are the lines that connect to the WMI service on the local computer.) Next, both the scripts have a query statements:

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

and

Set colFiles = objWMIService.ExecQuery _
    ("Select * From CIM_DataFile Where CreationDate < '" & strDate & "'")

Both of these queries return a list of files, or CIM_DataFile objects. We don’t really want to search the whole computer for files created before a certain date, then just delete the files in the C:\Scripts folder. Wouldn’t it make more sense to just search the C:\Scripts folder? We thought so too. So we’re going to keep the first query and throw out the second. But we still need some information from that query, which we’ll see in just a second. Right now our script looks like this:

strComputer = "."

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

So far this is simply the script from the Repository, we haven’t actually put anything together yet. Well, now that we have the list of files in the folder, we need to loop through that list. We do that with a For Each statement, which, once again, we saw in both our scripts:

For Each objFile In colFileList
    Wscript.Echo objFile.Name
Next

and

For Each objFile in colFiles
    Wscript.Echo objFile.Name
'    objFile.Delete
Next

There are only two minor differences here: the name of the collection of files, and the fact that the second statement has a Delete statement in it. We need the collection name to match the name we used in our query, in this case colFileList, so we’ll keep the first For Each statement. But we’re also going to need the Delete statement, so we put that in our new, combined script:

strComputer = "."

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In colFileList
    Wscript.Echo objFile.Name
'    objFile.Delete
Next

We still mostly have our Repository script with one line added, the line to delete the files. However, at this point we’d be deleting all the files in the C:\Scripts folder. We know that we need to check the date first. How do we do that? Well, first we need the date. As we already saw, this is how we specify the date June 1, 2006:

strDate="20060601000000.000000+000"

Now we need to compare that date to the creation date of the file. But where do we get that? Well, that’s where we get back to that query we discarded:

Set colFiles = objWMIService.ExecQuery _
    ("Select * From CIM_DataFile Where CreationDate < '" & strDate & "'")

Notice in particular this part of the query:

Where CreationDate < '" & strDate & "'

This part of the query is using the CreationDate property of the CIM_DataFile object to make the comparison to our specified date. If we can use the property in a query, we can certainly use it outside the query:

For Each objFile In colFileList
    If objFile.CreationDate < strDate Then
        Wscript.Echo objFile.Name & " " & objFile.CreationDate
'        objFile.Delete
    End If
Next

In our For Each loop, we loop through the files, then for each file in the collection we check to see whether the CreationDate for that file is less than strDate, or June 1, 2006. If it is, we echo the file name and creation date and delete the file. We then loop around to the next file in the collection.

We now have a script that deletes all the files from the C:\Scripts folder that were created before June 1, 2006:

strDate = "20060601000000.000000+000"

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In colFileList
    If objFile.CreationDate < strDate Then
        Wscript.Echo objFile.Name & " " & objFile.CreationDate
'        objFile.Delete
    End If
Next

Let’s take a quick look at what we’ve done so far. We took a script from the Script Repository that returned a list of all the files in the C:\Scripts folder. We then found a Hey, Scripting Guy! article that deletes all files from the local computer that were created before November 2, 2003. We changed the date by changing the value of the variable strDate so that we’re deleting files created before June 1, 2006. We then used the CreationDate property we found in that script to check the creation dates of the files returned from the C:\Scripts folder.

Note: When we specified the CreationDate for our comparison we had to use UTC format so WMI could read it. This tells us that the CreationDate property is stored in UTC format, so that’s what the output of our Wscript.Echo statements will be. We won’t go over it here because we need to move on to our Excel output, but check out this script for an example of changing a UTC date into a more readable format. It will be good practice for you after all you’ve learned in this article!

This isn’t so bad, is it? See, one step and a time and things start to come together.

Step into Excel

Let’s look back at our list and see what’s left:

  • Retrieve all the files in the C:\Scripts folder

  • Determine the creation date of those files

  • Compare that date to June 1, 2006

  • Write the file names and dates to an Excel spreadsheet

  • Delete files from a folder

Of the five steps in our list, we’ve completed all but one: Write the filenames and dates of the files we’re going to delete to an Excel spreadsheet.

This step is going to be a little tricky. There’s a lot of information about scripting with Excel in the Script Center. However, in browsing around we don’t see anything that jumps right out at us called “How to send output to an Excel spreadsheet.” What we did find was Add Data to a Spreadsheet Cell in the Script Repository:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

How do you figure out what’s going on here? The simplest way to start would be to just try running the script and see what happens. If you do that, this is what you’ll see:

Excel Spreadsheet

The script opens up Excel and adds a value to the first cell in the worksheet. You can see that as you look through each line in the script. It starts by creating an Excel.Application object - this opens Excel. The next line sets the Visible property to True. By default when you open Excel from a script it runs as a hidden process, so we need to specifically tell it to show itself; that’s what setting the Visible property to True does. At this point we have an instance of Excel, but no workbooks, so we call Workbooks.Add to add a workbook that we will be able to add data to. The last line adds that data, putting the string “Test value” into the cell in the first column of the first row:

objExcel.Cells(1, 1).Value = "Test value"

Now how do we use this? For starters, we know we need to open Excel and add a workbook (and we’ll make it Visible so we can see what’s going on). How do we do that? No, think about it a minute and try it yourself before you look at the answer.

Ready?

Did you figure it out?

All right, we’ll tell you just to make sure you got it. We took the code that opened Excel, made it visible, and added a workbook, and added that code to our script. Our script now looks like this:

strDate = "20060601000000.000000+000"
strComputer = "."

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add

For Each objFile In colFileList
    If objFile.CreationDate < strDate Then
        Wscript.Echo objFile.Name & " " & objFile.CreationDate
'        objFile.Delete
    End If
Next

Our script now echoes the Name and CreationDate, and it opens a new workbook in Excel.

Note: If you put your Excel code someplace else in the script that’s fine. As long as you put it somewhere before the For Each loop you’re in great shape.

All that’s left is to write the Name and CreationDate to our open instance of Excel. We saw how to write to a single cell:

objExcel.Cells(1, 1).Value = "Test value"

The 1,1 in this line of code represents row 1, column 1 in our worksheet. In our case, we want to write the name of the first file we find to row 1, column 1, and the creation date of that file to row 1, column 2. When we loop through a second time, we want to write the file name to row 2, column 1, and the creation date to row 2 column 2. When we loop through a third time…well, you get the idea. As you can see, all we really need to do is keep track of which row and column we’re on as we write our output. Something like this:

i = 0
For Each objFile in colFileList
    If objFile.CreationDate < strDate Then
        i = i + 1
        objExcel.Cells(i, 1).Value = objFile.Name
        objExcel.Cells(i, 2).Value = objFile.CreationDate

        Wscript.Echo objFile.Name
        Wscript.Echo objFile.CreationDate
'        objFile.Delete
    End If
Next

We start by setting a variable i to 0. We’ll use this variable to keep track of what row we’re on. The first time through the For Each loop we add 1 to i, making the value of i equal to 1. Then we call this line:

objExcel.Cells(i, 1).Value = objFile.Name

Look familiar? Let’s refresh our memories:

objExcel.Cells(1, 1).Value = "Test value"

That’s right, it’s the same line we had in our Excel script from the Repository, with a couple of minor modifications. Instead of assigning the string “Test value” to the cell value, we assign it objFile.Name, which we already know is the name of the file because that’s the value we’ve been echoing. Also, instead of assigning the name to cell 1,1 we’re assigning it to cell i,1. We do this because, if we’re deleting more than one file, we’ll need more than one row in our Excel spreadsheet. The first time through the value of i is 1, so we’re adding the name to row 1, column 1. The second time through we add 1 to i again, giving it a value of 2, so the next file name will be added to row 2 column 1.

The next line is the same except we’re assigning CreationDate to the cell value and we’re putting it in column 2:

objExcel.Cells(i, 2).Value = objFile.CreationDate

Now that we’re writing our file name and creation date to Excel we probably don’t need the Wscript.Echo statements anymore. Here’s our final script:

strDate = "20060601000000.000000+000"
strComputer = "."

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

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add

i = 0
For Each objFile in colFileList
    If objFile.CreationDate < strDate Then
        i = i + 1
        objExcel.Cells(i, 1).Value = objFile.Name
        objExcel.Cells(i, 2).Value = objFile.CreationDate

        Wscript.Echo objFile.Name
        Wscript.Echo objFile.CreationDate
'        objFile.Delete
    End If
Next

The Final Step

There might be more you want to do with this example, such as format the cells. Your final step is to go out on your own and add the pieces you need. As a matter of fact, why don’t you give this a try: Update this script (or start from scratch if you prefer) to do this:

  • Retrieve all the files from the C:\Scripts folder

  • Write the name, creation date, and size of the files created before June 1, 2006 to an Excel spreadsheet

  • AutoFormat the Excel columns for the proper width

  • Sort the data by file size

  • Copy the files to the C:\Scripts\Temp folder (rather than delete them)

Here’s a hint: Take a look at the Office Space archive, or the Hey, Scripting Guy! archive. Send your solution to scripter@microsoft.com (in English, if possible), we’d love to see how you did!