Set ChartSpace variable to the current PivotChart form

[grgimpy]

I'm trying to write code that will conditionally format individual data points in a Pivotchart form. I am getting the error "Error 13: Type Mismatch" for the following line of code:

Set objChartSpace = Me.Form.ChartSpace

I used the following support page as a guide (

http://support.microsoft.com/kb/285848),

but am confused because in the example they use the code:

Set objChartSpace = Me.PivotChart.Form.ChartSpace

instead. But when I try and type this into VBA, "PivotChart" is not even an option (I believe I have referenced everything correctly).

After that I'm not even sure if my coding will work b/c I can't get past setting objChartSpace to the current pivotchart forms' Chartspace.

Any ideas on what I'm doing wrong? Also, I realize conditional formatting can be done using Access, but my formatting is more complicated than Access allows you to do.

Thanks

Dim objChartSpace As OWC11.ChartSpace
Dim objChart As OWC11.ChChart
Dim objSeries As OWC11.ChSeries
    
    Set objChartSpace = Me.Form.ChartSpace
    
    Set objChart = objChartSpace.Charts(0)
    
    For Each objSeries In objChart.SeriesCollection
        If Me.ESA1_EtchantActualpH.Value > 8.4 Then _
            objSeries.Interior.Color = vbRed
    
    Next

 

[PHV]

Remove all references to OWC11 (even in the menu Tools -> References ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[grgimpy]

If I remove the references then I cannot even declare my variables:

Dim objChartSpace As ChartSpace

No longer works because of a compile error.

 

[PHV]

Which version of access ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry, disregard my answers :~/

 

[grgimpy]

Whenever I type the code:

Me.ChartSpace

I have no more options after ChartSpace. I've also tried doing other things with this command like export pivotcharts as pictures with

Me.ChartSpace.ExportPicture ....

but cannot even when I have referenced the OWC. Is there another setting I have to change in my Access to make the Me.ChartSpace command work properly?

 

[sugarflux]

Have a look here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;231009

 

[grgimpy]

Well, I finally figured out how to make all this OWC stuff work with PivotCharts. There definitely isn't much going on for information about this subject except a couple articles in Microsoft Knowledge Base.

Here are a few articles if anyone is interested:

http://support.microsoft.com/?kbid=304143

http://support.microsoft.com/kb/235885/EN-US/

http://support.microsoft.com/kb/285848

The one thing I have learned is that in order for the OWC reference to work in Microsoft Access 2003, you must use version 10. Version 11 simply does not work. I heard stuff about updates to Office security and such, but in order to make this as painless as possible, just use the OWC10 reference. By switching to this instead of version 11, I was able to solve many of the problems I was having.

Maybe when I get time, I'll write some FAQ's about manipulating Access PivotCharts. It really is a powerful tool and can really impress clients and employers.

For now here's a code example on how to export a PivotChart. I got this workaround from the following site:

http://www.pcreview.co.uk/forums/thread-2485348-1.php

Dim frm As Access.Form

Set frm = Me.frmPivotChart.Form


Dim oTemp As OWC10.ChartSpace
Set oTemp = CreateObject("OWC10.ChartSpace")


oTemp.XMLData = frm.ChartSpace.XMLData
oTemp.DataSource = frm.PivotTable

oTemp.ExportPicture "C:\PivotChart1.jpg", "JPEG", Width:=1024,
Height:=700

Set oTemp = Nothing