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

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

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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,
Thanks for your response. I've already tried this and it does not clear the
process in TaskManager.

Any other ideas?
Thanks,
VBA Dabbler

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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,
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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

Have you tried

set xlApp = nothing

in your Word code?

"VBA Dabbler" wrote:

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

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

"Jim Thomlinson" wrote:

Have you tried

set xlApp = nothing

in your Word code?

"VBA Dabbler" wrote:

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

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in message
...
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

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" wrote:

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in message
...
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

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


"VBA Dabbler" wrote in message
...
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" wrote:

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in message
...
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

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" wrote:

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


"VBA Dabbler" wrote in message
...
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" wrote:

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in message
...
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

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

"VBA Dabbler" wrote in message
...
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" wrote:

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


"VBA Dabbler" wrote in message
...
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" wrote:

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in
message
...
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

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.

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
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" wrote:

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


"VBA Dabbler" wrote in message
...
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" wrote:

Maybe -

xlApp.Quit
set xlApp = nothing

Regards,
Peter T

"VBA Dabbler" wrote in
message
...
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