Object Variable Not Set on Excel Selection Object

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

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 said:
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
Click to expand...

You cannot use any of the Excel objects *directly*, you must reference
through one of the Excel objects you've alredy created.

Any reference to ActiveSheet, Activeworkbook, Selection, Activecell,
Cells ... which is not fully qualified through one of the Excel
objects, will most likely create another instance of Excel in memorey,
causing problems at least the second time it's run, but sometimes also
the first time.

I think the selection property, is a property of the application
object, so after a succsesfull selecting of a range, you should be able
to do

appExcel.Selection.Clear

Anyway, if it's possible, then avoid doing any selection at all.
Selecting stuff usually only adds one thing - time.

Use something more like this

wksNew.Range(wksNew.Cells(6, 9), _
wksNew.Cells(7 + rstProjects.RecordCount, _
rstProjects.Fields.Count - 2)).Clear
' or
wksNew.Columns("A1:F14").Clear
' or
wksNew.Columns("I:L").Clear
' or
wksNew.Columns("I:L").Insert Shift:=xlToRight
....

Here's a bit of reading
http://support.microsoft.com/kb/178510/

Just typing a bit, without removing the selecting stuff - no testing,
no warranties ;-)

appExcel.Selection.Clear
rngCurr.Select
appExcel.Selection.EntireColumn.Select
appExcel.CutCopyMode = False
appExcel.Selection.Cut

'appExcel.ActiveWindow.LargeScroll ToRight:=-2
wksNew.Columns("C:C").Select
appExcel.Selection.Insert Shift:=xlToRight
Set rngCurr = Nothing
 
Hi Roy-Vidar,

Thanks for you suggestion. I do like to avoid using "Selection". What I am
trying to do is try to move a range of columns(the number of columns is
unknown, a crosstab) and inserted them before column C.

Do you recommend any other to code the "Move"?

Thanks

Jean
 
Jean said:
Hi Roy-Vidar,

Thanks for you suggestion. I do like to avoid using "Selection". What
I am trying to do is try to move a range of columns(the number of
columns is unknown, a crosstab) and inserted them before column C.

Do you recommend any other to code the "Move"?

Thanks

Jean
Click to expand...

I think perhaps the pure Excel stuff is something you'll have to visit
Excel NGs for, or play with the macro recorder in Excel - just remember
to fully qualify all Excel objects, methods and properties when using
them through Access.

If there is there anything not working now, please state which line and
which errormessage.

Here's a quicky - say you've selected one cell within the crosstab...

appExcel.Selection.CurrentRegion.EntireColumn.Cut
wksNew.Range("c:c").Insert Shift:=xlToRight
 
Back
Top