Excel workbook copy to existing workbook

[samj]

Looking at code examples, it would seem this is simple, but I can't figure it out. From an Access 2000 form command button, I want to copy all worksheets in an existing workbook to an existing workbook and save the results in a new workbook. I keep getting the message "subscript out of range." Any help is greatly appreciated.

Private Sub Copier_Click()
' Copy all sheets in FileA before sheets in FileMaster and save as NewFile

Dim mysheet As Variant
Dim sheet As Variant
Dim FileName As String
Dim X As Integer

Set xlApp = CreateObject("E xcel.Applicatio n")
Set mysheet = xlApp.Workbooks .Open("C:\FileA .xls").Sheets(1 )
Set sheet = xlApp.ActiveWor kbook.Sheets(1)
For X = 1 To xlApp.ActiveWor kbook.Sheets.Co unt
'Loop through each of the sheets in the workbook
xlApp.ActiveWor kbook.Sheets(X) .Copy _
Before:=xlApp.W orkbooks("C:\Fi leMaster.xls"). Sheets(1)
Next

FileName = "C:\NewFile.xls "
mysheet.Applica tion.ActiveWork book.SaveAs FileName

End Sub

[FishVal]

Hi, there.

I guess your have two different instances of Excel.Applicati on so you cannot refer to Workbooks collection via the same Excel.Applicati on object.

Regards,
Fish

[samj]

Hi, there.

I guess your have two different instances of Excel.Applicati on so you cannot refer to Workbooks collection via the same Excel.Applicati on object.

Regards,
Fish

I've checked task manager, and only one instance is running. I'm new to Excel automation, so figure I've got something wrong in syntax. Code works fine to create new workbook from just FileA, but problem occurs when trying to combine with another existing workbook (FileMaster). Any thoughts?

[FishVal]

In order to copy to Workbooks("C:\F ileMaster.xls") you need to open it just as "C:FileA.xl s".
Additionally I'd like to say that your code is somewhat unstraight and hazy. Not clear why do you reference worksheets by variables sheet and mysheet, and, at the same time you don't have references to source and destination workbooks, which would be more useful of course. And so on, and so on.