Dynamic Range

[acccessaccess2003]

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.

 

[Peter T]

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

 

[Jon Peltier]

Some comments in-line

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.

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/2008/04/22/chart-with-a-dual-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.

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.

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/2008/03/11/how-to-fix-a-recorded-macro/
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

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

- Jon

 

[Peter T]

Thanks for clarifying and/or correcting.

Regards,
Peter T