Closing Excel

[J Lunis]

XP SR2 / Word (Office) 2003

I have created a form in Word that calls Excel, retrieves some data, and
closes Excel. Well, actually, I guess I don't close Excel and that is
my problem.
The code I am using to open Excel is
Set filename = GetObject(, "Excel.Application")
Set filename = ("C:\Microsoft Office\Templates")

Closing Excel is attempted with
filename.Quit SaveChanges:=DoNotSaveChanges

Works OK, I get no errors and Excel disappears from the list of
Applications/Processes in Task Manager. However, when I shut down my
PC, I get a popup asking if I want to close filename.exe. How do I
close Excel so I no longer get the popup?

 

[John]

J,

You appear to be using the same variable for the application and a file.
GetObject(.... returns the Excel Application object so set that first
(almost as you have done:

Dim xlApp As Excel.Application

Set xlApp = GetObject(, "Excel.Application")

Then deal with your file.....open it, do something to it and finaly close
it............

Then close the Excel by calling the Quit method of the application object
(xlApp.Quit)

The above is untested but I think that's the correct procedure.

Hope it helps.

Best regards

John

 

[Ed]

At the end of your macro, after closing the workbook and quitting the
application set your workbook and application objects = Nothing. Objects
are usually destroyed when a macro ends, but it's just a good practice to
get into for that odd occasion when something hangs.

Ed

 

[J Lunis]

Thanks. Not sure I understand. I am not near my code now bu I believe
I have the Dim statement as you have it - left it out since I didn't
think it mattered.
Are you suggesting two variables as follows . . .
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
Set filename = ("C:\Microsoft Office\Templates")
followed by
xlApp.Quit

 

[Jean-Guy Marcil]

J Lunis was telling us:
J Lunis nous racontait que :

Thanks. Not sure I understand. I am not near my code now bu I
believe I have the Dim statement as you have it - left it out since I
didn't think it mattered.
Are you suggesting two variables as follows . . .
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
Set filename = ("C:\Microsoft Office\Templates")

Why are you setting a folder as a file?
Where is the Dim statement for "filename"?

followed by
xlApp.Quit

John wrote:

It depends what you want to do.
You can use GetObject or CreateObject, or even the New keyword.

Normally, we test to see if the application is already running, in which
case we use GetObject. If there is no need to test we can use CreateObject.

In the VBA editor, type GetObject, select it and hit F1 for the help topic.
You could do the same for CreateObject to see the difference.

In your original code, you were setting a variable as an Excel application,
then the same variable was reset as an Excel workbook.
Set filename = GetObject(, "Excel.Application")
'(If Excel is already running, this gets a handle on it, if not, it
generates an error.)
Set filename = ("C:\Microsoft Office\Templates")
'(This reassign filename to a folder? You wrote that this worked?)
Even if you closed that last variable, the first one that was created was
still running invisibly in the background.
filename.Quit SaveChanges:=DoNotSaveChanges


For more on this, see
http://word.mvps.org/faqs/interdev/controlxlfromword.htm
And,
http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm


--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jezebel]

Another possibility: Excel is *very* sensitive to errors when called
remotely like this. There are some code mistakes that do not throw an error,
but which cause Excel to sulk and refuse to close. Most common (in my
experience) is a use of the Range keyword, eg as an argument to the sort
function, such as you would get if you copied some code from Excel VBA to
Word VBA: Both Excel and Word have Range objects, but obviously not
interchangeable. If you pass an Word range to an Excel function, strange
things happen.

 

[J Lunis]

OK, let me start again.

Original code was (roughly)
Dim xlApp As Excel.Application
If Excel is not already Open
Set xlApp = GetObject(, "Excel.Application")
Endif
Set xlApp = ("C:\Microsoft Office\Templates\filename.exe")
A few lines to move data from Excel to Word
Application.Quit

Due to suggestions here, I just tried
Dim xlApp As Excel.Application
If Excel is not already Open
Set xlApp = GetObject(, "Excel.Application")
Endif
Set xlFile = ("C:\Microsoft Office\Templates\filename.exe") changed
variable
A few lines to move data from Excel to Word
Application.Quit SaveChanges:= DoNotSaveChanges
xlApp = Nothing

After these changes, I still get a popup on logoff asking if I want to
close filename.exe.

 

[Jean-Guy Marcil]

J Lunis was telling us:
J Lunis nous racontait que :

OK, let me start again.

Original code was (roughly)
Dim xlApp As Excel.Application
If Excel is not already Open
Set xlApp = GetObject(, "Excel.Application")
Endif
Set xlApp = ("C:\Microsoft Office\Templates\filename.exe")
A few lines to move data from Excel to Word
Application.Quit

Due to suggestions here, I just tried
Dim xlApp As Excel.Application
If Excel is not already Open
Set xlApp = GetObject(, "Excel.Application")
Endif
Set xlFile = ("C:\Microsoft Office\Templates\filename.exe") changed
variable
A few lines to move data from Excel to Word
Application.Quit SaveChanges:= DoNotSaveChanges
xlApp = Nothing

After these changes, I still get a popup on logoff asking if I want to
close filename.exe.

Well, for starters, "filename.exe" is not an Excel file and
"Application.Quit SaveChanges:= DoNotSaveChanges" does not refer to either
xlApp or xlFile .
Again, what is xlFile defined as (where is the Dim statement?)

Also, it would be useful if you posted the actual code you use.
If Excel is not already Open
Endif
Set xlFile = ("C:\Microsoft Office\Templates\filename.exe") changed
variable
etc.
are not real code lines. We cannot tell you what is wrong with your code if
we do not see it.

Have you tried the VBA help file and the web pages I suggested?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[J Lunis]

J Lunis was telling us:
J Lunis nous racontait que :


Well, for starters, "filename.exe" is not an Excel file and
"Application.Quit SaveChanges:= DoNotSaveChanges" does not refer to either
xlApp or xlFile .
Again, what is xlFile defined as (where is the Dim statement?)

Also, it would be useful if you posted the actual code you use.
If Excel is not already Open
Endif
Set xlFile = ("C:\Microsoft Office\Templates\filename.exe") changed
variable
etc.
are not real code lines. We cannot tell you what is wrong with your code if
we do not see it.

Have you tried the VBA help file and the web pages I suggested?

When I get near my code again I'll send it in.
Yeah, went to the web site. Without having my coode in hand, the only
thing that I see different is I have DIm XLfile as an Object instead of
Excel Workbook and I set the XLapp to Nothing but didn't do the same to
the workbook.
BTW, that filename.exe should be filename.xls. I need to stop trying to
multitask - it is obviously not my strength.

 

[Perry]

Hmm, try below steps in yr code:

Set xlFile = ("C:\Microsoft Office\Templates\filename.exe") changed

Replace by:
Set xlFile = xlApp.Workbooks.Open("C:\Microsoft
Office\Templates\filename.exe")

To save with changes:
Use following statement:
xlFile.Close 0

To quit Excel:

Application.Quit SaveChanges:= DoNotSaveChanges

Replace by:
xlApp.Quit

To release resource claim by the xlApp variable

xlApp = Nothing

Replace by:
Set xlApp = Nothing

Krgrds,
Perry