This time-saving macro will let you select a folder path and loop through each of the Excel files matching the file filter (default setting *.xl*) in that folder. The VBA procedure:

• Opens a workbook from a list of files
• Performs a task automatically (add your code...)
• Saves and closes the workbook

Option Explicit

Sub LoopAllExcelFilesInFolder()

    'To loop through all Excel files in a user selected folder and perform a set task on them
    'http://www.spreadsheet1.com/list-filenames-and-their-properties-in-an-excel-table.html
    
    Const cFileFilter As String = "*.xl*"
    
    Dim oWB As Workbook
    Dim xlOrigCalcMode As XlCalculation
    Dim sTargetPath As String
    Dim sTargetFile As String
    Dim C As Long
    
    On Error GoTo ErrorHandler_

    'Optimize macro speed - http://spreadsheet1.com/vba-development-best-practices.html
    'Do not disable Application Events, as events won't fire when workbooks are opened
     Application.ScreenUpdating = False
     xlOrigCalcMode = Application.Calculation
        
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Target Folder"
        .AllowMultiSelect = False
        If .Show Then
            sTargetPath = .SelectedItems(1) & "\"
        Else
            MsgBox "File selection cancelled!", vbCritical
            GoTo ExitSub_
        End If
    End With
    
    'http://www.spreadsheet1.com/syntax-and-usage-navigation-add-in-for-excel-2013-functions.html
    'https://msdn.microsoft.com/en-us/library/office/gg278779.aspx
    sTargetFile = Dir(sTargetPath & cFileFilter, vbNormal + vbReadOnly)
    
    'Loop through each Excel file in folder
    Do While sTargetFile <> vbNullString
        
        Set oWB = Workbooks.Open(sTargetPath & sTargetFile)
        C = C + 1
        
        '------------------------------------------------------------------
        'Make changes in open workbook here - Recalculation may be required
        '------------------------------------------------------------------
                
        oWB.Close SaveChanges:=True
        DoEvents
        sTargetFile = Dir 'Get next filename
    Loop
        
    MsgBox "Files opened: " & C, vbInformation
ExitSub_:
    'Reset macro optimization settings
    Application.ScreenUpdating = True
    Application.Calculation = xlOrigCalcMode
Exit Sub

ErrorHandler_:
    'Error if file is password protected, corrupted or missing, changes made to protected sheets etc
    MsgBox "Error in file: " & Err.Number & " " & Err.Description & vbCrLf & vbCrLf & sTargetFile, vbCritical
End Sub

Browser File Tip

Here is a quick way to get a list of files from a folder and put them into Excel:

In Chrome, type (drive letter) C:// in the Chrome navigation bar. Chrome will behave as a Window Explorer! 

• Navigate to any folder in your disc
• Highlight filenames required, then press CTRL-C to copy the list to clipboard
• Go to Excel and Paste Special as Values. Filenames, size and date modified will be copied to an Excel range. Finally, autofit column width.
  

---

What if you want to loop through only certain workbooks in a folder or in an entire disc as opposed to all of the files in a folder? You may need to create a list of files in Excel and then use the full path names to do whatever you want to achieve with these files.

There are several ways to create a list of files in a folder and its sub-directories. The most commonly used object based model by Excel VBA developers to access the computer's file system is the FileSystemObject (FSO), which is available inside the Microsoft Scripting Runtime library. 

However, our FREE File List VBA add-in is harnessing the power of Windows PowerShell, a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework.

---

Create a file list using a filename and/or extension search

---

To list files associated with Office or with a particular Office application, simply tick the 'Application' checkbox and select the application desired. The add-in will search for all file extensions associated with the program automatically.

Search for files of any Office application without entering extensions

---

All output columns are checked by default. You can unselect any file information that is not needed in the file report. The 'filename with path' is the default information that cannot be removed. The file list Excel Table will be added in a new workbook.

File List output column selection

Information listed for each file found that matches the search criteria

---

Treemap Chart For Large Files In Excel 2016

The Treemap chart type was first introduced in Office 2016 and provides a hierarchical view of data in a way that patterns can be easily spotted. Here is how to visualize large files in your disc with a treemap chart:

• List files in your disc using output columns: File name and length
• Sort results using column C (file length) from largest to smallest
• Select rows in columns B & C up-to file size e.g. 30MB
• Click Insert tab, then Treemap chart

---

download

Download

Click button to download the FREE File List add-in for Microsoft Office Excel 2007 or later

VBA Add-in powered by the Ribbon Commander Framework

---

comments powered by Disqus

---

Home  »   Free Add-ins  »   List Files in an Excel range