Object variable or With Block variable not set

[acccessaccess2003]

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 Where:=xlLocationAsObject, Name:="Report Name"

 

[Joel]

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

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

 

[acccessaccess2003]

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]

Which line gives the error?

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?

 

[acccessaccess2003]

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]

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 Where:=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 Where:=xlLocationAsObject, Name:="Report Name"
End With

End Sub

 

[acccessaccess2003]

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 Where:=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]

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]

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]

I gave the chart a name. what I do to debug code is a newchart to ADD WATCH
by highlight variable and right click. Then step through code to find
objects I'm having problems with.


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)"
Set newchart = .Charts.Add
newchart.Name = "My Chart"
newchart.ChartType = xlColumnClustered
newchart.SetSourceData Source:=xl.Sheets("Report
Name").Range(DataRange), _
PlotBy:=xlColumns
newchart.SeriesCollection(1).Delete
newchart.SeriesCollection(1).XValues = "='Report Name'!Week"
newchart.SeriesCollection(2).XValues = "='Report Name'!Week"
newchart.Location Where:=xlLocationAsObject, Name:="Report Name"

With newchart
.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

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

 

[Joel]

I have some code below that may help. The best way to proceed is to Record a
Macro, then manually make the changes you want and modify the recorded code.
I also use the trick I told you about last night to add newchart to the watch
window. then look at the properties of newchart by opening the plus sign in
the watch window while you are stepping through the code

It doesn't appear that a chart on a Chart sheet is a shape (not sure).
Charts on worksheets are slightly different then charts on there own page.
there is a shape area if you go into plotarea and select parent.

newchart.plotarea.parent.shapes

But this seem to be empty because it has a count of 0. Hope this helps.

Sub test()
DataRange = "A1:B2"
Set xl = ThisWorkbook

Set newchart = Charts.Add
newchart.Name = "My Chart " & Sheets.Count
newchart.ChartType = xlColumnClustered
newchart.SetSourceData _
Source:=xl.Sheets("Report Name").Range(DataRange), _
PlotBy:=xlColumns

With newchart.PlotArea
.Left = .Left + 9
.Top = .Top - 64.5

.Width = .Width * 1.3
.Width = .Width * 1.05
End With
With newchart.Axes(xlValue)
.MaximumScale = 1.36
End With

End Sub