[AccessD] copying worksheets

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