excel 2007

[Guest]

How to change the following macro in order to work in excel 2007:
Sub SearchFiles()

With Application.FileSearch
.NewSearch
.LookIn = "F:\My Documents\"
.SearchSubFolders = True
.Filename = strFileName & ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If LCase(Right(.FoundFiles(i), Len(strFileName & ".xls") + 1)) =
"\" & LCase(strFileName & ".xls") Then
Workbooks.Open .FoundFiles(i), UpdateLinks:=3
Exit For
End If
Next i
Else
'MsgBox "There were no files found."
End If
End With

End Sub


Regards, Pedro

 

[Jim Cone]

Pedro,
XL 2007 was "improved" by removing FileSearch.
Try using the Dir function.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Pedro" <[email protected]>
wrote in message
How to change the following macro in order to work in excel 2007:
Sub SearchFiles()

With Application.FileSearch
.NewSearch
.LookIn = "F:\My Documents\"
.SearchSubFolders = True
.Filename = strFileName & ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If LCase(Right(.FoundFiles(i), Len(strFileName & ".xls") + 1)) =
"\" & LCase(strFileName & ".xls") Then
Workbooks.Open .FoundFiles(i), UpdateLinks:=3
Exit For
End If
Next i
Else
'MsgBox "There were no files found."
End If
End With

End Sub


Regards, Pedro