Clearing objects in a VBProject

[VBA Dabbler]

How do you clear an object from the VBProject once it has served its
usefulness?

I've done the following:
1. Opened a workbook from a Word VBProject with the following code:
Dim xlApp As Excel.Application
Dim FileString As String
FileString = "C:\MyDocuments\MyWorkbook.xls"
Excel.Application.Visible = True
Workbooks.Open (FileString) 'Focus is now on the workbook
2. Closed workbook by closing Excel

I've found that there is still an instance of the an object reference to the
Excel app - there is the "Excel.exe" process in the Windows Taskmanager.

I've found that when I reset the VBProject where the above code resides, the
instance is cleared and the "Excel.exe" process in the Windows Taskmanager is
removed.

Do you know how to selectively clear the instance of the object reference in
the VBProject?

Thanks,

VBA Dabbler

 

[Tom Ogilvy]

Dim xlApp As Excel.Application
Dim FileString As String
FileString = "C:\MyDocuments\MyWorkbook.xls"
xlApp.Application.Visible = True
xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook
set xlapp = Nothing

 

[Jim Thomlinson]

Have you tried

set xlApp = nothing

in your Word code?

 

[VBA Dabbler]

Tom,
Thanks for your response. I've already tried this and it does not clear the
process in TaskManager.

Any other ideas?
Thanks,
VBA Dabbler

 

[VBA Dabbler]

Jim,
Thanks for your response. I've already tried it and it doesn't work.
Actually the "FileString" is pointing to a file on a network server, not one
on the harddrive. I don't think that should matter.
Thanks,
VBA Dabbler

 

[Peter T]

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

 

[VBA Dabbler]

Peter,
Thanks for your response. I tried this also - it does not work. Reason
being, it closes Excel, but does not release the object created in the
VBProject.

The only thing I found that works is to reset the project. Do you know how
to do that with VB scripting?

Thanks,
VBA Dabbler

 

[Peter T]

Sorry, I didn't read your question properly. You said Excel is closed by
user later, not in you routine so don't use Quit.

I didn't try but am surprised your Workbooks open line works.

This works for me in Word without leaving Excel in the taskbar when user
quits:

Sub Test()
Dim xlApp As Object
Dim xlWB As Object

Set xlApp = CreateObject("excel.application") ' new instance or try
GetObject ?
'assumes c\:test.xls exists
Set xlWB = xlApp.Workbooks.Open("C:\test.xls")

xlApp.Visible = True
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

FWIW, the statement "End" on it's own should reset word vba, but shouldn't
be necessary and not recommended. Do you still need VB scripting?

Regards,
Peter T

 

[Tom Ogilvy]

It is obvious that you didn't try it, so apparently you not are looking for
the correct solution.

I tested your code and experienced the same problem - no surprise.

I ran this code
Sub TesterAA100()
Dim xlApp As New Excel.Application
Dim FileString As String
FileString = "C:\MyDocuments\MyWorkbook.xls"
xlApp.Application.Visible = True
xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook
Set xlApp = Nothing

End Sub

and did not have the problem.

If you want to reset then put the single statement

END
in your code. But that would be an ill advised solution.

 

[VBA Dabbler]

Tom,
Thanks for your perserverance - I am looking for the correct solution.

I didn't look closely enough to your response to notice that you were using
the 'xlApp' object in the 'Visible' and 'Workbooks.Open' commands.

Once I included it in both, the dangling instance of 'Excel.exe' as a
process in Task Manager properly clears.

Thanks so much for your help, Tom.

Regards,

VBA Dabbler

 

[VBA Dabbler]

Peter,
That's all right - I left that part of the story out. Your suggestion below
does work. Thanks for your help.

I think the crux of the problem was my not including the 'xlApp' object in
the 'Workbooks.Open' command, which obviously you included.

Follow-up Question: Since the workbook will be left open for the user to
decide when to close the workbook and terminate the application, is it
necessary to set the workbook and application objects to 'Nothing'?

Regards,
VBA Dabbler

 

[Peter T]

Follow-up Question: Since the workbook will be left open for the user to

decide when to close the workbook and terminate the application, is it
necessary to set the workbook and application objects to 'Nothing'?

From what I understand it's certainly good practice to release the object
variables. In some situations the object can remain in memory and a cause of
memory leaks. I can't say though if that might occur in this example, but if
in doubt - explicitly release them when done. Also be sure to release in
reverse hierarchical order. So in my example, first Workbook then the Excel
object variables.

I really don't know why I didn't see Tom's initial reply to you before I
posted. Had I done so I would have simply nudged you into taking a closer
look at that, rather than suggesting something very similar. Anyway glad you
got it working.

Regards,
Peter T

 

[Tom Ogilvy]

The reason the Excel object stays in memory is because your procedure
maintains a reference to it. In your original code, you used references
which could not be released (by using workbook.Open directly as an example).
When you chain everything through the reference to xlApp, then when you
release xlApp (by setting it to nothing) you release the references and
Excel closes all the way when the user closes it. (doesn't remain in the
task list)

If you create intermediate references based off of the top level reference
(xlApp), then you should set those to nothing in the reverse order that you
created them.

set xlApp = Creatobject( . . .
set xlbk = xlApp.workbooks.Open( . . .
set xlSheet = xlbk.worksheets(1)
set xlRng = xlSheet.Range(" . . .
set xlRng = nothing
set xlSheet = nothing
set xlbk = nothing
set xlapp = nothing

In the case of automation, it is more than good practice. It is required
that you release your references.

The problem can be created in subtle ways

xlRng.sort Key1:=Range("A1"), Order1:=xlAscending

using Range("A1") in this example creates a non-releasable reference to
excel and recreates the problem you were having.

xlRng.sort Key1:=xlRng(1), Order1:=xlAscending

would fix that.