‘**************************************************************
‘* Searches a directory with Excel 2007 vba
‘* Needed because Microsoft disabled FileSearch function
‘*
‘***************************************************************
Sub File_Search()

Dim Coll_Docs As New Collection
Dim Search_path, Search_Filter, Search_Fullname As String
Dim DocName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Long

Search_path = “C:\myPath”   ‘ where ?
Search_Filter = “*.xlsx”            ‘ what ?
Set Coll_Docs = Nothing

DocName = dir(Search_path & “\” & Search_Filter)
Do Until DocName = “”            ‘ build the collection
Coll_Docs.Add Item:=DocName
DocName = dir
Loop

‘MsgBox “There were ” & Coll_Docs.Count & ” file(s) found.”
r = 1
For i = Coll_Docs.Count To 1 Step -1              ‘
Search_Fullname = Search_path & “\” & Coll_Docs(i)
‘ Sheets(”List”).Cells(r, 1) = Coll_Docs(i)
Call changeFormats(Search_path, Coll_Docs(i))
‘ r = r + 1
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

‘**************************************************************
‘* Changes format from excel 2007 to 2003
‘* Needed to do this so that Java Excel Libraries worked properly
‘*
‘***************************************************************

Sub changeFormats(ByVal dir As String, ByVal fileName As String)

Workbooks.Open fileName:= _
dir & fileName

ActiveWorkbook.SaveAs fileName:= _
dir & Replace(fileName, “xlsx”, “xls”) _
, FileFormat:=xlExcel8

ActiveWindow.Close

End Sub