Create Excel chart from Access VBA (AC2007)

[AOB]

Hi guys,

I'm trying to put together a function to create a pareto chart in Excel from a module in Access VBA.

[COLOR=black]Dim serValues As Object[/COLOR]
Dim chtPareto As Object
 
[COLOR=blue]Set chtPareto = appExcel.Charts.Add[/COLOR]
 
With chtPareto
[INDENT].Name = "Pareto Chart"
.Move After:=objWorkbook.Sheets(objWorkbook.Sheets.Count)
.ChartType = 51      ' xlColumnClustered
 
[COLOR=red]Set serValues = .SeriesCollection.NewSeries[/COLOR]
 
With serValues
 
[INDENT].Name = piv.DataLabelRange.Text
.Values = objPivotSheet.Range(rngValues.Address)
.XValues = objPivotSheet.Range(rngLabels.Address)
 
[/INDENT]End With
 
[/INDENT]End With

appExcel, objWorkbook, piv, rngValues & rngLabels are late-bound objects referring to the Excel Application, target workbook, pivot table and appropriate ranges respectively (these are all working fine)

The problem I'm having is, when the code hits the line in blue (to create the new chart object), I don't get a blank chart? Weirdly, the chart is already populated with values from my pivot sheet (even though this is supposed to be a blank chart, the series of which I am about to define programmatically?)

So when it gets to the line in red, I get a 1004 error :

Application-defined or object-defined error

How do I get VBA to create a new - blank - chart which I can then assign series to?? Is it possible to create Excel charts from Access using late-binding or does one have to include a reference to the Excel object library?

Thanks

AOB

 

[KenHigg]

I ran across an issue once where if you do not kill an object it hangs around and caused wacky things to happen, I wonder if your issue could be the same. Try rebooting the pc to delete all objects and run it - ?

 

[AOB]

Thanks Ken

I think the problem is actually that the data driving the charts is in a pivot table. When a cell within the pivot is selected, Excel - in its infinite wisdom - predetermines that a pivot chart is what is required and you can't edit the data source of a pivot chart.

So what I've done is, once the pivots are created, I copy/paste the table range over itself (values only) so i'm just left with the raw data from the table (and none of the associated functionality) Working now.

I wish Access had a decent charting tool so I wouldn't have to resort to passing stuff over to Excel and back...

 

[KenHigg]

Actually - I am pretty sure it's the same 'Chart Engine', manipulating it just seems harder in Access...

FYI - I hate pivots because the cols can be dyanamic. Maybe if you recreate without the pivot - ?

 

[AOB]

It definitely doesn't have the breadth of functionality as Excel does. Which is frustrating as I believe you're right, it is fundamentally the same engine.

I also hate pivots but they are useful for grouping and summarising data (in Excel), hence I create the pivot to get my summary and then copy / paste(special) to remove the cache and leave me with unformatted / unpivotted data.