469,282 Members | 1,842 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Excel workbook copy to existing workbook

2
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("Excel.Application")
Set mysheet = xlApp.Workbooks.Open("C:\FileA.xls").Sheets(1)
Set sheet = xlApp.ActiveWorkbook.Sheets(1)
For X = 1 To xlApp.ActiveWorkbook.Sheets.Count
'Loop through each of the sheets in the workbook
xlApp.ActiveWorkbook.Sheets(X).Copy _
Before:=xlApp.Workbooks("C:\FileMaster.xls").Sheet s(1)
Next

FileName = "C:\NewFile.xls"
mysheet.Application.ActiveWorkbook.SaveAs FileName

End Sub
Mar 4 '08 #1
3 3476
FishVal
2,653 Expert 2GB
Hi, there.

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

Regards,
Fish
Mar 4 '08 #2
samj
2
Hi, there.

I guess your have two different instances of Excel.Application so you cannot refer to Workbooks collection via the same Excel.Application 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?
Mar 5 '08 #3
FishVal
2,653 Expert 2GB
In order to copy to Workbooks("C:\FileMaster.xls") you need to open it just as "C:FileA.xls".
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.
Mar 6 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Sam | last post: by
18 posts views Thread by John Bailo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.