Dynamic Range

I'm trying to automate a table from Access to Excel and generate a line chart
in Excel.

I have 3 attributes; Week, Target and Actual.

Initially 'Week' was a Number type and the automation worked perfectly fine.
But recently, I changed 'Week' to Text type and errors occur.

First, VBA keeps showing Run-time error '1004': Method 'SeriesCollection' of
object '_Chart' failed. When I press Debug, the line that generated the error
is:
..ActiveChart.SeriesCollection(2).XValues = "='Project Summary Report'!Week"
and I don't understand why. Can anyone help?

Secondly, the dynamic range that I've defined keeps plotting only 'Actual'
against 'Week'. What I want to achieve is plotting 'Actual' and 'Target'
against 'Week' to see comparison between 'Target' and 'Actual'.
So I ended up having to right-click to see the Source Data and found out
that the data range excluded the 'Target' column. i.e.
='Project Summary Report'!$A$1:$A$14,'Project Summary Report'!$C$1:$C$14
so I changed the comma in the middle, to a colon, the graph can then be
correctly plotted. Can anyone tell me why?

Thirdly, I want to place the chart on the same worksheet. Like I've
mentioned, when 'Week' was a Number type, the chart could be correctly
generated on the same worksheet. But now, the chart is on a different
worksheet from the table where its source data comes from. Can anyone help to
solve this problem?

Million thanks for anyone who can help.

Some comments in-line

> I'm trying to automate a table from Access to Excel and generate a line
chart
> in Excel.
>
> I have 3 attributes; Week, Target and Actual.
>
> Initially 'Week' was a Number type and the automation worked perfectly
fine.
> But recently, I changed 'Week' to Text type and errors occur.
>
> First, VBA keeps showing Run-time error '1004': Method 'SeriesCollection'
of
> object '_Chart' failed. When I press Debug, the line that generated the
error
> is:
> .ActiveChart.SeriesCollection(2).XValues = "='Project Summary
Report'!Week"
> and I don't understand why. Can anyone help?

The named range "Week" should refer to a single row or column of cells

Is "Week" a 'Worksheet' level name of the sheet named "'Project Summary
Report'. If it's a workbook level name, as is typical, change to =
"='workbook name.xls'Week"

>
> Secondly, the dynamic range that I've defined keeps plotting only 'Actual'
> against 'Week'. What I want to achieve is plotting 'Actual' and 'Target'
> against 'Week' to see comparison between 'Target' and 'Actual'.
> So I ended up having to right-click to see the Source Data and found out
> that the data range excluded the 'Target' column. i.e.
> ='Project Summary Report'!$A$1:$A$14,'Project Summary Report'!$C$1:$C$14
> so I changed the comma in the middle, to a colon, the graph can then be
> correctly plotted. Can anyone tell me why?

I take it that formula appears in the against DataRange in the Source data
dialog. With the comma it refers to two sets of data in columns A and C. If
you change the comma to a colon it will refer to a single block "A1:C14" and
three columns of data, probably the formula will be shortened to reflect
that

> Thirdly, I want to place the chart on the same worksheet. Like I've
> mentioned, when 'Week' was a Number type, the chart could be correctly
> generated on the same worksheet. But now, the chart is on a different
> worksheet from the table where its source data comes from. Can anyone help
to
> solve this problem?

Ideally you should have put it where you wanted in the first place, but you
can move it. Record a macro, rt-click location, it gave me
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"

(you will need the dot to link to your reference to Excel, bettter to use an
object refernce to your chart as there's rarely any need to use Active etc)

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:%23eas7hc3IHA.1428@TK2MSFTNGP06.phx.gbl...
> Some comments in-line
>
>> I'm trying to automate a table from Access to Excel and generate a line
> chart
>> in Excel.
>>
>> I have 3 attributes; Week, Target and Actual.
>>
>> Initially 'Week' was a Number type and the automation worked perfectly
> fine.
>> But recently, I changed 'Week' to Text type and errors occur.

Is it a line chart or an XY chart? Is there actually data in the X and Y
data ranges, both before and after running this line of code? If a line or
xy series has no plotted points, VBA cannot access many of its properties.
You need to create the chart with an appropriate data range to begin with,
add the data as you add the series, or convert temporarily to a column or
area type while VBA works its magic.

>> First, VBA keeps showing Run-time error '1004': Method 'SeriesCollection'
> of
>> object '_Chart' failed. When I press Debug, the line that generated the
> error
>> is:
>> .ActiveChart.SeriesCollection(2).XValues = "='Project Summary
> Report'!Week"
>> and I don't understand why. Can anyone help?
>
> The named range "Week" should refer to a single row or column of cells

Not necessarily.
Chart with a Dual Category Axis
http://peltiertech.com/WordPress/20...-category-axis/

> Is "Week" a 'Worksheet' level name of the sheet named "'Project Summary
> Report'. If it's a workbook level name, as is typical, change to =
> "='workbook name.xls'Week"

The syntax works fine (just tested) for both worksheet- and workbook-scoped
names.

Is the dot in front of ActiveChart significant? Unless it's inside a "With
xlApp" block, this could give rise to an error.

>> Secondly, the dynamic range that I've defined keeps plotting only
>> 'Actual'
>> against 'Week'. What I want to achieve is plotting 'Actual' and 'Target'
>> against 'Week' to see comparison between 'Target' and 'Actual'.
>> So I ended up having to right-click to see the Source Data and found out
>> that the data range excluded the 'Target' column. i.e.
>> ='Project Summary Report'!$A$1:$A$14,'Project Summary Report'!$C$1:$C$14
>> so I changed the comma in the middle, to a colon, the graph can then be
>> correctly plotted. Can anyone tell me why?
>
> I take it that formula appears in the against DataRange in the Source data
> dialog. With the comma it refers to two sets of data in columns A and C.
> If
> you change the comma to a colon it will refer to a single block "A1:C14"
> and
> three columns of data, probably the formula will be shortened to reflect
> that

You need to set the series x and y values for both series, or use
SetSourceData using an appropriate range.

>> Thirdly, I want to place the chart on the same worksheet. Like I've
>> mentioned, when 'Week' was a Number type, the chart could be correctly
>> generated on the same worksheet. But now, the chart is on a different
>> worksheet from the table where its source data comes from. Can anyone
>> help
> to
>> solve this problem?
>
> Ideally you should have put it where you wanted in the first place, but
> you
> can move it. Record a macro, rt-click location, it gave me
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
>
> (you will need the dot to link to your reference to Excel, bettter to use
> an
> object refernce to your chart as there's rarely any need to use Active
> etc)

Fix your recorded macro:
http://peltiertech.com/WordPress/20...recorded-macro/
http://peltiertech.com/Excel/Charts...ckChartVBA.html

Create a chart object directly, without the intermediate chart sheet step,
and as Peter points out, use object variables wherever possible.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Thanks for clarifying and/or correcting.

Regards,
Peter T

"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:uyPNagf3IHA.4988@TK2MSFTNGP04.phx.gbl...
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23eas7hc3IHA.1428@TK2MSFTNGP06.phx.gbl...
> > Some comments in-line
> >
> >> I'm trying to automate a table from Access to Excel and generate a line
> > chart
> >> in Excel.
> >>
> >> I have 3 attributes; Week, Target and Actual.
> >>
> >> Initially 'Week' was a Number type and the automation worked perfectly
> > fine.
> >> But recently, I changed 'Week' to Text type and errors occur.
>
> Is it a line chart or an XY chart? Is there actually data in the X and Y
> data ranges, both before and after running this line of code? If a line or
> xy series has no plotted points, VBA cannot access many of its properties.
> You need to create the chart with an appropriate data range to begin with,
> add the data as you add the series, or convert temporarily to a column or
> area type while VBA works its magic.
>
> >> First, VBA keeps showing Run-time error '1004': Method
'SeriesCollection'
> > of
> >> object '_Chart' failed. When I press Debug, the line that generated the
> > error
> >> is:
> >> .ActiveChart.SeriesCollection(2).XValues = "='Project Summary
> > Report'!Week"
> >> and I don't understand why. Can anyone help?
> >
> > The named range "Week" should refer to a single row or column of cells
>
> Not necessarily.
> Chart with a Dual Category Axis
>
http://peltiertech.com/WordPress/20...-category-axis/
>
> > Is "Week" a 'Worksheet' level name of the sheet named "'Project Summary
> > Report'. If it's a workbook level name, as is typical, change to =
> > "='workbook name.xls'Week"
>
> The syntax works fine (just tested) for both worksheet- and
workbook-scoped
> names.
>
> Is the dot in front of ActiveChart significant? Unless it's inside a "With
> xlApp" block, this could give rise to an error.
>
> >> Secondly, the dynamic range that I've defined keeps plotting only
> >> 'Actual'
> >> against 'Week'. What I want to achieve is plotting 'Actual' and
'Target'
> >> against 'Week' to see comparison between 'Target' and 'Actual'.
> >> So I ended up having to right-click to see the Source Data and found
out
> >> that the data range excluded the 'Target' column. i.e.
> >> ='Project Summary Report'!$A$1:$A$14,'Project Summary
Report'!$C$1:$C$14
> >> so I changed the comma in the middle, to a colon, the graph can then be
> >> correctly plotted. Can anyone tell me why?
> >
> > I take it that formula appears in the against DataRange in the Source
data
> > dialog. With the comma it refers to two sets of data in columns A and C.
> > If
> > you change the comma to a colon it will refer to a single block "A1:C14"
> > and
> > three columns of data, probably the formula will be shortened to reflect
> > that
>
> You need to set the series x and y values for both series, or use
> SetSourceData using an appropriate range.
>
> >> Thirdly, I want to place the chart on the same worksheet. Like I've
> >> mentioned, when 'Week' was a Number type, the chart could be correctly
> >> generated on the same worksheet. But now, the chart is on a different
> >> worksheet from the table where its source data comes from. Can anyone
> >> help
> > to
> >> solve this problem?
> >
> > Ideally you should have put it where you wanted in the first place, but
> > you
> > can move it. Record a macro, rt-click location, it gave me
> > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
> >
> > (you will need the dot to link to your reference to Excel, bettter to
use
> > an
> > object refernce to your chart as there's rarely any need to use Active
> > etc)
>
> Fix your recorded macro:
> http://peltiertech.com/WordPress/20...recorded-macro/
> http://peltiertech.com/Excel/Charts...ckChartVBA.html
>
> Create a chart object directly, without the intermediate chart sheet step,
> and as Peter points out, use object variables wherever possible.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
>