<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] Excel zombie won't die</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>The NEW keyword worked. Many Thanks!</FONT>
<BR><FONT SIZE=2>Jim Hale</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Michael R Mattys [<A HREF="mailto:michael.mattys@adelphia.net">mailto:michael.mattys@adelphia.net</A>]</FONT>
<BR><FONT SIZE=2>Sent: Saturday, August 30, 2003 12:22 PM</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: Re: [AccessD] Excel zombie won't die</FONT>
</P>
<BR>
<P><FONT SIZE=2>Jim,</FONT>
</P>
<P><FONT SIZE=2>Maybe Set appExcel = NEW Excel.Application</FONT>
<BR><FONT SIZE=2>would put the app under Access' thread control,</FONT>
<BR><FONT SIZE=2>allowing it to close the Excel app?</FONT>
</P>
<P><FONT SIZE=2>Michael R. Mattys</FONT>
<BR><FONT SIZE=2>www.mattysconsulting.com</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2>----- Original Message -----</FONT>
<BR><FONT SIZE=2>From: "Hale, Jim" <jim.hale@fleetpride.com></FONT>
<BR><FONT SIZE=2>To: "'Access Developers discussion and problem solving'"</FONT>
<BR><FONT SIZE=2><accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>Sent: Saturday, August 30, 2003 12:57 PM</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] Excel zombie won't die</FONT>
</P>
<BR>
<P><FONT SIZE=2>> Thank you for your reply. Here is a test function I used</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> Function test()</FONT>
<BR><FONT SIZE=2>> Dim appExcel As Excel.Application</FONT>
<BR><FONT SIZE=2>> Set appExcel = Excel.Application</FONT>
<BR><FONT SIZE=2>> appExcel.Visible = True</FONT>
<BR><FONT SIZE=2>> appExcel.Workbooks.Open _</FONT>
<BR><FONT SIZE=2>> "C:\Documents and Settings\jhale\Application</FONT>
<BR><FONT SIZE=2>> Data\Microsoft\Templates\PlanTemplate.xlt"</FONT>
<BR><FONT SIZE=2>> appExcel.ActiveWorkbook.Close SaveChanges:=False</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> The_End:</FONT>
<BR><FONT SIZE=2>> appExcel.Quit</FONT>
<BR><FONT SIZE=2>> Set appExcel = Nothing</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> End Function</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> After this runs an instance of Excel can be seen in the task manager. If I</FONT>
<BR><FONT SIZE=2>> kill it the next time I run the code it says the remote server cannot be</FONT>
<BR><FONT SIZE=2>> found. The only way to kill it is to close Access. I thought appExcel.quit</FONT>
<BR><FONT SIZE=2>> would shut it down. Any thoughts? This is office 2000 run under windows</FONT>
<BR><FONT SIZE=2>2000</FONT>
<BR><FONT SIZE=2>> prof. TIA</FONT>
<BR><FONT SIZE=2>> Jim Hale</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> -----Original Message-----</FONT>
<BR><FONT SIZE=2>> From: Henry Simpson [<A HREF="mailto:hsimpson88@hotmail.com">mailto:hsimpson88@hotmail.com</A>]</FONT>
<BR><FONT SIZE=2>> Sent: Friday, August 29, 2003 2:33 PM</FONT>
<BR><FONT SIZE=2>> To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>> Subject: Re: [AccessD] Excel zombie won't die</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> It looks like you aren't giving us the entire picture. Presumably you</FONT>
<BR><FONT SIZE=2>> created a workbook object? I generally count my Object variables in the</FONT>
<BR><FONT SIZE=2>> procedure and make sure that each Set statement has a matching Set x =</FONT>
<BR><FONT SIZE=2>> Nothing in the error handler, and I make sure there is an On Error Resume</FONT>
<BR><FONT SIZE=2>> Next as the first line after the Label that marks the exit routine.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> In Access/Excel 97, using the following code that mirrors your use of the</FONT>
<BR><FONT SIZE=2>> only object variable you show, the instance of Excel is created and</FONT>
<BR><FONT SIZE=2>> destroyed without any problem:</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> Sub ExTst()</FONT>
<BR><FONT SIZE=2>> Dim x As Excel.Application</FONT>
<BR><FONT SIZE=2>> Set x = Excel.Application</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> x.Visible = True</FONT>
<BR><FONT SIZE=2>> Msgbox "Pause Here"</FONT>
<BR><FONT SIZE=2>> x.Quit</FONT>
<BR><FONT SIZE=2>> Set x = Nothing</FONT>
<BR><FONT SIZE=2>> End Sub</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> If I were you, I would look for Dim statements beginning with the word</FONT>
<BR><FONT SIZE=2>> 'Excel.' as varaiables pointing to Workbook, Sheet, Range, Cell or other</FONT>
<BR><FONT SIZE=2>> Excel objects (or late bound objects that point to Excel objects) that are</FONT>
<BR><FONT SIZE=2>> not properly destroyed will prevent Excel from closing. If you have no</FONT>
<BR><FONT SIZE=2>such</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> object variables, I would explicitly create at least Workbook and</FONT>
<BR><FONT SIZE=2>WorkSheet</FONT>
<BR><FONT SIZE=2>> objects and destroy those, in reverse order of their creation.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> Hen</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> >From: "Hale, Jim" <jim.hale@fleetpride.com></FONT>
<BR><FONT SIZE=2>> >Reply-To: Access Developers discussion and problem</FONT>
<BR><FONT SIZE=2>> >solving<accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>> >To: "'Accessd (E-mail)" <accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>> >Subject: [AccessD] Excel zombie won't die</FONT>
<BR><FONT SIZE=2>> >Date: Fri, 29 Aug 2003 11:39:17 -0500</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> > I have an instance of Excel that I can't seem to kill except through</FONT>
<BR><FONT SIZE=2>the</FONT>
<BR><FONT SIZE=2>> >task manager or shutting down Access. I can make the instance visible and</FONT>
<BR><FONT SIZE=2>> >try to shut it down using exit off the file menu but it won't shut down</FONT>
<BR><FONT SIZE=2>> >that</FONT>
<BR><FONT SIZE=2>> >way either. The code executes without error. The code is below. What am I</FONT>
<BR><FONT SIZE=2>> >missing? TIA</FONT>
<BR><FONT SIZE=2>> >Jim Hale</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >Public Function Load_History(intDept As Integer, strDeptname As String, _</FONT>
<BR><FONT SIZE=2>> >intCo As Integer, strRegioncode As String, strRegionName As String, _</FONT>
<BR><FONT SIZE=2>> >strAreacode As String, strDept2 As String, strPlGroup As String) As</FONT>
<BR><FONT SIZE=2>Boolean</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >dim appExcel As Excel.Application</FONT>
<BR><FONT SIZE=2>> >Set appExcel = Excel.Application</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >........... tons of astounding code, yada,yada,yada</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >If strPW <> "none" Then 'set password</FONT>
<BR><FONT SIZE=2>> > For i = 1 To .Sheets.Count</FONT>
<BR><FONT SIZE=2>> > .Worksheets(i).Protect Password:=strPW</FONT>
<BR><FONT SIZE=2>> > Next i</FONT>
<BR><FONT SIZE=2>> > .Sheets("instruc").Unprotect Password:=strPW</FONT>
<BR><FONT SIZE=2>> > .Sheets("assumptions").Unprotect Password:=strPW</FONT>
<BR><FONT SIZE=2>> > End If</FONT>
<BR><FONT SIZE=2>> > .ActiveSheet.Protect DrawingObjects:=True, Contents:=True,</FONT>
<BR><FONT SIZE=2>> >Scenarios:=True</FONT>
<BR><FONT SIZE=2>> > .ActiveWorkbook.Protect Structure:=True, Windows:=False,</FONT>
<BR><FONT SIZE=2>> >Password:=strPWworkbook</FONT>
<BR><FONT SIZE=2>> > .ActiveWorkbook.SaveAs FileName:=strFileSave</FONT>
<BR><FONT SIZE=2>> > .ActiveWorkbook.Close SaveChanges:=True</FONT>
<BR><FONT SIZE=2>> > End With</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> > Load_History = True</FONT>
<BR><FONT SIZE=2>> >The_End:</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> > appExcel.Quit</FONT>
<BR><FONT SIZE=2>> > Set appExcel = Nothing</FONT>
<BR><FONT SIZE=2>> >dbs2.Close</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >Exit Function</FONT>
<BR><FONT SIZE=2>> ></FONT>
<BR><FONT SIZE=2>> >Err_CreatePlan:</FONT>
<BR><FONT SIZE=2>> >Load_History = False</FONT>
<BR><FONT SIZE=2>> > MsgBox Err.Description</FONT>
<BR><FONT SIZE=2>> > Resume The_End</FONT>
<BR><FONT SIZE=2>> >End Function</FONT>
<BR><FONT SIZE=2>> >_______________________________________________</FONT>
<BR><FONT SIZE=2>> >AccessD mailing list</FONT>
<BR><FONT SIZE=2>> >AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>> ><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>> >Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> _________________________________________________________________</FONT>
<BR><FONT SIZE=2>> Add photos to your messages with MSN 8. Get 2 months FREE*.</FONT>
<BR><FONT SIZE=2>> <A HREF="http://join.msn.com/?page=features/featuredemail" TARGET="_blank">http://join.msn.com/?page=features/featuredemail</A></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> _______________________________________________</FONT>
<BR><FONT SIZE=2>> AccessD mailing list</FONT>
<BR><FONT SIZE=2>> AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>> <A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>> Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>></FONT>
</P>
<BR>
<P><FONT SIZE=2>----------------------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>----</FONT>
</P>
<BR>
<P><FONT SIZE=2>> _______________________________________________</FONT>
<BR><FONT SIZE=2>> AccessD mailing list</FONT>
<BR><FONT SIZE=2>> AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>> <A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>> Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>></FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>