If any one has a work around for Application.File Search in Excel 2007 please let me know.

This is currently not working in Office 2007.



Re: Application.File Search.

Jon Peltier

Here is a procedure I originally copied from a forum post by Bill Manville. It starts in the directory D:\TEMP, but you can change that, the procedure uses Dir recursively to put the files into an array for further manipulation.

Dim aFiles() As String, iFile As Integer

Sub DoIt()
 iFile = 0
 ListFilesInDirectory "D:\TEMP\" ' change the top level as you wish
 MsgBox iFile & " files found"
End Sub
Sub ListFilesInDirectory(Directory As String)
 Dim aDirs() As String, iDir As Integer, stFile As String
 ' use Dir function to find files and directories in Directory
 ' look for directories and build a separate array of them
 ' note that Dir returns files as well as directories when vbDirectory specified
 iDir = 0
 stFile = Directory & Dir(Directory & "*.*", vbDirectory)
 Do While stFile <> Directory
  If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
   ' do nothing - GetAttr doesn't like these directories
  ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then
   ' add to local array of directories
   iDir = iDir + 1
   ReDim Preserve aDirs(1 To iDir)
   aDirs(iDir) = stFile
   ' add to global array of files
   iFile = iFile + 1
   ReDim Preserve aFiles(1 To iFile)
   aFiles(iFile) = stFile
  End If
  stFile = Directory & Dir()
 ' now, for any directories in aDirs call self recursively
 If iDir > 0 Then
  For iDir = 1 To UBound(aDirs)
   ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
  Next iDir
 End If
End Sub
- Jon
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions