named ranges

  • Thread starter Thread starter kate
  • Start date Start date
K

kate

I have been trying for days to get the following to work. I hav
created a
chart in Excel with named ranges. I have created a range for th
entire sou
rce data. When I try to reference it in Access, it reads in the rang
as th
e value and not the actual range, thus it does not work.any thoughts?
I kn
ow I can do this if I set the named ranges up for the series, but
wanted t
o do it for the entire source data instead..(for other reasons that ar
too
complex to go into!)
here is my code:

Set xlschart = CreateObject("Excel.Chart")
Set xlschart = xlswkrbk.charts("xchart")
xlsapp.Visible = False
xlschart.SetSourceData Source:=xlswkrbk.names("xmr")

if I type in the following in the immediate window of the debugger thi
is w
hat it returns for the range:
?xlswkrbk.names("xmr")
=OFFSET(XmRData3!$A$1,0,0,COUNT(XmRData3!$A:$A)+1,8


-
kat
 
Kate -

Excel converts the named range in the source data field into its
address, unlinking the source data from the dynamic name. You can use
defined names to change the size and location of each series in the
chart (and its error bars), but you cannot define a name that will
entice Excel to change the number of series in a chart. Of course, you
could use worksheet event procedures to keep updating the chart if the
source data range is changed.

- Jon
 
Back
Top