Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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 Whe=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 "acccessaccess2003" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object variable or With Block variable not set
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 "Joel" wrote: 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 Whe=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 "acccessaccess2003" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Number: 91 object variable or With block Variable not set | Excel Programming | |||
Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
Run-time Error'91: Object variable or With block variable not set | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |