Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder

Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder

Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet.

In the first picture, you see several different types of files in the selected folder. The following macro will list only the Excel workbook names, as shown in the second picture.

To modify the macro to list, say, only the PowerPoint files, change ".xls" to ".ppt". If you want to list all files of all types, simply delete or comment out the lines
If InStr(objFile.Name, ".xls") > 0 Then
and the End If statement three lines below that.





Here’s the macro, which works in all version of Excel.

Sub ListExcelFiles()

'Declare and define variables.
Dim SourcePath As String, NextRow As Long
Dim objFSO As Object, objFolder As Object, objFile As Object
SourcePath = "C:\Your\File\Path\"
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Set the folder object associated with your directory path.
'Halt the macro if the directory folder does not exist.
On Error Resume Next
Set objFolder = objFSO.GetFolder(SourcePath)
If Err.Number <> 0 Then
Err.Clear
MsgBox "This path does not exist:" & vbCrLf & _
SourcePath & vbCrLf & _
"Cannot continue.", 16, "No such animal."
Exit Sub
End If

'Define the NextRow variable as row 2.
'Row 1 will contain a header label.
NextRow = 2

'The workbook names will be listed in column A.
'Clear column A to start with a clean column.
Columns(1).Clear

'Place a header label in cell A1.
Range("A1").Value = "Excel files in the path " & SourcePath

'Loop through the Files in the source directory folder
'to list only the Excel file names.
For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") > 0 Then
Cells(NextRow, 1).Value = objFile.Name
NextRow = NextRow + 1
End If
Next objFile

'Release the Objects variables from memory.
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

'For easier readability, autofit column A.
Columns(1).AutoFit

'Optional, sort the list.
Range("A1").CurrentRegion.Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

End Sub

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder
  1. Prasad Avasare says:

    Hi Tom,
    I love the macro you have written. I have a faster solution to list down all the files in excel. Simply create a text file and write the code “dir/b *.* > dirlist.xls” as mentioned in the video. Change the file extention from .txt to .bat and double click to run the file. This will create an excel file which include all the files and folder names in that perticular path (in this video – desktop). Copy the listing.bat file in any other location and run the file. The excel file will be generated in the same location with the data.

    Here is the link of my video on youtube
    http://youtu.be/on2BMXfbgig

    • Tom Urtis says:

      Hello Prasad – –

      Thank you for following my blog. I’ve also compiled a list of 1001 Excel tips, half a dozen of which I tweet daily, and number 806 is this to list all files in folder:

      Start > Accessories > Command Prompt > cd C:\YourPath > Enter > dir filelist.txt > Enter

      Note that it is not necessary to create a text file in advance which your method requires. The benefit of my macro is to actually list the file names on the active worksheet of a working file, not in a separate file.

      Thanks again for your comment and for posting a link to your video.

  2. Prasad Avasare says:

    Hi Tom,
    Thank you for your prompt reply. Can you modify your macro to list all the file names in the path. Actually I have written this text file program to rename thousands of files. my macro file is actually pulling all the file names listed in dirlist.xls and then rename it. but for this everytime I have to paste this batch file in the path and run to create dirlist .xls where I need to rename files. Your help on this will be greatly appriciated.

    Thanks & Warm Regards,
    Prasad

    • Tom Urtis says:

      You wrote:
      “Can you modify your macro to list all the file names in the path.”

      Take a close look at my original blog post, where I answered your question (if I understand it correctly) with this I wrote:

      If you want to list all files of all types, simply delete or comment out the lines
      If InStr(objFile.Name, “.xls”) > 0 Then
      and the End If statement three lines below that.

      If this is not what you are asking about, please post back with what I’m not understanding.

      • Prasad Avasare says:

        Hi Tom,
        This what exactly I wanted. I already tried exactly the same before your reply and its successful. 🙂

        You are really great….!!! Now I can modify my macro and reduce efforts and time significantly to rename files at a time.

  3. Atiq says:

    Kindly provide this macro file in .doc format. I will download it. My mobile can not copy huge data.

  4. Abhilash VK says:

    Do we really need to write a macro? If I am not wrong. http://bit.ly/1pqNQ31

Leave a Reply

Your email address will not be published. Required fields are marked *

*