Need help with controlling an Excel.Chart.8 object in an Access form ...

[brooks]

I have an unbound OLE object on a form:

Name: OLEExcelChart
OLE Class: Microsoft Excel 2000
Class: Excel.Chart.8

I need to manipulate properties of the chart as well as the underlying
data.

I tried changing the chart type with:

Dim xlChart As Object

Set xlChart = Me.OLEExcelChart

xlChart.PlotArea.Select
xlChart.ChartType = xl3DPie

and get runtime error 438 "Object doesn't support this property or
method".

Would appreciate any help or code snippets.

Thanks,

Brooks

 

[David Lloyd]

Brooks:

In order to get a reference to the underlying Chart object, you need to
drill down the OLE Object hierarchy. For example:

Dim xlChart As Object

Set xlChart = Me.OLEExcelChart.Object.Charts(1)

The Object reference in this case returns a Workbook object. You must then
reference the Charts collection of the workbook to gain a reference to the
actual chart.

The chart type can be changed without selecting the plot area. However, if
you need to select the plot area then you will need to activate the OLE
Object first. For example:

Me.OLEExcelChart.Action = acOLEActivate
xlChart.PlotArea.Select

To gain a reference to the data sheet, you can use the Sheets collection (or
Worksheets collection) of the Workbook object. For example,

Set xlSheet = Me.OLEExcelChart.Object.Sheets(2)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have an unbound OLE object on a form:

Name: OLEExcelChart
OLE Class: Microsoft Excel 2000
Class: Excel.Chart.8

I need to manipulate properties of the chart as well as the underlying
data.

I tried changing the chart type with:

Dim xlChart As Object

Set xlChart = Me.OLEExcelChart

xlChart.PlotArea.Select
xlChart.ChartType = xl3DPie

and get runtime error 438 "Object doesn't support this property or
method".

Would appreciate any help or code snippets.

Thanks,

Brooks