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.