VBA - Application.Filesearch

[Nelson79]

Hi

I created the below code in Excfel 2003 but have recently upgraded to Excel 2007 and it no longer works. Looking on Google the error seems to be Application.Filesearch which is no longer avaialble in 2007. Any help adjusting the below code to suit 2007 would be much appreciated. Thanks:


Public Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
strCodeBook = wbCodeBook.Name
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "K:\BIG EUROPE ART\SALES\2011\RFP DATA\Cover Sheets"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xlsx"

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

strFileName = wbResults.Name

Call ImportData

wbResults.Close SaveChanges:=True

Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Sub ImportData()
''written by rmcniven,maplecourt
''declare variables for data to import
Dim strAccount As String
Dim strAcManager As String
Dim dtDeadline As Date
Dim dtReceived As Date
Dim strSpecial As String
Dim strLRA As String
Dim strCommissionable As String
Dim strCXL As String
Dim strSTDRoomsOnly As String
Dim strAllowSquatters As String
Dim strNumberProperties As String
Dim str3rdPartysite As String
Dim strsite As String
Dim a As Integer
''assign data in cover sheet to variables
Windows(strFileName).Activate
With Sheets("RFPC Instruction Page")
strAccount = .Cells(5, 4).Value
strAcManager = .Cells(3, 4).Value
dtDeadline = .Cells(9, 4).Value
dtReceived = .Cells(3, 11).Value
strSpecial = .Cells(65, 3).Value
strLRA = .Cells(13, 4).Value
strCXL = .Cells(14, 4).Value
strCommissionable = .Cells(15, 4).Value
strSTDRoomsOnly = .Cells(16, 4).Value
strAllowSquatters = .Cells(60, 4).Value
strNumberProperties = .Cells(11, 4).Value
str3rdPartysite = .Cells(33, 8).Value
strsite = .Cells(36, 8).Value


End With
''dump data into corresponding cells in consolidation workbook(this one)
wbCodeBook.Activate
Cells(3, 1).Select
For a = 1 To 10000
If Cells(3 + (a - 1), 1).Value = "" Then
Exit For
End If
Next a
Cells(3 + (a - 1), 1).Value = strAccount
Cells(3 + (a - 1), 2).Value = strAcManager
Cells(3 + (a - 1), 4).Value = dtDeadline
Cells(3 + (a - 1), 5).Value = dtReceived
Cells(3 + (a - 1), 7).Value = strSpecial
Cells(3 + (a - 1), 9).Value = strLRA
Cells(3 + (a - 1), 10).Value = strCommissionable
Cells(3 + (a - 1), 11).Value = strCXL
Cells(3 + (a - 1), 12).Value = strSTDRoomsOnly
Cells(3 + (a - 1), 15).Value = strAllowSquatters
Cells(3 + (a - 1), 16).Value = strNumberProperties
Cells(3 + (a - 1), 17).Value = str3rdPartysite
Cells(3 + (a - 1), 18).Value = strsite

End Sub