Object Variable Not Set Error on Selection object
Hi,
I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. Error: Object Variable Not Set or With Object Variable Not Set ================================================== ================ Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing ================================================== =============== Please help... Thanks Jean |
Object Variable Not Set Error on Selection object
Jean,
Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
Object Variable Not Set Error on Selection object
Hi Jim
I am trying to cut columns in range "rngCurr" to insert cut columns at column "C:C". I don't want to use Selection. But don't know any other way. Please help. Thanks Jean "Jim Cone" wrote in message ... Jean, Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
Object Variable Not Set Error on Selection object
Jean,
Where you would use the term "Selection", just replace it with the actual range object or the range address.... rngCurr.Select Selection.Clear becomes... rngCurr.Clear If the cell formatting is not necessary then assigning the values from one range to another range is the easiest. The ranges, however, must be the same size. A simple example where you know the range size is.... wksNew.Range("A1").Value = wksNew.Range("B1").Value -or if you are using entire columns then... wksNew.Columns("C:C").Value = wksNew.Columns("L:L").Value wksNew.Columns("L:L").ClearContents To make the destination range the same size as the origin range, you can use the "resize" property of the range object. Pick the top left cell of the destination range, say C5, then... dim newRange as Excel.Range Set newRange = wksNew.Range("C5").Resize(rngCurr.Rows.Count, rngCurr.Columns.Count) newRange.Value = rngCurr.Value rngCurr.ClearContents Note: When automating Excel, always set objects to Nothing before quitting the application, except for the application object which is set to nothing after quitting. Regards, Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Jean" wrote in message Hi Jim I am trying to cut columns in range "rngCurr" to insert cut columns at column "C:C". I don't want to use Selection. But don't know any other way. Please help. Thanks Jean "Jim Cone" wrote in message Jean, Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
All times are GMT +1. The time now is 01:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com