PRB: Cannot Use OLE to Access Chart Object in Excel 4.0

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

SYMPTOMS

Excel exposes a variety of objects which can be accessed from Visual Basic by using Object Linking and Embedding (OLE) or the OLE control. The Excel Chart is one of these objects. However, if the chart is embedded in an Excel version 4.0 spreadsheet, there is no direct way to activate the Chart using OLE automation. This article gives you two possible solutions.

When you use the OLE 2.0 control to embed an Excel version 4.0 chart, the SourceDoc and SourceItem should contain the fully qualified path, filename, and chart name. The documentation suggests that Visual Basic should be able to access the Chart directly, but for Excel version 4.0 this does not work.

CAUSE

This is a problem with Excel version 4.0 and OLE. Excel version 4.0 does not correctly write the path to the embedded object as the OLE layer expects.

RESOLUTION

Two Possible Solutions

You can copy and paste the chart object into a separate chart sheet and perform the OLE automation with that. To do this, double-click the chart object and choose Edit Copy. Then choose File New, and select Chart. Then choose Edit Paste to paste the chart into the blank chart sheet. This chart will still be linked to the worksheet data, so it will automatically update when the worksheet data is changed.

The second option is to upgrade to Excel version 5.0, which is capable of linking to a Chart Object within an Excel Sheet (or Book). In this case, you would set the SourceDoc property to:

   C:\EXCEL\BOOK1.XLS!Sheet1![BOOK1.XLS]Sheet1 Chart 1