HOWTO: VB Uses OLE Automation with Excel Version 5.0

• Microsoft Visual Basic Professional and Enterprise Editions, 16-bit only, for Windows, version 4.0
• Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0
• Microsoft Excel for Windows, 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

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

Additional query words: W_VBApp

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