Application.FileSearch and Excel 2007 problem

[Jungleman]

Hello everyone,

I was hoping someone could help me with I problem i have.. I am no expert on VBA, my knowledge is very basic, but I have managed to use some examples i found in the Excel help to make some macros that really help me at work. The thing is that when they changed our office package from 2003 to 2007 these macros stopped working. I've searched the web up and down with no luck to find a substitute for the code that's been giving me trouble. The problem lies with Application.FileSearch that, according to what i've read on many forums, is no longer supported on the 2007 version.

I had two macros, one of them opened excel files starting with the name "F1_*.xls" on a given directory, updated, saved and closed, then went to the next file and did the same thing untill there were no more, this was the code....

Sub ACTUALIZAR()

Set fs = Application.FileSearch
With fs
.LookIn = "F:\share\Control Financiero\Actividades\F1\2007 2008"
.Filename = "F1_*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i + 0, 1) = .FoundFiles(i)
Workbooks.Open Filename:=Cells(i + 0, 1), _
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
Next i
Else
End If
End With
End Sub

The other macro had the same code as the first but (without the opening, updating and saving of files) just listed all excel files on a worksheet starting with the name "F1_*.xls" along with their path. This was the code...

Sub ACTUALIZAR()

Set fs = Application.FileSearch
With fs
.LookIn = "F:\share\Control Financiero\Actividades\F1\2007 2008"
.Filename = "F1_*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i + 0, 1) = .FoundFiles(i)
Next i
Else
End If
End With
End Sub

I would be very very grateful if someone could help me replicate this proceedure on Excel 2007..
Thank you all...

[Bob Phillips]

FileSearch has been dropped in 2007



Dim fs As String
Dim fsFile As String
Dim i As Long

fs = "C:\test" '"F:\share\Control Financiero\Actividades\F1\2007 2008"
If Dir(fs, vbDirectory) <> "" Then

fsFile = Dir(fs & "\*.xls")
Do While fsFile <> ""
i = i + 1
Cells(i, 1).Value = fsFile
fsFile = Dir
Loop
End If

[Jungleman]

Thank you so much!!