Application.FileSearch - converting to Excel 2007

[poiu]

Hello,

I've googled this and gone through the relevent threads but can't get my head around the fix for this. Please could you tell me how to go about converting the below code from Excel 2003 to Excel 2007 (it's the problem of the Application.FileSearch function being deprecated).

I've inherited the code but basically it goes into a folder open each file in turn and collects the comments from a "comments" tab and puts into a master file.

Thanks for your help,

Poiu

Sub Test()
With Application.FileSearch
.NewSearch
.LookIn = "\\bnls614f\bnlsangroup2\FolderName\P" & Range("Period") & "\Comments - HR"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute

For I = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(I))

wbs = ActiveWorkbook.Name
' Unprotect the workbook
ActiveWorkbook.Unprotect Password:="Password"

Sheets("Comments").Visible = True
Sheets("MapGICCandCOA (2)").Visible = True
Sheets("Comments").Select

' Take all historic (and current month) comments from the first file,
' and only current month comments from the other files (Prevents
' overload due to the number of comment records)
If (I > 1) Then
Selection.AutoFilter Field:=7, Criteria1:=intPeriod
End If

LastRow1 = Range("A65536").End(xlUp).Row
Range("A1:J" & LastRow1).Select
Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Workbooks("Allcomments - HR function only.xls").Activate
ActiveWorkbook.Worksheets("Comments").Select
LastRow = Range("A65536").End(xlUp).Row
Cells(LastRow + 1, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Workbooks(wbs).Activate
Application.CutCopyMode = False
Workbooks(wbs).Close

' Reprotect the workbook
ActiveWorkbook.Protect Password:="Password"

Next I

End With
End Sub

 

[Trevor G]

You need to look at the DIR Command there are plenty of examples here in the forum.