Using Excel Objects to Manipulate a Spreadsheet

In the scripts for the articles "Generating Deployment Reports" (Windows 2000 Magazine, March 2000) and "Automating Excel to Create Trend Charts" (Win32 Scripting Journal, March 2000), I used an incremental counter to select and update cells in a Microsoft Excel 2000 spreadsheet. In ADTester.wsh, I use Excel object methods and properties to select and update cells. If you're unfamiliar with how to use Excel objects to add data to cells and navigate within a spreadsheet, here's a brief overview.

To specify that you want to work with the currently highlighted cell in a spreadsheet, you use the Excel.Application object's ActiveCell method. To set the value of the current cell, you use ActiveCell's FormulaR1C1 property. For example, to set the current cell's contents to the string Danny Cooper, you specify

appXL.ActiveCell.FormulaR1C1 = "Danny Cooper"

You can navigate throughout a spreadsheet in several ways. You can use ActiveCell's Next method on the current cell to move to the cell one column to the right. You then use the Select method to select this cell. For example, the following code moves to and selects the cell to the right of the current cell:

appXL.ActiveCell.Next.Select

You can also use ActiveCell's Row and Column methods with the Range object to navigate throughout a spreadsheet. The Range object selects cells in a spreadsheet. The Row and Column methods specify ActiveCell’s current row and column, respectively. So, for example, to move to and select column B in the current row, you use the code

appXL.Range("B" & appXL.ActiveCell.Row).Select

To move to and select column A in the next row, you specify

appXL.Range("A" & (appXL.ActiveCell.Row + 1)).Select

If you want to learn more about the Excel object model, you can go to the Microsoft Developer Network (MSDN) Web site at http://msdn.microsoft.com.