By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

Excel workbook copy to existing workbook

P: 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
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
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

P: 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
Expert 2.5K+
P: 2,653
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.