[AccessD] copying worksheets

Thanks A.D.  My original code did included the activesheet reference (only
because it was easier to write) so I rewrote the code but it appears that
excel application still refuses to die.

I have also noticed that this phenomenon also occurs when I (via automation)
create a new excel application and do nothing but insert a new worksheet
from a second excel template (eg. Sheets.Add Type:="C:\myTemplate.xlt").

Billy



On 9/13/06, A.D.TEJPAL <adtp at hotmail.com> wrote:
>
> Billy,
>
>    Apparently you wish to create a new excel workbook, copy some data into
> the first worksheet and then create a copy of this worksheet, followed by
> positioning of newly copied sheet at the very end. It also seems you wish to
> implement this job in the background, and quit excel in a clean manner.
>
>    For executing jobs with hidden instance of excel, reference to active
> state of objects MUST BE AVOIDED. Otherwise, you might encounter error and
> the excel application might refuse to die, despite using explicit Quit
> command.
>
>    Sample code as given below, carries out the jobs listed in first para,
> in a hidden manner and ensures clean termination of excel application,
> saving the workbook as Test.xls.
>
>    If it is desired that the new workbook becomes available for further
> direct action by the user, statement (A) should be enabled, at the same time
> disabling statement (D). In such a situation, you do have the option of
> using statement (C) with ActiveSheet style, instead of statement (B), if
> desired. However as already stated, (B) is considered the preferred style.
>
> Best wishes,
> A.D.Tejpal
> ---------------
>
> =====================================
> Sub P_NewWbCopySheet()
>    Dim exp As Excel.Application
>    Dim wb As Excel.Workbook
>    Dim ws As Excel.Worksheet
>
>    Set exp = New Excel.Application
>    'exp.Visible = True                  ' (A)
>    Set wb = exp.Workbooks.Add
>
>    Set ws = wb.Worksheets(1)     ' (B)
>    'Set ws = wb.ActiveSheet        ' (C) - Avoid ActiveSheet
>    '                                                         (See Note
> Below)
>    ws.Cells(1, 1) = "ABC"
>
>    ws.Copy After:=wb.Worksheets(wb.Worksheets.Count)
>    Set ws = wb.Worksheets(wb.Worksheets.Count)
>    ws.Name = "Ws_FreshCopy"
>
>    On Error Resume Next
>    Kill "C:\Temp\Test.xls"
>    wb.SaveAs "C:\Temp\Test.xls"
>
>    Set ws = Nothing
>    Set wb = Nothing
>    exp.Quit                                  ' (D)
>    Set exp = Nothing
>
>    ' Note - (a) For background work, where Excel is
>    '                 required to do its job & then quit unseen,
>    '                 use of ActiveSheet object MUST BE
>    '                 AVOIDED.
>    '           (b) If statement (C) is to be used in lieu of (B),
>    '                 statement (A) must be kept enabled.
>    '                 Otherwise, scope for error and the
>    '                 application may refuse to die
>    '                 (despite presence of statement (D))
> End Sub
> =====================================
>
> ----- Original Message -----
> From: Billy Pang
> To: Access Developers discussion and problem solving
> Sent: Wednesday, September 13, 2006 03:10
> Subject: Re: [AccessD] copying worksheets
>
>
> Thanks Marty and may the force be with you.  I tried the different
> copy method but I still get the same result (error message).
> However, I noticed that the excel app still exists after I destroyed it.
> That is...
>
>           Dim xlApp As Excel.Application
>           Set xlApp = CreateObject("Excel.Application")
>
>           some code for adding data to the the worksheet
>           some code for copying the worksheet
>           xlApp.Quit
>           Set xlApp = Nothing
>
> (that is, after stepping through set xlApp = nothing, I can still see
> Excel.exe in my windows task manager)
>
> However, if I remove the code that copies the worksheet, excel.exe is
> properly removed after set xlApp = nothing.
>
> Billy
>
> On 9/12/06, MartyConnelly <martyconnelly at shaw.ca> wrote:
> >
> > Use the Object Browser Luke!
> > There is more than one copy method
> > or it maybe you haven't set the activesheet
> > in your method
> >
> > Sub CopyAndName()
> > ' This creates a new sheet,
> > ' copies the data from the first sheet,
> >
> > Dim sName As String, sht1 As Worksheet, sht2 As Worksheet
> > Set sht1 = ActiveSheet
> > Set sht2 = Worksheets.Add(after:=ActiveSheet)
> > sht2.Name = "MyNewName "
> > sht1.UsedRange.Copy Destination:=sht2.Range("A1")
> > End Sub
> >
> >
> > Billy Pang wrote:
> >
> > >Hello:
> > >
> > >>From Access, I'm trying to make a copy of a worksheet within an
> instance
> > of
> > >Excel that is created at runtime in VBA.
> > >
> > >xlsheet.copy after:=activesheet
> > >
> > >However, I get the following error message.
> > >
> > >Run-time error '5'
> > >
> > >Invalid procedure call or argument
> > >Any ideas?
> > >
> > >Many thanks in advance,
> > >Billy
> --
> 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