XL2007 problem with Macro

[scoha]

I have a great macro which gets all the file names from a nominated directory and after I recently upgraded to 2007 it doesnt work - returns a Object Doesnt Support This Action error.

Here is my code:

Sub ListJpgFiles()
    ' This macro lists all .jpg files
    ' in a certain directory to a column in Excel
    Range("A1").Value = "FileName"
    NextRow = 2
    ' Use the FileSearch Object
    With Application.FileSearch
        .NewSearch
        ' Customize the following line
        .LookIn = "G:\Facilities\Towers\TwrPics\ "
        .SearchSubFolders = True
        .Filename = "*.jpg"
        .Execute
        FilesToProcess = .FoundFiles.Count
        ' loop through each workbook in the directory
        For i = 1 To .FoundFiles.Count
            ThisEntry = .FoundFiles(i)
            ' Find to path portion. Start looking from the end
            ' of This Entry for a backslash
            For j = Len(ThisEntry) To 1 Step -1
                If Mid(ThisEntry, j, 1) = Application.PathSeparator Then
                    Cells(NextRow, 1) = Mid(ThisEntry, j + 1)
                    Exit For
                End If
            Next j
            NextRow = NextRow + 1
        Next i
    End With
End Sub

Must be something to do with the File Search object??

 

[Smitty]

You got it.

The FileSearch object was inadvertently left out of Excel 2007.

See: http://www.codematic.net/excel-tools/office-2007-filesearch.htm

HTH

 

[scoha]

Thanks Smitty - what a shame

 

[Smitty]

Thanks Smitty - what a shame

Microsoft said they will put it back in the next version. It really was an oversight, but at least there are viable workarounds.

 

[stickboy2006]

this works for me in 2007



Sub Showfiles()

Dim fs, SEARCHAREA, ITEM, RESULTS
Set fs = CreateObject("Scripting.fileSystemObject")
Set SEARCHAREA = fs.Getfolder("c:\test")
Set RESULTS = SEARCHAREA.Files

For Each ITEM In RESULTS
Range("a" & Range("a5000").End(xlUp).Row + 1).Value = ITEM.Name
Next

End Sub

hope this helps.