Create New Worksheets in VB
 

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_AllArea s.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
--------------------------------------------------------------------

--
Message posted from http://www.ExcelForum.com

Create New Worksheets in VB
 

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"bbrendan " wrote in message
...
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 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_AllArea s.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
---------------------------------------------------------------------


---
Message posted from http://www.ExcelForum.com/