Copying excel sheet to another through access

[zrazzaq]

Hi:
I have the following code:

Public Function CopySheet()
    Dim xlApp As New Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlwbA As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("U:\Databases\Sales.xls")
Set xlwbA = xlApp.Workbooks.Open("U:\Databases\Book1.xls")
 xlApp.Visible = True
xlWB.Sheets("Dly Chart").Select
xlWB.Sheets("Dly Chart").Copy Before:=xlwbA("Book1").Sheets("Summary")

    
    Set xlApp = Nothing
    Set xlWB = Nothing
     Set xlwbA = Nothing
     'Release database object from memory
    Set dBase = Nothing

End Function

Now both of my excel workbook open, and the Dly Chart is selected from the first excel workbook. Now how do I copy that sheet into the other workbook before the summary tab in the excel workbook. That is where I am stuck.
Thanks
Zishan

 

[CautionMP]

zrazzaq,
Here is a thought.

[tt]xlWB.Worksheets("Dly Chart").Copy xlwbA.Worksheets("Summary")[/tt]

When you reference [tt]xlwbA[/tt] you don't need to state [tt]("Book1")[/tt] since it already points to that workbook because of your [tt]Set[/tt] statement.

I also used [tt]Worksheets[/tt] instead of [tt]Sheets[/tt], they both work but IMHO [tt]Worksheets[/tt] is safer.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)