HOWTO: VB Uses OLE Automation with Excel Version 5.0

MORE INFORMATION

A worksheet in Excel is sometimes called a spreadsheet. It is the primary document used in Excel to store and manipulate data. A worksheet consists of cells organized into columns and rows and is always a part of a workbook.

Step-by-Step Example

The following example shows how to use OLE automation to accomplish some common tasks on a worksheet, such as adding data, computing a sum and selecting cells.

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add the following constant declarations, taken from the CONSTANT.TXT file, into the general declarations section of Form1:

      Const OLE_CREATE_EMBED = 0
      Const OLE_ACTIVATE = 7

3. Add the MSOLE2.VBX file to the project, using the Add File option in the File Menu. MSOLE2.VBX is found in the WINDOWS\SYSTEM directory. The OLE control will appear as an option on the Visual Basic toolbar. Add an OLE control (OLE1) to Form1. Cancel the Insert Object Dialog box that pops up. You will be left with an empty OLE1 object on Form1. Set the OLE1 control's SizeMode property to Stretch.
4. Add a command button (Command1) to Form1. Set the Caption to: Embed Excel 5.0 Object. Add the following code to the Command1 Click event to embed an Excel version 5.0 worksheet into the OLE1 control. Because the SizeMode property is set to Stretch, the Worksheet automatically sizes itself in the OLE1 control when the code is executed.

         Sub Command1_Click()
            ole1.Class = "Excel.Sheet.5"
            ole1.Action = OLE_CREATE_EMBED
         End Sub
   						

5. Place another Command button (Command2) on Form1. Change the Command button's Caption to: Add Data. Add the following code to the Command2 click event:

         Sub Command2_Click ()
            ole1.Action = OLE_ACTIVATE
            ole1.Object.cells(1, 1).value = "Jan"
            ole1.Object.cells(2, 1).value = 3
            ole1.Object.cells(3, 1).value = 4
            ole1.Object.cells(4, 1).value = 6
         End Sub
   						

   The "ole1.Object" part is Visual Basic code. The rest of the line (cells(2,1).value = 1) is Excel's Visual Basic for Applications code.
6. Choose Start from the Run menu or press the F5 key to run the program. Click Command1 to see the worksheet. Click Command2 to see the information added to the worksheet. Choose End from the Run menu to return to development.
7. Add another Command button (Command3) to experiment with functions. Add the following code to the Command3 Click event code. The SUM function is one of many Excel functions that you can use in an experiment. Run the application, and press the command buttons to see the effect.

         Sub Command3_Click()
            ole1.Action = OLE_ACTIVATE
            ole1.Object.Range("A2:A4").Select
            ' Try any one of the following lines, or add some pauses between
            ' them to see the selections taking place and the active cell
            ' changing.
   
            ' To try a line, remove the single quotation mark to uncomment the
            ' line:
            ' ole1.Object.Range("C6").Activate
            ' ole1.Object.cells(6, 1).value = "=SUM(R2C:R4C)"
            ' ole1.Object.Range("A6").Select
         End Sub
   						

Find Out More

To find out more about Microsoft Excel's Visual Basic for Applications, open a new module sheet in Excel, and choose Object Browser from the View menu, or press the F2 key. The Object Browser lists all the objects in Excel and their related objects and methods. The Object Browser demonstrates the hierarchical nature of the object model.

If you want to try something new, but are unsure of the syntax, it is a good idea to start the Macro recorder in Microsoft Excel, step through the process manually, switch off the Macro recorder, and view the code in the current module. Then cut and paste the code into the Visual Basic event procedure. Usually all that is required is a prefix of ole1.object.