Stuart McLachlan
stuart at lexacorp.com.pg
Mon Sep 22 22:07:41 CDT 2008
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 -- Stuart Mclachlan