Summary workbook made up of combination of workbooks (1 Viewer)

[Martyh]

Hi !

I have several workbooks each with one sheet (located in the directory /AA ) and I want to put all the worksheets from them into one summary workbook. (located in the directory /CR ) The cavaet is that I am doing this from VBA code in Access.

My code lines are:

Dim ExcelApp As Excel.Application
Dim ExcelApp2 As Excel.Application
Dim WkBk As Excel.Workbook
Dim Wkb As Excel.Workbook
Dim WS As Worksheet

Dim txtReferenceNumber As String

Dim strPath As String
Dim Path As String
Dim FileName As String


Set ExcelApp = CreateObject("Excel.Application") ' for the summary SS
Set ExcelApp2 = CreateObject("Excel.Application") ' for the many SS
strPath = "C:\Finance Database\CR\" ' for the summary SS
txtReferenceNumber = Format(modGlobalVariables.intFiscalYear, "####")
'how I am combining all the workbooks with the FYr at the begining
FileCopy strPath & "BudgetTemplateCR.xls", _
strPath & txtReferenceNumber & "TotalBudget.xls"
'copy the template file (which really just contains formatting)
Set WkBk = ExcelApp.Workbooks.Open( _
FileName:=strPath & txtReferenceNumber & "TotalBudget.xls")
'openning the summary wb

Path = "C:\Finance Database\AA\" 'Change as needed
'for the individual wb's each containing 1 sheet

FileName = Dir(Path & txtReferenceNumber & "*.xls", vbNormal)
' only the ones with the Current FYr at the beginning
Do Until FileName = ""
Set Wkb = ExcelApp2.Workbooks.Open( _
FileName:=Path & "\" & FileName)
'open the indivdual wb

For Each WS In Wkb.Worksheets
WS.Copy After:=WkBk.Sheets(WkBk.Sheets.Count)
'this is the statement i'm having problems with
Next WS
Wkb.Close False
FileName = Dir()
Loop ' Next .xls File


I get the error "Method 'Copy' of object '_Worksheet' failed. What am I doing wrong?

Thanks for taking the time,
Marty