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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
Hi Richie,

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

Thanks!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top