Hi Mike,

Try this,

Example from VBA Help & modified.

Sub Test()
Dim filearray()
Dim a as long

Dim MyPath, MyName

MyPath = "C:\windows\temp\" ' Set the path.

MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName < "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName < "." And MyName < ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory
Then
'Debug.Print MyName ' Display entry only if it
represents a directory.

a = a + 1
ReDim Preserve filearray(a)
filearray(a) = MyName

End If
End If
MyName = Dir ' Get next entry.
Loop

Open "c:\filearray.txt" For Output As #1
For j = 1 To a
Write #1, filearray(j)
Next j
Close #1
End Sub



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Shailesh,
Can I direct an additional question regarding to your answer?.
Say I want to list *.doc files only under MyPath to that output file.
May you modify your solution accordingly?
TIA
for it!

Martyn,

Easy enough

Dim filearray()
Dim a As Long, j As Long

Dim MyPath, MyName

MyPath = "D:\Bob\bu Tasters\Excel\" ' Set the path.

MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName < "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If Right(MyName, 4) = ".doc" Then
If MyName < "." And MyName < ".." Then
a = a + 1
ReDim Preserve filearray(a)
filearray(a) = MyName
End If
End If
MyName = Dir ' Get next entry.
Loop

Open "c:\filearray.txt" For Output As #1
For j = 1 To a
Write #1, filearray(j)
Next j
Close #1

--

HTH

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

"Martyn" wrote in message
...
Shailesh,
Can I direct an additional question regarding to your answer?.
Say I want to list *.doc files only under MyPath to that output file.
May you modify your solution accordingly?
TIA
for it!

Hi Martyn,

Try this,

Sub ListFileName()

Dim filearray()
Dim MyPath, MyName, MyCond
Dim j As Long, a As Long

MyPath = "c:\windows\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
MyCond = ".txt"
Do While MyName < "" ' Start the loop.
If (GetAttr(MyPath & MyName) And vbDirectory) < vbDirectory Then
If UCase(Right(MyName), 4) = UCase(MyCond) Then
a = a + 1
ReDim Preserve filearray(a)
filearray(a) = MyName
End If
End If
MyName = Dir ' Get next entry.
Loop

Open "c:\filearray.txt" For Output As #1
For j = 1 To a
Write #1, filearray(j)
Next
Close #1
End Sub


You can also run in dos mode, DOS Internal command DIR :
e.g.

Dir c:\windows\*.txt c:\filelist.txt

then you can view filelist.txt as under (in dos mode):
Type c:\filelist.txt


But with VBA you can adopt two methods below:

Sub BatchFile()
'To create batchfile that will run from Shell as we cann't use Shell
"C:\windows\*.txt C:\filelist.txt"

Dim BatchFilename, OutputFilename, MyCond

BatchFilename = "c:\dirlist.bat" 'Batch Filename
OutputFilename = "c:\filelist.txt"

' Find the xls files starting with "s"
MyCond = "dir c:\s*.xls /s " ' /S = search in all sub dir also

Open BatchFilename For Output As #1 ' Open file.
Print #1, MyCond & OutputFilename ' Write string to file.
Close #1 'close

Shell BatchFilename, vbNormalFocus 'Run batch File

End Sub

Sub DosCommand()

Dim OutputFilename, MyCond, Mac

OutputFilename = "C:\filelist.txt" ' output filename
' Find the xls files starting with "s"
MyCond = "dir C:\s*.xls /S" ' /S = search in all sub dir also

On Error Resume Next
Kill FileName ' if exist Kill. To append data remove or comment.
On Error GoTo 0

Mac = Shell(Environ$("comspec") & " /c " & MyCond & " " &
OutputFilename, 1)

End Sub



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thank you so much Shailesh, I'll give all of them a try...

Unfortunately The VBA code didn't work...because there was a small command
error with this line:

If UCase(Right(MyName), 4) = UCase(MyCond) Then

I think we are not allowed to use anything else then just a string within
the "( )" for the UCase( xxxxxx) command.

Thus I changed your code a bit and now it works just fine...
Here goes:
--------------------------------
Sub ListFileName()

Dim filearray()
Dim MyPath, MyName, Mt, MyCond ' I declare an additional variable called
Mt
Dim j As Long, a As Long

MyPath = "c:\windows\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrive the first entry.
MyCond = ".txt"
Do While MyName < "" ' Start the loop.
If (GetAttr(MyPath & MyName) And vbDirectory) < vbDirectory Then
Mt = (Right(MyName, 4)) ' I use this new variable to get the last
four characters.
If UCase(Mt) = UCase(MyCond) Then ' And changed this bit such that
the format of UCase is accepted.
a = a + 1
ReDim Preserve filearray(a)
filearray(a) = MyName
End If
End If
MyName = Dir ' Get next entry.
Loop

Open "c:\filearray.txt" For Output As #1
For j = 1 To a
Write #1, filearray(j)
Next
Close #1

End Sub
---------------------------------------
Thanks a lot for your help.
Sincerely