Is there a way to create a list on a worksheet of all the
file names in the current directory or all files names
with a specific extension such as .txt.

Thank you

use this that I got here. Chg to suit

Sub FindandListFiles()
Application.ScreenUpdating = False
Columns(1).ClearContents
'ThisRow = 2
'Cells(ThisRow, 1).Select
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\yourfolder\*.xls"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

--
Don Guillett
SalesAid Software

"Bill Vernon" wrote in message
...
Is there a way to create a list on a worksheet of all the
file names in the current directory or all files names
with a specific extension such as .txt.

Thank you

Bill,

Here's some code I posted earlier today to list all files in a selected
directory and its sub-directories.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(2, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Rows(1).Font.Bold = True
.Columns("A:C").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 1, 1).Value = arFiles(0, i)
.Cells(i + 1, 2).Value = arFiles(1, i)
.Cells(i + 1, 3).Value = arFiles(2, i)
Next
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(ByVal sPath)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(2, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder.")
As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

'----------------------------- end-script -----------------------------



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Vernon" wrote in message
...
Is there a way to create a list on a worksheet of all the
file names in the current directory or all files names
with a specific extension such as .txt.

Thank you

Bill,

Or you could use my Excel add-in "List Files".
Allows you to specify the file extension or wild cards.
Comes with one page Word.doc install/use instructions.
Very easy to use and available - free - upon direct request.
(remove xxx from my e-mail address)

Regards,
Jim Cone
San Francisco, CA
XX

----- Original Message -----
From: "Bill Vernon"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 2:13 PM
Subject: Directory Code

Is there a way to create a list on a worksheet of all the
file names in the current directory or all files names
with a specific extension such as .txt.
Thank you
"Bill Vernon" wrote in message
...
Is there a way to create a list on a worksheet of all the
file names in the current directory or all files names
with a specific extension such as .txt.
Thank you