i want to copy file names in to excel to creat a list for a register of
files , is there a way to copy the names straight in to excel or woul
di have to type them all in

robert


--
covi2k2
------------------------------------------------------------------------
covi2k2's Profile: http://www.excelforum.com/member.php...o&userid=28078
View this thread: http://www.excelforum.com/showthread...hreadid=485861

Hi

below are a couple od UDF's, combining which you can get the list of files
in any folder or subfolder. The optional parameter MyTime is used to make
formula recalculate {use TODAY() or NOW() as optional parameter}


Public Function GetSubfolder(MyFolder As String, FolderNum As Integer,
Optional MyTime As Date)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function


Public Function GetFileName(MyFolder As String, FileNum As Integer,
MyExtension As String, Optional MyTime As Date)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set fc = f.Files
i = 0
For Each f1 In fc
If Right(f1.Name, 3) = MyExtension Then
i = i + 1
If i = FileNum Then GetFileName = f1.Name
End If
Next
End Function


Public Function GetThisFolder(Optional MyTime As Date)
GetThisFolder = ThisWorkbook.Path
End Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"covi2k2" wrote in
message ...

i want to copy file names in to excel to creat a list for a register of
files , is there a way to copy the names straight in to excel or woul
di have to type them all in

robert


--
covi2k2
------------------------------------------------------------------------
covi2k2's Profile:
http://www.excelforum.com/member.php...o&userid=28078
View this thread: http://www.excelforum.com/showthread...hreadid=485861

Here's one way using DIR

Sub FindExcelFiles() 'It does work.
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\ahorse\*.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

"covi2k2" wrote in
message ...

i want to copy file names in to excel to creat a list for a register of
files , is there a way to copy the names straight in to excel or woul
di have to type them all in

robert


--
covi2k2
------------------------------------------------------------------------
covi2k2's Profile:
http://www.excelforum.com/member.php...o&userid=28078
View this thread: http://www.excelforum.com/showthread...hreadid=485861

Try this: http://vbaexpress.com/kb/getarticle.php?kb_id=781
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"covi2k2" wrote in
message ...

i want to copy file names in to excel to creat a list for a register of
files , is there a way to copy the names straight in to excel or woul
di have to type them all in

robert


--
covi2k2
------------------------------------------------------------------------
covi2k2's Profile:
http://www.excelforum.com/member.php...o&userid=28078
View this thread: http://www.excelforum.com/showthread...hreadid=485861