Hey Joel! There's finally some progress but there's still a slight error
generating from this line.
..Shapes("Chart 1").IncrementTop -3.75

The following run-time error -2147024809 (80070057) The Item with the
specified name wasn't found. Any remedies?

"Joel" wrote:

Try putting a period infront to ActiveChart so you are refereing to xlwkbk.
I think excel is still thinking the activechart is in book1 which you closed.
the 2nd time you run the code the book is book2. excel is looking at book1
and cannot find the closed book.

from
ActiveChart
to
.ActiveChart


"acccessaccess2003" wrote:

I kinda get your point of using xlwkbk. I've placed all xlwkbk related codes
into that block. It works well for the first execution but not for the
subsequent ones. As you may have noticed, I'm trying to plot Week on the
x-axis, and Target & Actual on the Y axis. It works fine for the first time
but when I close Book1, and try to execute the same set of codes again, it
instead generates the chart and plot Target against Actual. It generated the
following run-time error '1004', Method 'ActiveCharts' of object '_Global'
failed. The error came from the following line:

With ActiveChart

Here's part of my code:

With xlwkbk
.Names.Add Name:="Week", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C1,1,0,COUNTA('Report Name'!C1)-1)"
.Names.Add Name:="Target", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C2,1,0,COUNTA('Report Name'!C2)-1)"
.Names.Add Name:="Week", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C3,1,0,COUNTA('Report Name'!C3)-1)"
.Charts.Add
.ActiveChart.ChartType = xlColumnClustered
.ActiveChart.SetSourceData Source:=xl.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"

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Report Name"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Number of Tools Completed"
.HasDataTable = True
.DataTable.ShowLegendKey = True
.Shapes("Chart 1").IncrementTop -3.75
.ChartTitle.Select
Selection.Top = 1
.ChartArea.Select
.ChartTitle.Select
End With

.ActiveSheet.Shapes("Chart 1").IncrementLeft 9#
.ActiveSheet.Shapes("Chart 1").IncrementTop -64.5
.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.36, msoFalse,
msoScaleFromTopLeft
.ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3, msoFalse,
msoScaleFromTopLeft
.ActiveSheet.Shapes("Chart 1").ScaleWidth 1.05, msoFalse, _
msoScaleFromBottomRight
End With

"Joel" wrote:

I have two sub below test and test2. I did not have a record set so I
comment out some of the code and got test to run over and over again without
error. I think the problem is with activeworkbook. so I created test2 (did
not try) with referenciing the new workbook using xlwkbk. See code below

Sub test()

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 old = ThisWorkbook
' 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"


xlsheet.Range("A1:C8") = 3
' 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"


End Sub

Sub test2()

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 old = ThisWorkbook
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"


xlsheet.Range("A1:C8") = 3
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

With xlwkbk
.Names.Add Name:="Week", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C1,1,0,COUNTA('Report Name'!C1)-1)"
.Names.Add Name:="Target", RefersToR1C1:= _
"=OFFSET('Report Name'!R1C2,1,0,COUNTA('Report Name'!C2)-1)"
.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"
End With

End Sub



"acccessaccess2003" wrote:

I copied that particular function into a module and run it. On the Excel
ActiveSheet it doesn't show the intended chart so I checked back to VBA IDE
and it shows a dialog box that reads the run-time error 91 msg. I clicked on
'Debug' and the yellow-highlighted error code is as follows:

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

right after dataRange = xl.Selection.Address.

"Joel" wrote:

Which line gives the error?

"acccessaccess2003" wrote:

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"