Error from Excel 2003 to 2007

[YeshyTiger]

Hello,

My company had been using 2003 when I created a very large macro looped through files checking each one to a certain criteria. Now that we have upgraded to 2007, it does not recognize at least the first part of the macro that sets up the loop. Could anyone tell me why it is not recognizing the loop when it used to in 2003?

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim error As Integer
Dim psswrd As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wbCodeBook = ThisWorkbook
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\Documents and Settings\JG\Desktop\PMP Input Folder"
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "*.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(.FoundFiles(lCount))
                 
                .Select

 

[VoG]

It is because Application.Filesearch no longer exists in XL 2007.

If you do a search of the board you'll find several workarounds, for example http://www.mrexcel.com/forum/showthread.php?t=367639

 

[YeshyTiger]

Hmmm, I'm trying to understand the post in the link you gave me. Here is the template code I have now for just the loop:

[B]Sub RunCodeOnAllXLSFiles()[/B]

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

        '.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)



                [B] 'DO YOUR CODE HERE[/B]


                 wbResults.Close SaveChanges:=True

                 Next lCount

            End If

    End With

    On Error GoTo 0

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    Application.EnableEvents = True


[B]End Sub[/B]

And what it should become is?

Dim myDir As String, fn As String
myDir = strPath & "\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
    'Do Code Here
Loop