get a directory listing, store it in worksheet

[jdavis9]

Ok, here is what I need to do.
Get a directory listing of files located in c:\valvecurves\histdata\
all files as *.xls

list them in a worksheet, named "history_files"

What I currently do:

Using VB:
Shell and run a bat file which takes a directory listing
and writes it to a text file name dirlst.txt

the bat file look like this
dir *.xls /on /o-d >c:\valvecurves\histdata\dirlst.txt

then I open the text file, copy column "F" and paste it into my worksheet.

My question:
Isn't there an easier way to do this?

 

[Juan Pablo González]

I think that's a pretty good way to do it, but another way is to use the Application.FileSearch object.

 

[TommyGun]

Here's a very simple VB example using the DIR function.

Sub DirectoryList()

Dim p As String
Dim f As String
Dim c As Long

p = "C:\ProgramFiles\Files\*.xls"
f = Dir(p)

c = 1

Do While f <> ""

    ThisWorkbook.Sheets(1).Cells(c, 6) = f

    c = c + 1
    f = Dir

Loop

End Sub

 

[jdavis9]

As always, Thank you

Thank both of you. This is an incredible resource............