Introduction to Scripting Microsoft Office: Excel and Word

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 Archives to see past articles.

Download all the Sesame Script columns (from the very first column through the June, 2007 edition) in one easy-to-read, fully-searchable .chm file.

On This Page

The Corner Office
Scripting Microsoft Excel
Scripting Microsoft Word
More Information
Vacant Office

The Corner Office

The corner office is typically equated with status and respect. In case you’re wondering where the Scripting Guys stand here at Microsoft, well, let’ put it this way: Scripting Guy Greg Stemp recently walked into his office and found miscellaneous pieces of old equipment piled in it because someone had mistaken his office for a small storage closet.

While you might not work in a storage closet like Greg, if you’re a typical system administrator you probably don’t have the premium office space in your building, either. Does that mean you’re less valuable to your organization than the CEO with the giant office? Well, yes. But that’s beside the point. The point is that you have an office, or at least a cubicle…or a desk…or part of a desk….

Speaking of offices, when was the last time you took a close look at the things you can do with Microsoft Office? Sure, you can plug some equations into Excel, write up reports in Word, send your email from Outlook. But as a system administrator who knows a little something about writing scripts, if you’re not taking advantage of the scripting capabilities of Office, well, it’s no wonder you’re sitting in a closet instead of the corner office.

Oh, wait: Greg uses scripts that take advantage of Microsoft Office and he’s in a storage closet. All right, forget that theory. But the point is - yes, we’re still attempting to make a point - you really should check out the scripting capabilities of the various Office products. They’re pretty extensive and, as a system administrator, they can really help you. With that in mind, we’ll spend the rest of this article showing you the basics of scripting Excel and Word. Why just Excel and Word? For a couple of reasons: Excel and Word are the most frequently used applications by system administrators, and the scripting concepts are similar. Other Office products, such as Access and Outlook, have pretty robust scripting capabilities, but we’ll save them for another day.

Scripting Microsoft Excel

We’ll start with Excel because it’s probably the product you’ll use most. You can use Excel for tasks such as putting together reports detailing server capacity, tracking user information, and adding information from a spreadsheet to Active Directory, just to name a few. The possibilities seem limitless. Just like the number of boxes that will fit in Greg’s office.

Excel is actually made up of several parts. For example, there’s Excel itself, which is sort of the shell that holds the whole thing together:

Microsoft Excel

Within that shell there are workbooks (which is what actually gets saved as an .xls or .xlsx file), and within a workbook are separate worksheets:

Microsoft Excel with Workbook

In order to work with Excel using a script, you have to work with each of these parts. Here’s a simple script that opens Excel without any workbooks:

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

Did you try it? If so, it didn’t seem to do much, did it? If you were paying close attention you probably saw something flash briefly on the screen then disappear again. That was Excel. In this script we created a running instance of Excel by calling the CreateObject method, passing it the name of the object we want to create. In this case, because we’re creating an instance of Excel, we pass in the Excel.Application object. We’d like to see our instance of Excel, so we set the Visible property to True to make it visible on screen. At that point the script ended, and so did Excel.

Not too useful so far, is it? But neither is an empty instance of Excel. That’s why, in order to keep Excel open, we need to either open a workbook or create a new one. Let’s start by opening an existing workbook, C:\Scripts\Test.xls:

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

objExcel.Workbooks.Open("C:\Scripts\Test.xls")

If we want to open a workbook, it makes sense that we need to access the next part of the Excel object model, the workbook. We do that through the Workbooks collection of the Excel object. We then call the Open method on the workbook, passing it the name and path to the Excel file we want to open.

And there you have it, an open Excel workbook:

Microsoft Excel with Open Workbook

Now let’s create a new workbook:

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

objExcel.Workbooks.Add

Here we again use the Workbooks collection, this time calling the Add method. We don’t need to pass any parameters; we’re simply adding an empty workbook. (If we wanted to we could pass in a template name so we could open the workbook with a specific Excel template.) We don’t need to explicitly add any worksheets because Excel does that automatically:

Microsoft Excel with New Workbook

Now suppose we want to add some information to the workbook. The great thing about the Open and Add methods is that they return object references to the workbook. This gives us access to the workbooks, which, in turn, give us access to the worksheets within the workbook. For example, let’s say we want to create a new workbook and add a value to the first cell in the first worksheet of that workbook. Here’s how we do that:

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

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

objWorksheet.Cells(1, 1) = "100"

After creating the Excel object and making Excel visible on screen, we add our new workbook. Notice that, this time, we stored the object reference to the workbook in the objWorkbook variable. We then use the Worksheets property of that workbook to get a reference to the first worksheet in the workbook. We know we’re accessing the first worksheet because we passed the value 1 to the Worksheets property. If we had passed a 2 we would have a reference to the second worksheet. We also could have passed the name of the worksheet, like this:

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Now that we have access to the worksheet we can use the Cells property to assign a value to a specific cell:

objWorksheet.Cells(1, 1) = "100"

Here we’ve assigned the value 100 to the cell in row 1, column 1 (which is what the 1,1 is for). And now that we’ve assigned a new value to the worksheet it’s time for us to save the workbook:

objWorkbook.SaveAs("C:\Scripts\Test.xls")

All we did here was call the workbook’s SaveAs method, passing it the filename we want to save it to. Add this line to the end of your script to save the file to - in this case - C:\Scripts\Test.xls.

There’s one important thing to note when you’re working with Excel. Let’s take a look at what would happen if we had left out this line:

objExcel.Visible = True

By default the Visible property is False. This can be useful: there might be times when you want to run a script without having to show anything on screen. However, if you do that you need to be sure that you call the Quit method to close Excel:

objExcel.Quit

If you don’t, your script will finish but Excel will continue to run in the background. If you run the script several times, you’ll have an instance of Excel running for each time you run the script.

Bonus: If you make changes to an Excel workbook and call the Quit method, you’ll be prompted to save the workbook before closing. If you don’t want any prompts to be displayed when you call Quit, simply set the DisplayAlerts property to False, like this:

objExcel.DisplayAlerts = False

That wasn’t too bad, was it? Now that you know the basics of Excel, the rest of the Office products are going to seem pretty simple. Read on….

Scripting Microsoft Word

Word works a lot like Excel in terms of the fact that it has multiple parts. There’s Word itself, then there’s the document within Word. Within the document there are headers and footers and various other elements, but we won’t be getting into any of that today. Let’s start by opening Word:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Just as we did with Excel, we call the CreateObject method, this time creating a Word.Application object. We then set the Visible property to True so we can see Word on screen. If you run this script, you’ll notice an obvious difference between creating an Excel application and creating a Word application: Excel flashes on the screen and immediately closes, but Word sticks around. Run this script and you’ll have an open instance of Word on your screen. This seems a little nicer than the Excel version, simply because you at least know for sure that you’ve done something.

Now let’s open an existing document:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

objWord.Documents.Open("C:\Scripts\Test.doc")

We open a Word document by - logically enough - calling the Open method on the Documents collection. We simply pass the path and filename of the document we want to open to the Open method and we’re done. Would you rather create a new document? That’s easy enough:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

objWord.Documents.Add

Calling the Add method adds a blank document to your instance of Word. There are several parameters you can pass to the Add method if you want to:

  • Template - the name of the template you want the document to use.

  • NewTemplate - set this parameter to True if you’re using this document to create a template. The default is False.

  • DocumentType - a constant specifying the type of document you want to create.

  • Visible - set this parameter to False to make the document hidden. Note that this is different from the Visible property we set earlier. The first Visible property we showed you determines whether or not Word appears on screen, and the default is False. (That is, don’t make Word visible on screen.) The default for the Visible parameter of the Add method is True, meaning that the document you create will be visible inside Word. But try this:

    objWord.Documents.Add ,,,False
    

    Here we’ve set the Visible parameter to False. If you run the script with this line in it, you’ll see an open instance of Word but you won’t see any open documents, even though there is an open document.

Now let’s add some text to our Word document:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Add

Set objSelection = objWord.Selection

objSelection.TypeText "Here is some text in a Word document."

We once again create an instance of Word and make it visible. Next we add a document, this time assigning the object reference for that document to the variable objDoc. Now we can start adding text to our document. The first thing we do is create a Selection object:

Set objSelection = objWord.Selection

When you open Word and you look at a document, you know where the text will appear when you begin typing; that’s because you can see the selection cursor blinking on the screen. The Selection object is basically your blinking cursor - it gives you a reference to the position in the document where the text will be entered. By default this is always the beginning of the document. What that means is that we can use the objSelection object to start typing some text at the beginning of our document:

objSelection.TypeText "Here is some text in a Word document."

We do the actual “typing” by calling the TypeText method and passing it the text we want in the document.

If you want to save the document, simply tack this line onto the end of your script:

objDoc.SaveAs "C:\Scripts\Test.doc"

See, we told you the reference to the Document object (objDoc) would come in handy. We’ve used the SaveAs method of the Document object to save the document, passing as a parameter the filename we want to save the document to.

More Information

There is a lot of information on the Script Center about scripting Office products. In particular, take a look at these sections:

Vacant Office

We’re hoping someday to give you the beginner’s guides to scripting Access and Outlook, but we’ll see. We had planned on cleaning the junk out of Greg’s office first, but because all there is in his office is junk that would make it look like a vacant office. And that could be a real problem: if anyone sees an office that looks vacant they might try to move someone else in. At least being in a storage closet seems to keep everyone away.