I'm trying to put together a function to create a pareto chart in Excel from a module in Access VBA.
Dim serValues As Object
Dim chtPareto As Object
Set chtPareto = appExcel.Charts.Add
.Name = "Pareto Chart"
.ChartType = 51 ' xlColumnClustered
Set serValues = .SeriesCollection.NewSeries
.Name = piv.DataLabelRange.Text
.Values = objPivotSheet.Range(rngValues.Address)
.XValues = objPivotSheet.Range(rngLabels.Address)
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?