topic Re: How to end Excel from VBA in VBA Forum

How to end Excel from VBA

Anonymous — Tue, 29 Apr 2003 07:49:42 GMT

I'm having a hell of time trying to close down excel from my vba program.
My operating system is Windows 98.

Here is the code:

Private Sub Excel_Running_Check()
Dim excelApp As Excel.Application
On Error Resume Next
Err.Clear
Set excelApp = GetObject(, "excel.application")
If Err <> 0 Then
Err.Clear
Else
excelApp.DisplayAlerts = False
excelApp.ActiveWorkbook.Close
excelApp.Quit
Set excelApp = Nothing
End Sub

The idea for this sub is to check for a running instance of excel. If it
exists then close it down before opening up another instance of excel. The
problem is that excel still remains memory resident. I verify this by using
ctrl-alt-del to open the task manager and notice that excel is still
running. The only way I can get excel to stop running is to close down my
application with the End function. I've seen similar posts all over the
internet but I haven't found an answer to it yet. The fact that excel is
not released from memory causes problems when reopening excel through my
program. Subsequent intances of spreadsheets are not formatted
programatically as is intended through my code. Several instances of excel
remain running in task manager. Please help.

Re: How to end Excel from VBA

Anonymous — Tue, 29 Apr 2003 08:03:46 GMT

This is a known problem with Excel. I suspect that you havn't shown all of
your code, but the rule here is to clear up all your object variables in the
reverse of the hierarchical order that you derived them. For example, you
start with an application object, then use it to create a workbook object,
and then use that object to create a sheet object. Before you close the app,
you need to set to nothing, first the sheet object and then the workbook
object. Then you can deal with the app object.

--
Ed
--

"Gordon Reichhardt" wrote in message
news:66D7FF33CB39611744607A55E9E7CD31@in.WebX.maYIadrTaRb...
> I'm having a hell of time trying to close down excel from my vba program.
> My operating system is Windows 98.
>
> Here is the code:
>
> Private Sub Excel_Running_Check()
> Dim excelApp As Excel.Application
> On Error Resume Next
> Err.Clear
> Set excelApp = GetObject(, "excel.application")
> If Err <> 0 Then
> Err.Clear
> Else
> excelApp.DisplayAlerts = False
> excelApp.ActiveWorkbook.Close
> excelApp.Quit
> Set excelApp = Nothing
> End Sub
>
> The idea for this sub is to check for a running instance of excel. If it
> exists then close it down before opening up another instance of excel.
The
> problem is that excel still remains memory resident. I verify this by
using
> ctrl-alt-del to open the task manager and notice that excel is still
> running. The only way I can get excel to stop running is to close down
my
> application with the End function. I've seen similar posts all over the
> internet but I haven't found an answer to it yet. The fact that excel is
> not released from memory causes problems when reopening excel through my
> program. Subsequent intances of spreadsheets are not formatted
> programatically as is intended through my code. Several instances of
excel
> remain running in task manager. Please help.
>
>

Re:

Anonymous — Wed, 30 Apr 2003 21:19:01 GMT

I have the same behaviour with Visual Lisp see:

customization: Excel Quit: new behaviour (04/29/03)

I am searching a solution.

Marco

-------------------------------------------------------

1) if I run this function the Excel app is closed correctly
but if I activate the lines with ";<<" Excel is not closed
completely and in task manager remain a instance, why?

2) is it a method to find any "non-released" objects?

--

; A2K2 - SDI = 1 - Excel not running
(defun C:XlTest ( )
(vl-load-com)
(setq
*ExcelApp* (vlax-get-or-create-object "Excel.Application")
*ActiveWbk*
(vlax-invoke-method
(vlax-get-property *ExcelApp* 'WorkBooks) 'Add
)
*ActiveSht* (vlax-get-property *ExcelApp* 'ActiveSheet)
*ActiveCls* (vlax-get-property *ActiveSht* 'Cells)
)
(vla-put-visible *ExcelApp* 1)
(setq
SttCll (vlax-variant-value
(vlax-get-property
*ActiveCls* 'Item
(vlax-make-variant 4) (vlax-make-variant 4)
)
)
EndCll (vlax-variant-value
(vlax-get-property
*ActiveCls* 'Item
(vlax-make-variant 4) (vlax-make-variant 10)
)
)
; RngObj (vlax-get-property *ActiveSht* 'Range SttCll EndCll);<<
)
(vlax-release-object SttCll)
(vlax-release-object EndCll)
; (vlax-release-object RngObj) ;<<
(setq RngObj nil SttCll nil EndCll nil)
(vlax-release-object *ActiveCls*)
(vlax-release-object *ActiveSht*)
(vlax-invoke-method *ActiveWbk* 'Close :vlax-False)
(vlax-release-object *ActiveWbk*)
(vlax-invoke-method *ExcelApp* 'QUIT)
(vlax-release-object *ExcelApp*)
(setq
*ExcelApp* nil *ActiveWbk* nil *ActiveSht* nil *ActiveCls* nil
)
(gc)
)

--

"Ed Jobe" ha scritto nel messaggio
news:8483292FF87D571DBF50F70EDED0016D@in.WebX.maYIadrTaRb...
> This is a known problem with Excel. I suspect that you havn't shown all of
> your code, but the rule here is to clear up all your object variables in
the
> reverse of the hierarchical order that you derived them. For example, you
> start with an application object, then use it to create a workbook object,
> and then use that object to create a sheet object. Before you close the
app,
> you need to set to nothing, first the sheet object and then the workbook
> object. Then you can deal with the app object.
>
> --
> Ed
> --
>

Re: How to end Excel from VBA

Anonymous — Wed, 11 May 2005 13:16:44 GMT

I have has the same problem but finally found a solution!
The problem fore me would occur when I was formatting the cells in the worksheet.
Avoid using .Select. ActiveSheet, ActiveCell, With, .Range, and .Select.
Instead use the object reference.
For example instead of :
Range("A1:A3").Font.Bold = True
try:
objSheet.Range("A1:A3").Font.Bold = True

Instead of
'With objSheet.Rows("4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

try:

objSheet.Rows("4").HorizontalAlignment = xlCenter
objSheet.Rows("4").VerticalAlignment = xlCenter
objSheet.Rows("4").WrapText = True
objSheet.Rows("4").Orientation = 0
objSheet.Rows("4").AddIndent = False
objSheet.Rows("4").IndentLevel = 0
objSheet.Rows("4").ShrinkToFit = False
objSheet.Rows("4").MergeCells = False

When you're ready to close Excel you will want to use
objExcel.quit
set objExcel = nothing

It works great for me.
Hope this helps

Re: How to end Excel from VBA

Anonymous — Wed, 11 May 2005 13:55:34 GMT

As pointed out it is a long standing issue. Try this as a long standing solution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;132535

Good luck,

Bob Coward
CADS, Inc

800-366-0946
bcoward@mindspring.com

Re:

Anonymous — Thu, 20 Apr 2006 13:50:14 GMT

Marc...

I am trying to write a piece of LISP code to answer an "alert box" that pops up when I load a linked Excel file...

I found your email in a LISP program (MsXUtl01.LSP)…you look to be an Excel-AutoCAD expert, so I am asking you this question!! (Thanks!)

I have a problem I’m working on…I need to “translate” a line of VBA code into its LISP equivalent…

Set xlbook = xlapp.Workbooks.Open(path & "\" & dwgname & "-" & "KeyNotes Data Main.xls", UpdateLinks:=3)

This line of VBA code opens an Excel file and answers “yes” to an alert asking “Do you want to Update Links?” … “Yes / No”…

Here’s what I have so far for my LISP…and it works, except that the program stops when the alert is displayed asking the user to indicate his Link Updating preference…

((setq excelsession (vlax-get-or-create-object "Excel.Application"))
(vlax-invoke-method
(vlax-get-property excelsession 'WorkBooks)
'Open fn ßHOW DO I “FEED” A RESPONSE TO EXCEL WHEN IT ASKS IF I WANT TO UPDATE LINKS??? (“YES”)
)
(if (= (strcase dmode) "SHOW")
(vla-put-visible excelsession 1)
(vla-put-visible excelsession 0)
)
)

Any help or suggestion will be greatly appreciated!

Pete

Re: How to end Excel from VBA

Anonymous — Mon, 27 Nov 2006 03:25:59 GMT

The Microsoft Knowledge Base article referred to seems to apply to FoxPro...

APPLIES TO
• Microsoft Visual FoxPro 3.0 Standard Edition
• Microsoft Visual FoxPro 5.0 Standard Edition
• Microsoft Visual FoxPro 5.0a
• Microsoft Visual FoxPro 6.0 Professional Edition

Is there a VBA specific solution?

Thanks!

Re: How to end Excel from VBA

Anonymous — Mon, 27 Nov 2006 12:44:49 GMT

Hi,
I have to tryed to rewrite this code on pure VBA with using
of API functions see my example
Make sure that in VBEditor ->Tools->Options->General
was checked "Break on Unhandled Errors" button
Feel free to adapt it to your suit

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Any) As Long
Const WT_SLEEP = 2500
Const WM_CLOSE = &H10
Const WM_ACTIVATEAPP = &H1C
Const WM_USER = 1024
Const gcClassnameMSExcel = "XLMAIN"

Sub TestExcelQuit()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim hwnd As Long
Dim WhatD As Long
On Error Resume Next
Set xlApp = Nothing
Set xlApp = GetObject(, "Excel.Application")
If Err.Number 0 Then 'read this line as If Err.Number not equal zero
'because this symbol not shown correctly in this code
'I don't know why
Set xlApp = CreateObject("Excel.Application")
End If
Err.Clear
On Error GoTo Err_Control
MsgBox "Sleep 2.5 seconds"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.worksheets(1)
xlSheet.Application.Visible = True

Sleep WT_SLEEP ' pause 2.5 sec

xlBook.Close Savechanges:=False
Set xlSheet = Nothing
Set xlBook = Nothing

hwnd = FindWindow(gcClassnameMSExcel, 0)

Do While hwnd > 0
'WhatD = SendMessage(hwnd, WM_USER + 18, 0, ByVal 0&) ' Microsoft Excel 95
WhatD = SendMessage(hwnd, WM_ACTIVATEAPP, 0, ByVal 0&) ' Microsoft Office 2000 or Microsoft Office XP
'Set xlApp = GetObject(, "Excel.Application")
xlApp.Quit
hwnd = FindWindow(gcClassnameMSExcel, 0)

Loop
Set xlApp = Nothing

Err_Control:
If Err Then
MsgBox Err.Description
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If

End Sub

Fatty

~'J'~ Message was edited by: Fatty

Re: How to end Excel from VBA

Mikko — Tue, 28 Nov 2006 16:40:57 GMT

http://www.devcity.net/Articles/239/1/article.aspx