Managing files in macro built for Xls 2000 not working in 2007 just a tip needed

[metatron]

Would really appreciate your help.

I have this macro built on excel 2000 which has to be updated for 2007 and doesnt work.

Basically I have to collect information from 1000 other excell files and place it in one workshhet.

Sub lucas()
Range("A1").Select

Dim FS
Dim fichero
Dim i
Dim dato1


Set FS = Application.FileSearch
With FS
.LookIn = "F:\planes"
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
' MsgBox "Se encontraron " & .FoundFiles.Count & " archivo(s)."
Dim AÑO As Integer
AÑO = InputBox("Por favor, indique el año para el que desea totales.", "Calculo de Totales")
Worksheets(1).Range("a2:k10000").ClearContents
For i = 1 To .FoundFiles.Count

ActiveCell.Offset(i, 0) = .FoundFiles(i)
fichero = .FoundFiles(i)
Workbooks.Open (fichero)
Dim datos As Integer

dato1 = WorksheetFunction.VLookup(AÑO, Worksheets(1).Range("a5:u100"), 1, False)


ActiveWorkbook.Close
ActiveCell.Offset(i, 1) = dato1

Next i


SearchOrder:=xlByColumns, MatchCase:=True

Else
MsgBox "No se encontró ningún archivo."
End If
End With





Noticed that some commands have changed in this version, and have been able to make an initial change searching for files with:


' Find and count files
StrPath = "F:\PLANES\"
strFile = Dir(StrPath & "*.XLS")
Do While strFile <> ""
i = i + 1
Debug.Print strFile
strFile = Dir
Loop
MsgBox "Total files found: " & i

But reaching to the For i = 1 To .FoundFiles.Count I get an error and cant find the solution to work with each file.


Obviously Im not an epert.

Many thanks in advance!

 

[MARK858]

You need more than a tip I am afraid as Application.Filesearch was done away with in Excel 2007.
See if the 2 links below help, afraid I can't post more help as just going out.

http://www.mrexcel.com/forum/excel-questions/643288-excel-2010-visual-basic-applications-replacement-application-filesearch.html

http://www.mrexcel.com/forum/excel-questions/633760-sos-application-filesearch-excel-2007-a.html

 

[metatron]

Thanlks a lot mark!
Will double check in these threads.

Rgds