ALL YOUR EXCEL NEEDS

FREE Excel STUFF

Excel Newsletter

Advanced Search

PRODUCTS

Development

Contact Us

Loop Through a Folder of Excel Workbooks

Excel VBA: Loop Through a Folder of Excel Workbooks With the FileSearch Method

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

See Also: Loop Through Worksheets | Create a Hyperlinked List of Excel Workbook

Loop Through a Folder of Excel Workbooks

 

With the use of Excel VBA Macro code we can loop through a Folder on ones Hard-drive and work on all Excel Workbooks within it. The code below can be used on any version of Excel, 2000 or higher. The only changes needed are to the file paths used. The part of the code that reads: .Filename = "Book*.xls" has been commented out but can be uncommented if you wish to restrict the list to that of specific Excel Workbook name. Note also the code 'as is' restricts the list to only Excel Workbooks. However, this can be any of the File types listed below;

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



        '.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.Close SaveChanges:=True



             



                 Next lCount



            End If



    End With



    



    On Error GoTo 0



    Application.ScreenUpdating = True



    Application.DisplayAlerts = True



    Application.EnableEvents = True



End Sub

MsoFileType can be one of these MsoFileType constants

msoFileTypeAllFiles
msoFileTypeBinders
msoFileTypeCalendarItem
msoFileTypeContactItem
msoFileTypeCustom
msoFileTypeDatabases
msoFileTypeDataConnectionFiles
msoFileTypeDesignerFiles
msoFileTypeDocumentImagingFiles
msoFileTypeExcelWorkbooks
msoFileTypeJournalItem
msoFileTypeMailItem
msoFileTypeNoteItem
msoFileTypeOfficeFiles
msoFileTypeOutlookItems
msoFileTypePhotoDrawFiles
msoFileTypePowerPointPresentations
msoFileTypeProjectFiles
msoFileTypePublisherFiles
msoFileTypeTaskItem
msoFileTypeTemplates
msoFileTypeVisioFiles
msoFileTypeWebPages
msoFileTypeWordDocumentsSee Also: Create a Hyperlinked List of Excel Workbook

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Search Tips FREE Excel Help

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS