List of files in a directory

ctaylor415

New Member
Joined
May 15, 2002
Messages
3
Is there a simple way to produce a list of files in a directory?

I am backing up files on my pc and need a list of files in excel to reference the backup disks.

Thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Chris,

Try something like this:

<pre>
Sub findfiles()
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:My Documents"
.SearchSubFolders = False
' .filename = "Run"
' .MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & " Excel file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no Excel files found."
End If

End With

End Sub
</pre>

HTH
 

Helper monkey

Board Regular
Joined
Jun 23, 2002
Messages
63
Hi Richie,

This could be really useful to me if it could return the file names as well... Is it possible?

Thanks!
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
creates a list in colummn A of the active sheet:

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:temp"
.SearchSubFolders = True
.FileName = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329

ADVERTISEMENT

Hi Helper Monkey,

The messagebox part of the posted routine shows the full path name. Is that suitable for your purposes or do you want just the filename?
 

Helper monkey

Board Regular
Joined
Jun 23, 2002
Messages
63
Cheers guy's! :)

Ideally i would like to have a list of the existing files in a listbox so that i can select a single existing workbook and then open it.

I'll try and tinker with the code that you've given me and see what i can come up with.

Thanks again!
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
I'm guessing your listbox is on a userform, in which case the following code (adapted from a function I use at work), placed in the userform's Initialize event, seems to work OK. Change the TargetFolder to the path you require and the extension to "*.*" to list all files, not just Excel workbooks. You'll need to click on Tools-References and check the Microsoft Scripting Runtime box for the code to work: -

Code:
Private Sub UserForm_Initialize()
Dim myFileSystem As New Scripting.FileSystemObject
Dim myFolder As Scripting.Folder
Dim myFiles As Scripting.Files
Dim myFile As Scripting.File
Const TargetFolder As String = "C:My Documents"
Const myExtension As String = "*.xls"

On Error Resume Next
Set myFolder = myFileSystem.GetFolder(TargetFolder)
If Err <> 0 Then MsgBox "Unable to find folder.": Exit Sub

Set myFiles = myFolder.Files
For Each myFile In myFiles
    If Right(LCase(myFile.Name), 4) Like myExtension Then
        Me.ListBox1.AddItem myFileSystem.GetBaseName(myFile.Name)
    End If
Next myFile

Set myFolder = Nothing
Set myFileSystem = Nothing

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,210
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top