Hey Joel,

Thanks for your reply. I still get back the same error despite adding the
line of code you've instructed. Any other remedies?

"Joel" wrote:

Sometimes you need to add the following. Don't ask why, just try it.

Charts.Add
AvtiveChart.Activate '<= ADD
ActiveChart.ChartType = xlColumnClustered


"acccessaccess2003" wrote:

Hi all,

I keep getting this run-time error '91' message when I run this macro. It
works fine for the first time but not subsequently. I believe it has to do
with the bug on Selection. I'm trying to create a chart based on a dynamic
range of data that's imported from MS Access 2003. Therefore I need to select
the current range that's present on my ActiveSheet. Any idea how I can solve
this problem? Any help is appreciated. Thanks.

The following is part of my code:

Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim MyRecordset As ADODB.Recordset
Dim rowCount As Integer
Dim colCount As Integer
Dim I As Integer
Dim C As Integer

Set MyRecordset = New ADODB.Recordset
Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Add

xl.Visible = True

MyRecordset.Open "tblName", CurrentProject.Connection, adOpenStatic

Set xlsheet = xlwkbk.Worksheets.Add
xlsheet.Name = "Report Name"

With xlsheet
xl.Range("A2").CopyFromRecordset MyRecordset
End With

' Enumerating through to add column headings
C = 1
For I = 0 To MyRecordset.Fields.Count - 1
xl.ActiveSheet.Cells(1, C).Value = MyRecordset.Fields(I).Name
C = C + 1
Next I

rowCount = xlsheet.UsedRange.Rows.Count
colCount = xlsheet.UsedRange.Columns.Count

xl.Selection.CurrentRegion.Select
dataRange = xl.Selection.Address

ActiveWorkbook.Names.Add Name:="Week", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C1,1,0,COUNTA('Report Name'!C1)-1)"
ActiveWorkbook.Names.Add Name:="Target", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C2,1,0,COUNTA('Report Name'!C2)-1)"
ActiveWorkbook.Names.Add Name:="Week", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C3,1,0,COUNTA('Report Name'!C3)-1)"

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Report
Name").Range(dataRange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "='Report Name'!Week"
ActiveChart.SeriesCollection(2).XValues = "='Report Name'!Week"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Report Name"