Looping Through Workbooks in Folder

[millhouse123]

I got this code from Excel help and it doesn't seem to be working and I am not sure why.

I get an erro on Application.FileSearch that "Object doesn't support action"

Any ideas?

Thanks

With Application.FileSearch
        .NewSearch
        .LookIn = Currentpath
        .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)
                 'ActiveWorkbook.Range("D153:D223").Copy
                 'X = X + 2
                 'CodeWB.Worksheet("Scoring Calc").Cells(16, X + 2).Paste
                 'wbResults.Close SaveChanges:=False
                 Next lCount
            End If
    End With

 

[Andrew Poulsom]

Are you using Excel 2007? If so FileSearch has been removed. You will need to use the Dir function or the FileSystemObject:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6/

 

[millhouse123]

Yes, I am using 2007. I am guessing their is some alternative now?

Thanks

 

[Andrew Poulsom]

Yes, I am using 2007. I am guessing their is some alternative now?

Thanks

Yes, I gave you the alternatives in my original reply.

 

[millhouse123]

I am sorry I didn't see the link the first time I looked. In the mean time I had found this link through google. Reguardless the solutions are a little above my skill. I was looking at the solution that added two class modules but I really don't understand how they work. I added two class modules and then replaced the application.fileserach line with New FileSearch but I get some kind of error. I guess I don't know enough about vba yet do figure it out on my own so any help would be awsome.

 

[Andrew Poulsom]

Here is an example using Dir:

http://excel2007tips.blogspot.com/2007/05/no-applicationfilesearch-in-2007.html

 

[millhouse123]

Thanks for the link, I was wondering how do I add a format? The current format uses .xlsx but I would also like to look for .xls in the same loop.

 

[Andrew Poulsom]

Maybe look for xls*.