[AccessD] Excel object behavior

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




More information about the AccessD mailing list