Copying List of Files to Excel

  • Thread starter Thread starter Amr Tabbarah
  • Start date Start date
A

Amr Tabbarah

Is there a way to copy the list of files of any type, from a selected
directory/folder, into Excel in such a way that each file name and its
extension are in a seperate cell, and all file names are in the same
column?

Thanks.
 
Amr

This routine will return an autofitted, sorted list in column A
in the active sheet. Works for Excel 2000 and onward.

1. Enter the VBA editor with <Alt><F11>
2. Doubleclick the project in the project window
(upper left of the screen)
3. Choose Insert > Module
4. Copy the below code and paste it into the righthand
window.
5. Edit DirPath to reflect the actual folder
6. Return to the sheet with <Alt><F11>
7. Run the macro from Tools > Macro > Macros

Sub GetFileNames()
'Leo Heuser, 5 Jan. 2004
Dim ActCell As Range
Dim Counter As Long
Dim DirPath As String
Dim Dummy As Variant

DirPath = "F:\Dokumenter\Excel\Test"
Set ActCell = ActiveSheet.Range("A1")

With Application.FileSearch
.NewSearch
.LookIn = DirPath
.Filename = "*.*"
.Execute

For Counter = 1 To .FoundFiles.Count
Dummy = InStrRev(.FoundFiles(Counter), "\")
Dummy = Mid(.FoundFiles(Counter), Dummy + 1)
ActCell.Value = Dummy
Set ActCell = ActCell.Offset(1, 0)
Next Counter

End With

With ActCell.EntireColumn
.AutoFit
.Sort key1:=.Cells(1, 1), order1:=xlAscending
End With

End Sub
 
Thanks Leo.

I have tried it in a Sheet Code and it worked.

I was however not been able to find what you are referring to as
"Project" (is is the icon left of "File") and "Righthand window".

Could you please further clarify.

Best Regards,


Amr Tabbarah
 
You're welcome, Amr.

When you are in the VBA editor, the
projects window (View > Project Explorer)
is the window in the left part of the screen.
Here you can see, which projects (Workbooks)
are open for the moment. Doubleclick the
icon for the workbook in question and choose
the menu Insert > Module.
Now an empty general module is inserted in
the project, and the code screen for this module
is the empty window to the right. Insert the
code into this window, return to the workbook
with <Alt><F11> and you're on :-)
 
Back
Top