Pre 2007 macro needs VBA update for FileSearch

[MikeL]

I used this code quite a bit to go thru all the Workbooks in a Folder and copy paste each WS in those WB to a new WB. FileSearch and FoundFiles.Count no longer work for me.

Please help.

--------------------------------------------------------
Sub Merge()


Dim i As Integer
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim WS As Worksheet

Application.ScreenUpdating = False

Set wbDest = Workbooks.Add


With Application.FileSearch
.NewSearch
' Change Filepath Below To Suit
.LookIn = "T:\data"
.FileType = msoFileTypeExcelWorkbooks
.Execute

For i = 1 To .FoundFiles.Count

Set wbSource = Workbooks.Open(.FoundFiles(i))

For Each WS In wbSource.Worksheets

WS.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)

Next WS

wbSource.Close
Next i
End With
Application.ScreenUpdating = True

End Sub

 

[Joe4]

Take a look here: http://excel2007tips.blogspot.com/2007/05/no-applicationfilesearch-in-2007.html

 

[MikeL]

Thanks Joe4. Its the 'do something part of that link that I am having trouble with. How will

For i = 1 To .FoundFiles.Count
Set wbSource = Workbooks.Open(.FoundFiles(i))

work now?