Steve Goodhall
steve at goodhall.info
Tue Sep 23 14:35:35 CDT 2008
I did this kind of thing a few years ago I was working on my Thesis. It's a bit vague, but I think you will need to set every pointer to anything e.g. Workbooks, Worksheets, Ranges, etc.) in the Excel instance to Nothing. Regards, Steve Goodhall, PMP 248-505-5204 mobile -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don [IT] Sent: Tuesday, September 23, 2008 2:19 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel object behavior Thanks, Shamil As a test I wrote a brief proc that simply opens and then kills an Excel instance. Indeed, the instance does die as soon as its object variable is set to nothing. That is NOT happening in my proc, so it looks like I'm overlooking something there - probably in the way my references are qualified. Still, if I haven't correctly terminated that Excel instance, I'm surprised that it doesn't hang around after the proc finishes. Seems like I'm doing SOMEthing right - just not EVERYthing. Thanks again to all who responded. Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Tuesday, September 23, 2008 10:51 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Excel object behavior <<< Maybe that's normal behavior, maybe not. Anybody know for sure? >>> Don, For sure it's not normal behavior - you should have somewhere "hanging" MS Excel object(s), which were not forcibly set to nothing before you quit MS Excel Application instance, or you might have created a "ghost" instance of MS Excel if you didn't qualify fully MS Excel objects while using them in your VBA code - something like Stuart's sample shows... -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don [IT] Sent: Tuesday, September 23, 2008 8:27 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel object behavior Thanks to all for the feedback, however, my question is not so much how to kill the instance (I seem to be doing that OK), but whether the instance should be surviving to the bitter end of the proc - despite having quit/closed/set to nothing all related Excel objects well before the end of the proc. Maybe that's normal behavior, maybe not. Anybody know for sure? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Monday, September 22, 2008 8:08 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel object behavior Yep, it took me a long time to track down. I had a function which created a Word document from a template and then stuffed data into it was. I cut and pasted code from the Word Macro recorder to do some formatting of the data once it was inserted. It was generally used only once every few days and it always worked fine. Then when a user tried to call it twice in a row it broke the second time around with a 462 error. I finally tracked it down by googling "Error 462: The remote server machine does not exist or is unavailable." On 22 Sep 2008 at 22:05, Michael R Mattys wrote: > That is a spectacular find, Stuart ... the beating heart of VB/VBA > Why have I never seen this 'feature' discussed before? > When I look back now over the years ... lol! > > Michael R. Mattys > MapPoint & Access Dev > www.mattysconsulting.com > > ----- Original Message ----- > From: "Stuart McLachlan" <stuart at lexacorp.com.pg> > To: "Access Developers discussion and problem solving" > <accessd at databaseadvisors.com> > Sent: Monday, September 22, 2008 9:37 PM > Subject: Re: [AccessD] Excel object behavior > > > > Don't know about Excel, but I've had a similar problem occur in Word. The > > problem turned > > out to be using Unqualified References. It's explained at > > http://support.microsoft.com/kb/319832 > > > > Here's an example from that page which illustrates your problem: > > > > <quote> > > Qualifying the Code to Avoid Errors > > The best guideline is to avoid using any Office object that you do not > > explicitly call from a > > parent object that you set in a specific variable. In other words, look > > for code that uses > > Office objects without qualifying which Office instance or document that > > it is supposed to > > refer to. For example, this code uses an unqualified call to display the > > count of open > > workbooks in Microsoft Excel: > > > > Sub CreateThreeBooks() > > Dim oXL As Excel.Application > > Dim i As Long > > > > ' Create Excel instance (make it visible for test)... > > Set oXL = New Excel.Application > > oXL.Visible = True > > > > ' Open a few empty workbooks... > > For i = 1 To 3 > > oXL.Workbooks.Add > > Next i > > > > ' How many books did we open? > > MsgBox "Number of workbooks: " & Workbooks.Count, vbMsgBoxSetForeground > > > > ' Shutdown Excel (or do we?)... > > oXL.Quit > > Set oXL = Nothing > > ' Check the Processes list. Excel.exe is still running! > > End Sub > > > > When you run the code, it appears to run correctly the first time. > > However, Excel continues > > to run, even though you called the Quit method. If you call the code > > again, the message > > box now incorrectly displays 0 for the workbook count. Your code may now > > fail where you > > would expect it to succeed. > > > > To resolve both problems, you must fully qualify the Workbooks object that > > you reference > > for the count, as follows: > > > > MsgBox "Number of workbooks: " & oXL.Workbooks.Count, > > vbMsgBoxSetForeground > > > > </quote> > > > > > >> From: McGillivray, Don [IT] > >> Sent: Mon 9/22/2008 17:10 > >> To: Access Developers discussion and problem solving > >> Subject: [AccessD] Excel object behavior > >> > >> > >> Hello, All > >> > >> I have a procedure that exports a bunch of data to Excel, and then > >> uses the Excel object model to manipulate the resulting spreadsheets > >> (creating pivot tables, applying formatting, and the like.) Usually > >> it works as advertised, but some of my users are having a problem > >> where the UI freezes (or maybe it just appears to freeze). > >> > >> In trying to isolate the problem, I watched the effects of my code > >> carefully, and I notice that the Excel instance that I create in code > >> doesn't terminate until the procedure itself is finished. This, > >> despite the fact that I have closed, quit, and set to nothing all > >> Excel related objects before the end of the procedure. I know through > >> experience that failing to terminate Excel objects correctly leaves > >> the instance alive even at the end of the procedure. So I assume that > >> I'm killing them correctly since the instance does indeed die. > >> > >> Can anybody tell me if I should expect to see the instance survive > >> until the end of the proc, or is there something else I ought to be > >> doing when I'm finished with Excel? > >> > >> Thanks! > >> > >> Don McGillivray > >> > > -- > > Stuart Mclachlan > > -- 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