Manipulating the Server | Sams Teach Yourself Visual Basic 2010 in 24 Hours Complete Starter Kit (Sams Teach Yourself -- Hours)

After you have an instance of an object from an automation server, manipulating the server (creating objects, setting properties, calling methods , and so forth) is accomplished by manipulating the object.

Forcing Excel to Show Itself

When Excel is started using Automation, it's loaded but not shown. By remaining hidden, it allows the developer to use its functionality and then close Excel without the user ever knowing what happened . For instance, you could create an instance of an Excel object, perform a complicated formula to obtain a result, close Excel, and return the result to the userall without the user ever seeing Excel. In this example, you want to see Excel so that you can see what your code is doing. Fortunately, showing Excel couldn't be easier. Add the following statement to make Excel visible:

 objExcel.Visible = true;

Creating an Excel Workbook and Worksheet

In Excel, a Workbook is the file in which you work and store your data; you can't manipulate data without a Workbook. When you first start Excel from the Start menu, an empty Workbook is created for you. However, when you start Excel via Automation, Excel doesn't create a Workbook; you have to do it yourself. To create a new Workbook, you use the Add method of the Workbooks collection. After the Workbook has been created, you need to set up a worksheet. Enter the following statements:

 //start a new workbook and a worksheet. Excel.Workbook objBook =                    objExcel.Workbooks.Add(System.Reflection.Missing.Value); Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1);

Notice how System.Reflection.Missing.Value is being passed into the Add() method. This is because the Add() method supports a default parameter and C# does not support default parameters. Using the System.Reflection.Missing.Value as the parameter in the Add() method enables the COM's late-binding service to use the default value for the indicated parameter value.

Working with Data in an Excel Workbook

In this section, you're going to manipulate data in the worksheet. The following describes what you'll do:

Add data to four cells in the worksheet.
Select the four cells.
Total the selected cells and place the sum into a fifth cell .
Bold all five cells.

To manipulate cells in the worksheet, you manipulate the Range object, which is an object property of the Worksheet object. Entering data into a cell involves first selecting a cell and then passing data to it. Selecting a cell is accomplished by setting a range object by calling the get_Range () method of the Worksheet object; the get_Range () method is used to select one or more cells. The get_Range () method accepts a starting column and row and an ending column and row. If you want to select only a single cell, as we do here, you can substitute the ending column and row with System.Reflection. Missing.Value parameter . After a range is set, you pass data to the selected range by using the set_Value() method on the Range object. Sound confusing? Well, it is to some extent. Programs that support Automation are often vast and complex, and programming them is usually far from intuitive.

range.set_Value(Missing.Value,"75") is used for Excel 10 (Excel XP). Use range.Value = "75" for Excel 9 (Excel 2000).

If the program you want to automate has a macro builder (as most Microsoft products do), you can save yourself a lot of time and headache by creating macros of the tasks you want to automate. The "macros" are actually code, and in the case of Microsoft products, they're VBA code.

The following section of code uses the techniques just described to add data to four cells. Enter this code into your procedure:

 Excel.Range objRange; objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value); // For EXCEL9 Use objRange.Value method in place of all of the // objRange.set_Value() statements used in this example. i.e. // objRange.Value = "75"; objRange.set_Value(System.Reflection.Missing.Value, 75 ); objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, 125 ); objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, 255 ); objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, 295 );

The next step is to have Excel total the four cells. You'll do this by using the get_Range() method to select the cell in which to place the total, and then use set_Value() method again to create the total by passing it a formula, rather than a literal value. Enter the following code into your procedure:

 objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value); objRange.set_Value(System.Reflection.Missing.Value, "=SUM(RC[-4]:RC[-1])" );

Next, you'll select all five cells and bold them. Enter the following statements to accomplish this:

 objRange = objSheet.get_Range("A1", "E1"); objRange.Font.Bold=true;

The last thing you need to do is destroy the object reference by setting the object variable to null. Excel will remain open even though you've destroyed the Automation instance (not all servers will do this). Add this last statement to your procedure:

 objExcel=null;

To help you ensure that everything is entered correctly, Listing 20.1 shows the procedure in its entirety.

Listing 20.1 Code to Automate Excel

 private void btnAutomateExcel_Click(object sender, System.EventArgs e) {    Excel.Application objExcel = new Excel.Application();    objExcel.Visible = true;    //start a new workbook and a worksheet.    Excel.Workbook objBook =       objExcel.Workbooks.Add(System.Reflection.Missing.Value);    Excel.Worksheet objSheet =                Excel.Worksheet)objBook.Worksheets.get_Item(1);    Excel.Range objRange;    objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value);    // For EXCEL9 Use objRange.Value method in place of all of the    // objRange.set_Value() statements used in this example. i.e.    // objRange.Value = "75";    objRange.set_Value(System.Reflection.Missing.Value, 75 );    objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value);    objRange.set_Value(System.Reflection.Missing.Value, 125 );    objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value);    objRange.set_Value(System.Reflection.Missing.Value, 255 );    objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value);    objRange.set_Value(System.Reflection.Missing.Value, 295 );    objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value);    objRange.set_Value(System.Reflection.Missing.Value,                "=SUM(RC[-4]:RC[-1])" );   objRange = objSheet.get_Range("A1", "E1");   objRange.Font.Bold=true;   objExcel=null; }

Testing Your Client Application

Now that your project is complete, press F5 to run it and then click the button to automate Excel. If you entered the code correctly, Excel will start, data will be placed into four cells, the total of the four cells will be placed into a fifth cell, and all cells will be made bold (see Figure 20.2).

Figure 20.2. You can control almost every aspect of Excel using its object model.

graphics/20fig02.jpg

Automating applications, particularly Office products such as Excel and Word, requires a lot of system resources. If you intend to perform a lot of automation, you should use the fastest machine with the most RAM that you can afford.

Top