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
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
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.
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
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.
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.
Kindly provide this macro file in .doc format. I will download it. My mobile can not copy huge data.
Do we really need to write a macro? If I am not wrong. http://bit.ly/1pqNQ31