Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Adding Comments to an Excel Spreadsheet

There was a time when one of the Scripting Guys dreaded the coming and going of Daylight Saving Time, not because of some weird phobia regarding sunshine vs. darkness, but simply because the time change meant he had to change the clock in his car. How hard could that be? Remarkably hard: changing the clock in that car meant holding down two of the buttons on the radio (!) while simultaneously pressing a third button to advance the time. And that was just to change the hour of the day. Changing the minutes required holding down two different buttons and then repeating the process. Pretty much by the time he got the clock switched to Daylight Saving Time it was time to switch it back again.

Obviously this wasn’t exactly a user-friendly design, but it’s understandable what the car makers were trying to do: they were trying to conserve space, and trying to cram as many features and functions as they could onto one little dashboard. That’s something script writers can appreciate: after all, we’re always trying to jam as much information as we can into our reports. There’s nothing wrong with that, except that this inevitably means creating a spreadsheet or text file that requires the user to scroll sideways as well as up and down. That works, but scrolling sideways is roughly equivalent to using three buttons on the radio in order to change the clock: it might work, but nobody likes doing it.

Microsoft Excel has tried to address this problem by allowing you to attach comments to a cell. The information included in a comment isn’t immediately displayed on screen; it shows up only when you hold the mouse pointer over that cell. For example, here’s a comment attached to a cell in a spreadsheet:

It’s not the ultimate solution, but it’s often-times a step ahead of the dreaded sideways-scrolling spreadsheet: you get the information you need and yet, at the same time, can fit all the information onto the screen. No sideways-scrolling required.

So can we add comments to cells using a script? Did you even have to ask?

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

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

k = 1
For i = 1 to 5
    For j = 1 to 3
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

objWorksheet.Cells(1, 1).AddComment "Test comment."
objWorksheet.Cells(2, 3).AddComment "This is another test comment."

Don’t let this script fool you: it looks a little complicated only because we used a couple of For Next loops to automatically add some data to the spreadsheet. Other than that, it’s actually quite easy, especially the part where we add comments to two of the cells.

The script begins the same way most of our Excel scripts begin: by creating an instance of the Excel.Application object and then setting the Visible property to True (otherwise Excel would run in an invisible window and we wouldn’t be able to see it). We use the Add method to create a new workbook, and then set an object reference (objWorksheet) to the first worksheet in the worksheets collection. That’s what we do with these two lines of code:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

At this point we have a nice, clean worksheet, one without any data on it. We then use this code to simply write some arbitrary data to the worksheet:

k = 1
For i = 1 to 5
    For j = 1 to 3
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

If you don’t fully understand what’s going on here, don’t worry about; like we said, we did this just so we could fill some cells with data.

The part you should worry about (or at least be interested in) is this:

objWorksheet.Cells(1, 1).AddComment "Test comment."
objWorksheet.Cells(2, 3).AddComment "This is another test comment."

This is the part where we actually add comments to two of the cells. In the first line, we connect to the cell in row 1, column 1 (i.e., cell A1) and then call the AddComment method. We pass AddComment a single parameter: the comment we want to add to the cell. In the second line we repeat the process, this time adding a comment to the cell in row 2, column 3 (cell B3). That gives us a spreadsheet that looks like this (note the little red triangle indicating that cell A1 contains a comment):

Cool, huh? Of course, this demonstration script might not get you all fired up about adding comments to a spreadsheet; we’ll concede that adding a phony comment to a cell containing fake data might not be the most exciting thing in the world. (Although compared to some of the other things we Scripting Guys have to do….) So let’s take a look at a more realistic example. Here’s a script that returns information about all the services installed on a computer. For each service the following information is recorded:

• The service Name is written to column 1.

• The service DisplayName is written to column 2.

• The service State is written to column 3.

• The service Description is added to column 1 as a comment.

When we run the script we’ll end up with a spreadsheet that looks like this:

Yes, much more exciting than our phony comment and fake data example.

Here’s the script. We won’t walk through it line-by-line; suffice to say that it uses WMI to retrieve service information, and then writes that information to the spreadsheet. As an added bonus, at the tail end of the script we select all the cells (using the UsedRange property) and then use the AutoFit method to autosize all the columns and give us a nice, neat display. Just an added bonus from the Scripting Guys!

Oh yeah, here’s the script:

On Error Resume Next

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

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colServices = objWMIService.ExecQuery("Select * From Win32_Service")

i = 1

For Each objService in colServices
    objWorksheet.Cells(i, 1) = objService.Name
    objWorksheet.Cells(i, 2) = objService.DisplayName
    objWorksheet.Cells(i, 3) = objService.State
    objWorksheet.Cells(i, 1).AddComment objService.Description
    i = i + 1
Next

Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit

One thing we should point out is that the first line in our script is On Error Resume Next. Typically we don’t include even this minimal amount of error-handling in our sample scripts; we do that to keep the samples as short as possible. In this case, however, we’re likely to run into trouble if we don’t use On Error Resume Next. That’s because some services don’t have descriptions; the value of their Description property is Null. WMI is fine with that, but the AddComment method isn’t: if you try to add a Null value as a comment your script will blow up. As long as we have On Error Resume Next present, however, the script will try to write the Null value and, failing, simply skip that step and move on to the next line of code.

One other thing we should add is that if you have a really long description the entire comment might not visible on screen. But that’s OK; you can always tack on code like this to increase the height and width of your comments:

For Each objComment in objWorksheet.Comments
    objComment.Shape.Height = 125
    objComment.Shape.Width = 300
Next

Or you could try holding down three of the buttons on your radio. Hey, if something like that can change the time on a clock, who knows what else it can do.