Creating an Excel list of Excel Files

**kasimagj** · 07-02-2008, 11:57 AM

I have a folder filled with many different excel files.

Is it possible to create a list, in excel, that populates all of the file names from a folder?

**Macdave_19** · 07-02-2008, 11:59 AM

This code will go into a set folder and extract all the .xls filenames

just attach it to a button

Sub Filenames()
    
    Dim fName, FNames(), FType, MyPath As String, i As Integer
    
    'Change Folder to suit
    MyPath = "C:\"
    ChDrive MyPath
    ChDir MyPath
    
    FType = "*.xls"
    fName = Dir(FType)
    Do Until fName = ""
        i = i + 1
        ReDim Preserve FNames(1 To i)
        FNames(i) = fName
        fName = Dir
    Loop
    If i = 0 Then
        MsgBox "No Files Found"
    Else
        For i = 1 To UBound(FNames)
            ActiveSheet.Cells(i, 1) = Left(FNames(i), Len(FNames(i)) - 4) 'Removes the ".xls"
        Next i
    End If
End Sub

**kasimagj** · 07-02-2008, 12:19 PM

I get a compile error with 'Sub Filename()' what do I need to fix?

**ravishankar** · 07-02-2008, 12:27 PM

Hi
Replace filepath from D:\my documents\ to your folder path and runt the macro

Sub List_files()
Dim  x As Integer
Dim f As String
Cells(2, 1).Select
f = Dir("D:\my documents\" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
End Sub

ravi

**kasimagj** · 07-02-2008, 12:39 PM

for some reason it doesn't seem to work. When I run it, it moves down one row with blanks. I realized that they aren't .xls, they're .txt. So i changed the .xls -> .txt.

Why doesn't this still work?

**modytrane** · 01-13-2009, 01:58 PM

Code provided by Macdave works fine.
Look at the attached example.
Enter your path name in K1 [e.g. C:\Sales\Reports] and file type in K3 [e.g. .Doc].
And click the button.
List appears in column A.
modytrane