Instance of Excel will not go away

[Guest]

My understanding is that when you create an Excel object, the correct way to
cause the instance of Excel (in Task Manager Process) is to quit the
application and set the object variable to Nothing.

You will not in my code below that I am doing that. Note that this code is
not attached to a form, yet. The instance of Excel will not go away until I
close my Access database. Could it be where I do the Select? Is it
necesssary to deselect it to get the instance to go away?

Here I open it:
Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.Interactive = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select
Selection.End(xlDown).Select
intLastRow = Selection.Row

code does stuff here

Here I close it:
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
rst.Close
Set rst = Nothing
End Sub

 

[Ken Snell [MVP]]

These three lines of code are creating a new reference to EXCEL because you
have not fully qualified them with the objects that you created:

ActiveSheet.Range("F3").Select
Selection.End(xlDown).Select
intLastRow = Selection.Row

Most likely, the problem will be resolved if you change these four lines of
code:

xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select
Selection.End(xlDown).Select

intLastRow = Selection.Row

to these:

xlBook.Worksheets("Actuals_res_export").Activate
xlBook.Worksheets("Actuals_res_export").Range("F3").Select
xlBook.Worksheets("Actuals_res_export").Selection.End(xlDown).Select
intLastRow = xlBook.Worksheets("Actuals_res_export").Selection.Row

 

[Guest]

Thanks, Ken. I will give it a try even though I still don't quite understand
what the difference. If it is necessary to fully gualify, then what good is
ActiveBook or ActiveSheet and why would it not be necessary to really fully
qualify the reference like:
xlApp.xlBook.Worksheets("Actuals_res_export").Activate

 

[Guest]

Sorry, Ken, this did not work. I copied the lines in as you suggested, and
the last 2 lines each raised error 438 "Object does not support the property
or method. And, I still have to close the database for the instance of Excel
to close.
I do thank you for your input.

 

[Ken Snell [MVP]]

ActiveSheet is an object in the Excel.Application program. When it's used in
VBA code from a non-EXCEL application, it creates a new reference into the
EXCEL application that is not included in the objects that you've creating.

This Knowledge Base article may provide more information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;178510
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

I "guessed" at the reference "trail" for the Selection object. I prefer to
not use the Selection object in my code and as such am not very "conversant"
about it.

Perhaps you will need to include the cell Range object in the reference.

Sorry for the nonworking code suggestion.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Klatuu -

One other item I note.. You're using the intrinsic EXCEL constant xlDown in
the code. ACCESS will not know what that constant value is unless you have a
reference set to EXCEL library in your database. You may need to replace it
with its actual numerical value.

 

[Guest]

Ken,

I believe Klatuu will back me up on this one..

Activesheet...... Activecell..... Selection....

Are ALL objects directly related to the interface... They have their
purpose, when you need to modify the interface (for whatever reason)

Also, they are use a LOT in recording macros...

It is a bad, bad, idea to automate solutions based on objects relating to
the interface.
Recording and having Activesheet etc, is okay, but when you clean up the
code, it's best to replace activesheet with the specific worksheet you want
to work on.

A good example why..

You are debugging a macro.. you stop the code a few lines before a
Selection.copy command.

In reviewing the data, you change the selection and before you continue
running, you don't change it back to what it was before. The macro will
execute the Selection.copy based on what you selected in the interface. Just
bad hoo-doo.

 

[Tim Ferguson]

I think this is the one:

xlBook.ActiveWindow.Selection.End(&HFFFFEFE7).Select



All the best


Tim F

 

[Guest]

or.. in longs
xlup = -4162
xldown = -4121
xltoleft = -4159
xltoright = -4161

 

[Ken Snell [MVP]]

When I write VBA code in EXCEL, I normally avoid using ActiveSheet, etc.
because sometimes the "active" thing does change from keyboard actions or
other code steps... so I have settled on fully qualified references using
worksheet, workbook, and range objects that I expressly set to the desired
"thing".

So I agree with your comment that their use in Automation is not a good
option.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Thanks, Ken. I will give it a try even though I still don't quite
understand
what the difference. If it is necessary to fully gualify, then what good
is
ActiveBook or ActiveSheet and why would it not be necessary to really
fully
qualify the reference like:
xlApp.xlBook.Worksheets("Actuals_res_export").Activate

The above step is fully qualfiied in its use of objects... you set up xlApp
and xlBook, so your use makes the code fully qualified.