Hale, Jim
Jim.Hale at FleetPride.com
Thu Oct 27 08:39:17 CDT 2005
OK. How? Jim -----Original Message----- From: John Colby [mailto:jwcolby at colbyconsulting.com] Sent: Wednesday, October 26, 2005 6:43 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Preventing Extra Excel instances SOLVED You need to report this one to Microsoft. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, October 26, 2005 7:24 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Preventing Extra Excel instances SOLVED Hi Jim: Excellent deduction. I have been annoyed by that issue for years. Truly a piece of brilliance. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Wednesday, October 26, 2005 8:09 AM To: 'Accessd (E-mail) Subject: [AccessD] Preventing Extra Excel instances SOLVED As I have commented on several occasions, I've had a pesky problem with orphan Excel instances that are not destroyed when the program finishes. As several of you pointed out all object variables must explicitly be set to nothing. While this cured some cases I have continued to have Excel instances that refuse to die. I have finally traced the cause to the following code fragments: Public Function UpdateLoan(appXcel As Excel.Application) Dim rng(1 To 2) As Range With appXcel Do While Not IsEmpty(ActiveCell) ............. Set rng(1) = .Range(Cells(2, intCopycol), Cells(47, intCopycol)) The following code fragments are the culprit: isEmpty(ActiveCell) and .Range(Cells(2, intCopycol), Cells(47, intCopycol)) The code works fine but leaves the Excel instance in memory. a period (.) before ActiveCell and Cells solves the problem, ie.: isEmpty(.ActiveCell) .Range(.Cells(2, intCopycol), .Cells(47, intCopycol)) Apparently global members of the Excel application object such as ActiveCell and Cells must be explicitly qualified with the Application object for them to be properly destroyed when they go out of scope. Even though the program will run correctly without them being qualified, they are not cleaned up properly when used this way. Exactly what is happening internally wiser heads than I may want to comment. Jim Hale *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email.