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

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" <[email protected]> wrote in message news:[email protected]...
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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]>
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"
<[email protected]>
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" <[email protected]> wrote in message
news:[email protected]...
> 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