Forum Home
Press F1
 
Thread ID: 81414 2007-07-27 03:58:00 Making Excel list of File names within a Folder Reggarb (12599) Press F1
Post ID Timestamp Content User
573176 2007-07-27 03:58:00 Can you make a excel list of file names within a folder?

Thanks 		Reggarb (12599)
573177 2007-07-27 04:06:00 May I ask why this would be necessary? Since your file names are already visible within a folder. winmacguy (3367)
573178 2007-07-27 04:10:00 through command promt i suppose

dir C:/whaeverdirectory/whateverfolder/

will only list files though, not folders. The copy from cmnd prmt and past to excel 		Jan Birkeland (4741)
573179 2007-07-27 04:12:00 Not into Excel directly, but....

Start / Run / type cmd
Go to the root folder you want a list of. eg cd temp
Then do dir /s /o /b >c:\templist.txt

Vary the switches to get different results.
It creates a text file of the directory and dump it in c drive which you can copy paste into excel if you want.

I use this to make a text file of all my backup CD/DVD's and I can use Search&Replace to find a file by keyword if i need it. 		Bantu (52)
573180 2007-07-27 23:47:00 Hi

Open your spreadsheet and add a new worksheet called "FileList". Open the VBA editor in Excel (by pressing the Alt & F11 keys at the same time) and copy/paste the following VBA code into the 'ThisWorkbook' project :


Public Sub ListMyFiles()

'Set a reference to 'Microsoft Scripting Runtime' under menu option:
' Tools > References > find and tick 'Microsoft Scripting Runtime'

Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long

'Change the directory below to the one you want to search
Set fso_Folder = fso.GetFolder("C:\")
file_count = 0

For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Next fso_File

Set fso = Nothing

End Sub

Make sure you set the reference per the instruction within the code and change the directory to the one you want to search.

Save and close the VBA editor, then run the macro by selecting menu option > Tools > Macros > find and run the macro.

This will put all of the file names into the first column in the "FileList" tab.

HTH, Andrew 		andrew93 (249)
1