Create New Worksheets in VB

[bbrendan]

Hi,

I know this is an excel forum, but this is what I would like to do

I have a VB app, which creates multiple workbooks in one folder.
I would like to take each workbook (they ony have one sheet in each
and combine them into one workbook.

So far I have found this code from this site. It seems to work fine i
I use it in excel, but I would like to use it from VB.

i have the references setup, but when it loops through the first tim
it then crashes out.

Any ideas? please


----------------------------------------------------------------
Dim oExcel As Excel.Application
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long

Set oExcel = New Excel.Application

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "c:\xlsdata" ' This is the folder containing th
workbooks
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbook
'msoFileTypeExcelWorkbooks

If .Execute() > 0 Then

Set basebook
oExcel.Workbooks.Open("\c:\xlsdata\Summary_AllAreas.xls")
'ThisWorkbook to hold all sheets
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Cop
after:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
basebook.Close
Next i
End If

End With
Application.ScreenUpdating = True

 

[Dick Kusleika]

bbrendan

The code looks okay to me. What do you mean it crashes out? Do you get an
error, and on which line?

I wonder if Summary_AllAreas.xls is the second workbook found and since it's
already open causes problems with the FileSearch. Just a guess though.
Even if that's not the problem, it seems like you'd need some code in there
to skip your AllAreas workbook anyway.