Billy Pang
tuxedoman888 at gmail.com
Wed Sep 13 14:49:16 CDT 2006
Sweet! that did it! Thanks Jim! Billy On 9/13/06, Hale, Jim <Jim.Hale at fleetpride.com> wrote: > > use xlSheet.Copy after:=xlApp.Worksheets("Sheet1") > This is one of my pet hair pullers- Excel hates to give up being the > center > of attention. Explicit referencing as shown above should solve the > problem. > Jim Hale > > -----Original Message----- > From: Hale, Jim [mailto:Jim.Hale at fleetpride.com] > Sent: Wednesday, September 13, 2006 2:03 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] copying worksheets > > > you need to set xlSheet to nothing as well. I have found that all variable > objects (excel related or not) need to be set to nothing or more often > than > not excel will stay in memory. > Jim Hale > > -----Original Message----- > From: Billy Pang [mailto:tuxedoman888 at gmail.com] > Sent: Wednesday, September 13, 2006 1:53 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] copying worksheets > > > ok.. here is it. what this does is it creates a brand new excel workbook, > write something to sheet2 and then make copy of sheet2. > > what i have noticed is that when copy sheet2 code is there, the xlApp > object > is indestructible! (ie. cannot be destroyed; like superman). you can > still > see that excel.exe still exists in windows task manager after execution. > xlapp survived the set xlApp=nothing onslaught. > > however, if you comment out the copy sheet2 code, xlApp can be destroyed. > the kryponite. > > Public Function fCopyYoWorksheet() > > Dim xlApp As Excel.Application > Dim xlBook As Excel.Workbook > Dim xlSheet As Excel.Worksheet > > Debug.Print "starting..." > Set xlApp = CreateObject("Excel.Application") > Set xlBook = xlApp.Workbooks.Add > Set xlSheet = xlBook.Worksheets("Sheet2") > xlSheet.Range("B2").Value = "Yo! numbers" > xlSheet.Copy after:=Worksheets("Sheet1") > xlBook.Close ' it will prompt you to save > Set xlBook = Nothing > xlApp.Quit > Set xlApp = Nothing > > Debug.Print "finished!" > > End Function > > > > > > *********************************************************************** > 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 > > *********************************************************************** > 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 > -- Billy Pang http://dbnotes.blogspot.com/ "Once the game is over, the King and the pawn go back in the same box." - Italian proverb