Getting error on .FoundFiles.count in file search

Question

I am trying to use the following code (copied from this site). But when I get to .FoundFiles.count, I get an error "Object variable or With block variable not set." I have searched for hours for a solution to this problem. I wonder if someone could explain. Thanks.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                        'DO YOUR CODE HERE
                         wbResults.Worksheets("Sheet1").Range("B7").NumberFormat = "YYYY.MM.DD"
                        wbResults.Close SaveChanges:=True
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

what version of XL are you using? `Application.FileSearch` is not supported in 2007 and above. — Scott Holtzman, Apr 22 '13 at 18:17
yup. see the answer posted below. Try the `Dir` function. Combine that with some logic and you will get what you are after. — Scott Holtzman, Apr 22 '13 at 18:29
http://stackoverflow.com/questions/15339006/file-searching-in-vba — Siddharth Rout, Apr 22 '13 at 19:22

score 1 · Answer 1 · answered Apr 22 '13 at 18:19

1

The Application.FileSearch has apparently been deprecated in Excel 2007; a quick search yielded a few alternatives, such as this one. A bit more digging will turn up other solutions too.

answered Apr 22 '13 at 18:19

Geoff

8,551
1
43
50

+ 1 yup it is not available from xl2007+ – Siddharth Rout Apr 22 '13 at 19:22

Getting error on .FoundFiles.count in file search

1 Answers1