One issue I've run into now, after implementing this solution, is that
Excel
does not quit after the function is complete - even after:
Set xlapp = Nothing
xlapp.Quit
Looking at the first example, before releasing xlApp first release the
others in reverse order:
Set objChart = nothing
Set objSheet = nothing
xlapp.Quit
Set xlapp = Nothing
Not sure about the second example.
Regards,
Peter T
"deko" wrote in message
...
mystery solved!
Dim objSheet As Object
Dim objChart As Object
For p = LBound(sn) To UBound(sn) 'array of sheet names
Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p))
Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(p)).Range("C1").End(xlD own).Row), _
PlotBy:=xlColumns
objChart.ChartType = xlLineMarkersStacked
objChart.Axes(xlCategory, xlPrimary).HasTitle = False
objChart.Axes(xlValue, xlPrimary).HasTitle = False
objChart.HasLegend = False
objChart.ChartTitle.Text = sn(p)
objChart.HasTitle = True
Next p
The ChartObjects collection holds embedded Chart objects, which are
accessed
through the ChartObject object; the Chart property of the ChartObject
object
is used to access the actual chart. When using automation, the Add
method
of the ChartObjects object will add an embedded chart, rather than the
Add
method of the Charts object. There's an example he
http://msdn.microsoft.com/library/de...us/odc_xl2003_
ta/html/odc_XL_manipulatecharts.asp
but it's not very well documented.
One issue I've run into now, after implementing this solution, is that
Excel
does not quit after the function is complete - even after:
Set xlapp = Nothing
xlapp.Quit
Thinking that the problem was the old "global reference to the automated
application" issue (helpfully described by Mr. Mehta at
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/)
I tried this:
For p = LBound(sn) To UBound(sn)
With
xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Char tObjects.Add
_
(Left:=300, Top:=20, Width:=500, Height:=300).Chart
.ChartType = xlLineMarkersStacked
.SetSourceData Source:=xlapp.Workbooks(strXlsFile).Worksheets
_
(sn(p)).Range("A1:C" &
xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Rang e _
("C1").End(xlDown).Row), PlotBy:=xlColumns
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.HasLegend = False
.ChartTitle.Text = sn(p)
.ChartTitle.Font.Bold = True
.HasTitle = True
End With
Next p
But the problem remains. Apparently Access needs a diaper on this one...