[AccessD] copying worksheets

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.