ghanley wrote:
Yes - I have manage to run a Macro recording - and I end up with this
ActiveChart.SetSourceData Source:=Sheets("Query3").Range("B3:D8"),
PlotBy:= xlColumns
My problem now is converting this to VB that Access understands because
I need it to be dynamic as the recordsource changes from form criteria.
The only reason for me trying to manipulate the datasource - is because
the legend needs to be updated when the record count changes.
If there are fewer data series being plotted - the legend shows empty
entries.
Hope this makes sence??
GAry
Here's how I change the labels on 12 charts from Access that each take
up a sheet in Excel:
'Chart Labels
For lngI = 1 To 12
objXL.Sheets(ChartSheetName(lngI)).Select
objXL.ActiveChart.ChartTitle.Select
objXL.Selection.Characters.Text = strNewTitle(lngI)
objXL.Selection.AutoScaleFont = False
With objXL.Selection.Characters(Start:=1,
Length:=Len(strNewTitle(lngI))).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 11
End With
Next lngI
Obviously my objXL.Sheets(ChartSheetName(lngI)).Select won't work in
your case. Maybe recording a macro that just selects the chart will
show you what to use in place of this line. Once you can reference
ActiveChart, SetSourceData may become available for it also. Also, I
like starting from objXL for references (including Sheets) so that I
don't have any problems closing the spreadsheet. Plus, make sure you
have a reference to the Excel Object Library. Except for referencing
objXL, the syntax should be identical.
James A. Fortune
