Embedded Excel Chart

Does anyone know how to change the datasource of an Embedded Excel
chart in an unbound Object Frame.

How to reference the properties??

I want to do something like this.

Dim XLChart as object

XLChart.SetSourceData Source:=Sheets("worksheets(1)").Range ("B3:H5"),
PlotBy:=xlRows

and then change it's Title

XLChart.worksheets(1).ChartObjects("Chart
1").ChartArea.Select.HasTitle = True
XLChart.worksheets(1).ChartObjects("Chart
1").ChartArea.Select.ChartTitle.Characters.Text = "My Title"

Regards

Gary

re: Embedded Excel Chart

Hi All,

Can this be done??

Controlling the properties of a chart within an unboumd object frame -
embedded excel worksheet??

Regards

Gary

re: Embedded Excel Chart

Hi All,

Can this be done??

Controlling the properties of a chart within an unboumd object frame -
embedded excel worksheet??

Regards

Gary

re: Embedded Excel Chart

ghanley wrote:[color=blue]
> Hi All,
>
> Can this be done??
>
> Controlling the properties of a chart within an unboumd object frame -
> embedded excel worksheet??
>
> Regards
>
> Gary[/color]

I've controlled normal chart properties, but not within an unbound
object frame. Can you do it manually while recording an Excel macro?

James A. Fortune

re: Embedded Excel Chart

ghanley wrote:[color=blue]
> Hi All,
>
> Can this be done??
>
> Controlling the properties of a chart within an unboumd object frame -
> embedded excel worksheet??
>
> Regards
>
> Gary[/color]

I've controlled normal chart properties, but not within an unbound
object frame. Can you do it manually while recording an Excel macro?

James A. Fortune

re: Embedded Excel Chart

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

re: Embedded Excel Chart

ghanley wrote:[color=blue]
> 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[/color]

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