Scripting Excel: The Saga Continues

 

Greg Stemp
Microsoft Corporation

May 17, 2004

Summary: Demonstrates how script writers can retrieve information from Active Directory and then display that information in a formatted Excel spreadsheet. (16 printed pages)

I guess when you call yourself a Microsoft® Scripting Guy and write a column called Scripting Clinic, it shouldn't come as a surprise that people assume your life revolves around scripting. In truth, though, I don't have much to do with scripting outside of work. For example, I never read books about scripting; instead, I**read things like The Fabric of the Cosmos by Brian Greene, a fascinating book about quantum mechanics, string theory, and other things I don't understand in the least.

**Note   **The only exception is the section of the book dealing with Schrödinger's cat. Edwin Schrödinger was a physicist who proposed the following thought experiment: You place a cat in a locked box, a box in which there is a 50-50 chance that a radioactive gas that would kill the cat instantly has been released. Schrödinger's thesis was that, until you actually looked in the box, the cat was neither dead nor alive, but was instead in some sort of indeterminate state. I understand this, because, when I was a kid, I'm pretty sure we owned Schrödinger's cat. Only with our cat, even when you did look at him you couldn't always tell whether he was dead or alive, so maybe it wasn't Schrödinger's cat after all.

By the way, if you're wondering how that cat is doing now, well, it's kind of hard to tell.

Currently, I'm at the point in the book where Greene discusses "time's arrow." Apparently many physicists see no reason why time can't go backwards just as easily as it goes forward; even Albert Einstein himself felt there was no difference between the past, the present, and the future. (And the way my life has gone so far indicates he was probably right.)

As you might expect, when you read about the arrow of time, it gets you to thinking. You think about the nature of the universe, you think about the moral and ethical implications of time travel, you think, "Hmmm, in last month's column we told people how to use a script to pull data out of a Microsoft® Excel spreadsheet and then use that data to create user accounts in Microsoft® Active Directory®. If time can be reversed, I wonder if this process could be reversed as well. I wonder if a script could pull data out of Active Directory and then use that data to populate an Excel spreadsheet."

As you might expect, everyone around me said I was crazy (and that was before I mentioned this idea). And so I decided to go back in time to 1905 and ask Einstein if he thought you could use a script to write data to Excel. After all, I didn't want to make a fool of myself in this column. Unfortunately, Einstein was traveling forward in time, and we missed each other. (I left a message for him on his answering machine, but because the answering machine hadn't been invented yet, I'm not sure when he'll get back to me.) And so I decided, "What the heck; let's give this a try and see what happens. After all, it doesn't work, I'll just travel back in time and delete this column before it gets posted to MSDN." So if you're not reading this column right now, you know why.

Working with Excel: A Quick Review

Last month we learned how to write a script that pulls data out of an Excel spreadsheet. (If you don't recall last month's column, you can either travel back in time, or click here.) In that column, we pointed out that you can create an instance of Excel using just two lines of code:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

Of course, this is an example of Schrödinger's Spreadsheet: until you look, you can't tell whether this really is a spreadsheet or not. That's because, by default, any time you create an instance of Excel, that instance runs in an invisible window. To actually see that instance of Excel, you need to set the Visible property to TRUE, like this:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True

These three lines of code display a blank spreadsheet on the screen. That's pretty exciting in and of itself, but it would be even more exciting if that spreadsheet actually contained some data. According to quantum mechanics, there is a slim possibility that the atoms of the spreadsheet could spontaneously rearrange themselves to display meaningful data. Rather than wait for that possibility, however, let's see if we can add some meaningful data to the spreadsheet on our own.

Adding Data to a Spreadsheet

As it turns out, you can easily add data to a spreadsheet without rearranging all the atoms: you simply reference a cell and then set the value accordingly. For example, suppose we want to type the word Schrödinger into row 1, column 1. Try this script and see what happens:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Schrödinger"

When you run this script, you should see something very similar to this:

Figure 1. Writing data to an Excel spreadsheet

Pretty cool, huh? If we want to add additional data, we just need to reference a few more cells and set the appropriate values. For example, this script adds the names of four famous physicists to our spreadsheet:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Schrödinger"
objExcel.Cells(1, 2).Value = "Bohr"
objExcel.Cells(1, 3).Value = "Heisenberg"
objExcel.Cells(1, 4).Value = "Einstein"

When you run this script, you should get something that looks like this:

Figure 2. Writing data to multiple cells in an Excel spreadsheet

Now, that's really co... Well, okay, you're right: that really isn't all that cool, is it? It's great that we can add data to a spreadsheet, but let's think about this for a moment. Why do we want to display data in Excel rather than displaying that data in a command window or saving it to a text file (the two methods most commonly used by those writing system administration scripts)? Most likely we want to do that because neither of those options let you do much to format data. Data output to the command window or saved to a text file lacks a certain aesthetic appeal, to say the least. That's why we want to use Excel; Excel enables you to create nicely formatted, easy-to-read output. But we don't exactly have nicely formatted output here; after all, you can't even read the names Schrödinger or Heisenberg. Therefore, before we go much further, let's take a bit of a quantum leap and talk about formatting in Excel.

Formatting in Excel

If you've ever used Excel, you know how easy it is to format something: you just select a cell or two and apply a format of some kind (change the font size, change the background color of the cell, make the text boldface, whatever). You do the same thing when programmatically working with Excel. For example, you can format the active cell (the one you are currently typing in) by just by applying some formatting commands. The following script types the word Schrödinger into the cell in row 1, column 1 and then:

• Makes the text boldface (by setting the Bold property to TRUE).
• Makes the font size 24 point (by setting the Size property to 24).
• Makes the font color red (by setting the ColorIndex property to 3).

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Schrödinger"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3

When you run the script, your screen will look like this:

Figure 3. Writing formatted data to an Excel spreadsheet

Takes your breath away, doesn't it?

Best of all, with the exception of the ColorIndex property (which we'll talk about in a moment), this is very straightforward. Want to make the text italic? Use this line of code:

objExcel.Cells(1, 1).Font.Italic = TRUE

Want to use Times New Roman as the font? Use this line of code:

objExcel.Cells(1, 1).Font.Name = "Times New Roman"

**Note   **Unfortunately, we don't have time to go into all the many formatting options at your disposal. However, you will find some good information about the Excel object model here.

Okay, but what about that ColorIndex property? Although there are a couple different ways to work with colors in Excel, ColorIndex is probably the easiest (which might tell you a little something about the other methods). In Excel, there are 56 built-in colors (index numbers 1–56) available to you; all you have to do is set the value of the ColorIndex property to the desired index number. The only problem is this: How do you know that index number 3 is red, and that index number 4 is a really, really interesting shade of green?

Good question. In a cursory search of MSDN, I couldn't find those index numbers documented anywhere. But that's all right; here's a simple little script that shows you what all 56 index numbers represent:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
For i = 1 to 56
    objExcel.Cells(i, 1).Value = i
    objExcel.Cells(i, 1).Interior.ColorIndex = i
Next

When you run the script, you'll get output like this:

Figure 4. Excel ColorIndex values

As you can see, if you have a desperate need to set the font color to turquoise, well, then just set the ColorIndex to 8.

Incidentally, if you looked closely at the preceding script, then you now know how to set the background color of a cell: just use the Interior.ColorIndex property. For example, to color a cell red, use this code:

objExcel.Cells(1, 1).Interior.ColorIndex = 3

I know; it is a wonder you could have survived so long without knowing that.

Let's do one more thing to our formatting script before we move on. As you might recall, our test script gives us output that looks like this:

Figure 5. Writing formatted data to an Excel spreadsheet

What's wrong with that? Well, for one thing, the word Schrödinger doesn't all fit in column 1. That's fine for now, but what happens if we type something into row 1, column 2? Then we get this:

Figure 6. Improperly sized columns in an Excel spreadsheet

Not exactly the kind of output we were hoping for. Obviously what we need to do is to make column 1 a little bit wider. But how?

Working with Ranges

I'm glad you asked that question, because now it's time to talk about ranges. So far we've been formatting only the active cell, which works great for changing font sizes and cell colors and things like that. There will be times, however, when you need to work with more than one cell. Maybe you want to change the font size for all the cells in given row. Maybe you want to sort all the data you've collected. Maybe, as is the case here, you want to resize an entire column. If so, you need to use a range, a specified set of cells.

Although there are several different ways to indicate the cells to be included in a range, they all have one thing in common: They require you to create an instance of the Range object and then specify which cells should be part of that range. For example, here are a few common ways to create ranges.

To create a range consisting of a single cell:

Set objRange2 = objExcel.Range("A1")

To create a range consisting of an entire column:

Set objRange = objExcel.ActiveCell.EntireColumn

As you might expect, there is a similar command that creates a range encompassing an entire row:

Set objRange = objExcel.ActiveCell.EntireRow

What if you want to select a row or column other than the one with the active cell? No problem. Create a range using a single cell found within the desired row or column; then use the Activate method to make it the active cell. At that point, set a range representing the entire row or column. For example, this code makes cell E5 the active cell and then, by selecting the entire row, creates a range consisting of all the cells in row 5:

Set objRange = objExcel.Range("E5")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireRow

To create a range consisting of a group of cells:

Set objRange = objExcel.Range("A1:C10")

Note that all you do here is specify the starting point (A1) and the ending point (C10). Excel automatically selects all the cells between those points and puts them into the range.

To create a range consisting of all your data:

Set objCell = objExcel.Range("A1").SpecialCells(11)

In this case, 11 is a parameter that represents the last cell in the spreadsheet containing data. This command creates a range that starts in cell A1 and extends through all the cells that contain data.

To demonstrate, let's revisit a previous script and see if we can make it a little snazzier. In this revised script, we'll put the physicists' names in a single column (rather than a single row), and we'll give that column a boldface label (Name). And then, just to be fancy, we'll:

• Change the background color and the font color of the cell (1,1) with the label.
• Create a range that encompasses the five cells (A1:A5) we're using and change the font size.
• Create a range that encompasses the cells with the four physicists' names (A2:A5) and change the background color.
• Select column A and use the Autofit() method to resize the column so that all the text fits.

Here's the script:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Name"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 30
objExcel.Cells(1, 1).Font.ColorIndex = 2
objExcel.Cells(2, 1).Value = "Schrödinger"
objExcel.Cells(3, 1).Value = "Heisenberg"
objExcel.Cells(4, 1).Value = "Bohr"
objExcel.Cells(5, 1).Value = "Einstein"
Set objRange = objExcel.Range("A1","A5")
objRange.Font.Size = 14
Set objRange = objExcel.Range("A2","A5")
objRange.Interior.ColorIndex = 36
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()

And here's the finished product:

Figure 7. Fancy formatting in an Excel spreadsheet

Now you know how Michelangelo must have felt when he finished the Pieta.

Sorting Data

Of course, with all due respect to Michelangelo, the Pieta is nice, but you can't sort it. However, you can sort your output in Excel. Tack these two lines on the end of your script (we'll explain what these mean momentarily), and run the script again:

Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2,,,,,,,1

You should end up with something that looks like this:

Figure 8. Sorted data in an Excel spreadsheet

To sort data in Excel, you must sort by a range. Thus you need to create a range featuring the first cell in the column you want to sort by. Because we want to sort by column A, we create a range that consists of a single cell: A1. That's what this line of code does:

Set objRange2 = objExcel.Range("A1")

The Sort method looks absolutely crazy (all those commas!), but that's because we're only sorting by a single column. When you sort something in Excel, you must specify all the sort parameters in order; if you're aren't using a parameter, then just leave it blank. The parameters themselves are summarized in the following table.

Parameter Position	Description
1	First column to sort by. This must be specified as a range.
2	Sort order for the first column. Set this to 1 for ascending (the default) or 2 for ascending.
3	Second column to sort by. This must be specified as a range.
4	Not used in scripts. Leave this blank.
5	Sort order for the second column.
6	Third column to sort by. This must be specified as a range.
7	Sort order for the third column.
8	Indicates whether or not the data being sorted has a header row. Set this to 1 to indicate the data has a header row; 0 to indicate that it does not have a header row; and 0 to have Excel make a best guess as to whether or not the data has a header row.

Here's how we interpret the sort parameters objRange2,,,,,,,1 in our sample script:

Parameter	Description
objRange2	First column to sort by. In our case, this is range consisting of cell A1.
,	Sort order for the first column. We aren't using this, so it's left blank.
,	Second column to sort by. We aren't using this, so it's left blank.
,	Not used in scripts. Leave this blank.
,	Sort order for the second column. We aren't using this, so it's left blank.
,	Third column to sort by. We aren't using this, so it's left blank.
,	Sort order for the third column.
1	Indicates that the data being sorted has a header row.

In a later script, we'll sort on two different columns, and you'll get to see an example of what a fancier sort looks like.

Oh, Yeah: Extracting Data from Active Directory

Those of you with good memories (or those of you with access to a time machine) might recall that at the start of this column, I promised to show you how to grab data out of Active Directory and pop it directly into a spreadsheet. We've taken a somewhat roundabout way of getting here, but now we're ready to do just that.

What we're going to do is to write a script that searches Active Directory and extracts information for each user account; we'll then use that information to construct a company phone directory. Note that while we'll show you the code for searching Active Directory, we're not going to discuss that code in anyway; after all, this is a column about Excel, not Active Directory. (If you'd like to know more about using scripts to search Active Directory, you might want to check out our recent webcast on the subject.)

Let's take a look at the script, and then we'll walk you through the relevant pieces:

Const ADS_SCOPE_SUBTREE = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Phone number"
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT givenName, SN, department, telephoneNumber FROM " _
        & "'LDAP://dc=fabrikam,dc=microsoft,dc=com' WHERE " _
            & "objectCategory='user'"  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("telephoneNumber").Value
    x = x + 1
    objRecordSet.MoveNext
Loop
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")
objRange.Sort objRange2,,objRange3,,,,,1

Sure, it looks complicated. But it's surprisingly easy. For example, see the lines of code printed in italic? Well, for now, just ignore them. What they do is:

• Create a visible instance of Excel (something you already know how to do).
• Label the four columns being used for output (Last name; First name; Department; and Phone number). You already know how to do this, too.
• Search the fabrikam.com domain and retrieve the SN (surname or last name), givenName (first name), department, and telephoneNumber attributes for all the user accounts. If you don't know how to do this, view the webcast.

In fact, things don't really get interesting until we reach these boldface lines:

x = 2
Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("telephoneNumber").Value
    x = x + 1
    objRecordSet.MoveNext
Loop

This is where we actually take the data retrieved from Active Directory and display it in Excel. To do that, we start by setting the value of the variable x to 2. This variable indicates the current row in the spreadsheet. How come we start with 2 instead of 1? That's easy; we put column headers in row 1. Because of that, we'll start displaying data in row 2.

Next we create a loop to cycle through all the records in the returned recordset. The first command inside the loop is this:

    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("SN").Value

This code selects row 2 (represented by x), column 1 and sets the value to objRecordSet.Fields("SN").Value. This is the SN (last name) of the first user in the recordset. The script then moves to row 2, column 2, and sets the value of that cell to the user's givenName (first name). After doing the same thing for department and telephoneNumber, the script increments x by 1 (thus making x = 3). It then loops around and processes the next record in the recordset, displaying this data in row 3. This continues until all the user data has been added to the spreadsheet.

In other words, that's how you take data out of Active Directory and display it in Excel. You retrieve the data and then simply set the value of a specified cell to the value taken from Active Directory. By using a simple Do Loop and by incrementing the row number each time, you can display information for each item in your recordset. It's that easy.

The remainder of the script is just icing on the cake. For example, this code sets the active range to cell A1, selects the entire column, and then uses the Autofit method to resize that column so that all the data shows onscreen. The script then repeats the process with columns B, C, and D.

Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

The final four lines of code sort all the returned data, first by department name and then by last name. The script creates a range encompassing all the data, then creates individual ranges for cell C1 (the first column to sort by) and cell A1 (the second column to sort). At that point, the script calls the Sort method, passing the individual range objects as parameters. (Trust me: it's easier than it might sound.)

And what if you want to automatically save the spreadsheet after you populate it? If that's the case, just include this code at the end of the script; these three lines save the spreadsheet as C:\Scripts\Phone_Directory.xls, and then terminate your instance of Excel:

Set objWorkbook = objExcel.ActiveWorkbook
objWorkbook.SaveAs("C:\Scripts\Phone_Directory.xls")
objExcel.Quit

If you don't want to terminate your instance of Excel, just leave off the last line.

And remember, you're not limited to displaying Active Directory information in Excel. For example, here's a very simple WMI script that retrieves the name and state of all the services running on a computer, and then displays that information in Excel.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
x = 1
strComputer = "."
Set objWMIService = GetObject _
    ("winmgmts:\\" & strComputer & "\root\cimv2")
Set colServices = objWMIService.ExecQuery _
    ("Select * From Win32_Service")
For Each objService in colServices
    objExcel.Cells(x, 1) = objService.Name
    objExcel.Cells(x, 2) = objService.State
    x = x + 1
Next

Pretty simple, but it gives you something to build on.

Is That All There Is?

Believe it or not, we still haven't touched upon all the cool ways you can use Microsoft Excel in your system administration scripts. For example, we haven't discussed the possibility of using Excel to create charts and graphs. Is that something we're going to do in the future? Hey, what do I look like, a time traveler? (Actually, we will talk about creating charts and graphs. The only question is whether we'll do that in this universe or in some parallel universe.)

If you happen to be in this universe and you have questions or comments on this month's column, please send them to scripter@microsoft.com. See you next month (or, depending on which way time's arrow is pointing, see you last month.)

 

Scripting Clinic

Greg Stemp has long been acknowledged as one of the country's foremost authorities on scripting, and has been widely acclaimed as a world-class... huh? Well, how come they let football coaches make up stuff on their resumes? Really? He got fired? Oh, all right. Greg Stemp works at... Oh, come on now, can't I even say that? Fine. Greg Stemp gets paid by Microsoft, where he tenuously holds the title of lead writer for the System Administration Scripting Guide.