HOWTO: VB Uses OLE Automation with Excel Version 5.0

ID: Q112443

The information in this article applies to:

Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit only, for Windows, version 4.0
Standard and Professional Editions of Microsoft Visual Basic for Windows, version 3.0
Microsoft Excel, version 5.0

SUMMARY

This article demonstrates how to embed a Microsoft Excel version 5.0 Worksheet object in a Visual Basic application, and then manipulate it by using OLE Automation and an MSOLE2.VBX control or an OLE Container Control. Microsoft Excel version 5.0 offers OLE objects that support Worksheet and Chart functionality using Visual Basic for Applications.

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.

Additional query words: W_VBApp

Keywords          : kbprg kb16bitonly kbVBp400 IAPOLE VB4WIN vbwin 
Version           : WINDOWS:3.0 4.0
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: October 1, 1997