Using VB to create multiple worksheets

[bbrendan]

Hi,

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 another site.(

http://www.exceltip.com/)

It seems to work fine if 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 time 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 the workbooks
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks '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).Copy after:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
basebook.Close
Next i
End If

End With
Application.ScreenUpdating = True
---------------------------------------------------------------------

thank you
brendan

 

[bjd4jc]

You need to use your top-level Application Object (it's not built into VB like it is excel) to access your objects. You can not say

Application.ScreenUpdating

In Your instance it would be

oExcel.ScreenUpdating
With oExcel.FileSearch