[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




On 9/12/06, MartyConnelly <martyconnelly at shaw.ca> wrote:
>
> Let's see the intermediate code.
>
> Billy Pang wrote:
>
> >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
> >>>
> >>>
> >>>
> >>>
> >>>
> >>--
> >>Marty Connelly
> >>Victoria, B.C.
> >>Canada
> >>
> >>--
> >>AccessD mailing list
> >>AccessD at databaseadvisors.com
> >>http://databaseadvisors.com/mailman/listinfo/accessd
> >>Website: http://www.databaseadvisors.com
> >>
> >>
> >>
> >
> >
> >
> >
> >
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
> --
> 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