List FileName

[Guest]

I have a big folder which contants a lot of doc files, whose has some stable
name rule.

To make a filter to those file, I'd like to prepare a list which contains
all of the file name in this folder.

I am able to use VBA, but have no idea which function to use. Could help me?

What's more, if the function could search via subfolder, that will be better.

Thanks!

 

[Guest]

The code here will do what you want

http://www.vbaexpress.com/kb/getarticle.php?kb_id=800

Mike

 

[Bob Phillips]

Sub SubGetMyData()
Const BASE_FOLDER As String = "C:\personal\Bob" '<=== change to suit
Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim iRow As Long
Dim sh As Worksheet

iRow = 3
On Error Resume Next
Set sh = Worksheets("_filelist")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = "_filelist"
Set sh = Worksheets("_filelist")
End If
sh.Cells.Clear
iRow = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(BASE_FOLDER)
For Each objFile In objFolder.Files
If objFile.Type Like "*Microsoft*Word*" Then
sh.Cells(iRow, "A").Value = objFile.Path
iRow = iRow + 1
End If
Next
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)