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 Whe=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