List FileName
 

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!

List FileName
 

The code here will do what you want

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

Mike

"ViestaWu" wrote:

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!

List FileName
 

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)

"ViestaWu" wrote in message
...
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!