[AccessD] copying worksheets

err... kryptonite, not kryponite.

On 9/13/06, Billy Pang <tuxedoman888 at gmail.com> wrote:
>
>  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
>



-- 
Billy Pang
http://dbnotes.blogspot.com/
"Once the game is over, the King and the pawn go back in the same box." -
Italian proverb