Project: Filesearch drop results into a worksheet

[Rob A]

I have a mini-project I would like to build as a utility to help support some work I do for current and future projects, but I'm not quite sure how to start. I appologize that I do not have time right now to provide some code to show how I would start this project (I am a relative newby to VBA, but I'm learning quickly). I thought I'd throw this "out there" and see what happens. If anyone has ideas and/or answers to my questions below, that would get me started. Thanx.

MY MINI-PROJECT:
I would like to return the results of a filesearch (multiple files) into a worksheet including the filename, path, filetype (if possible) and the update timestamp. This is similar to the results of the find-files tool in Windows Explorer, except that I would like to have these results in Excel so they can be manipulated.

Ideally, I would like to use the update timestamp of files to narrow the search criteria (return only those files that have been modified within the last ten days, two months, or within a certain range of dates). I also would like to use wildcard characters in the search string. In my limited experience with FileSearch, I have had nothing but bad luck. I can't figure out how to use wildcards (is it possible?) I also would like to search the contents of the .Lookin directory as well as any child directories.

It would also be ideal if the user could specify the name of the workbook they want to create that would contain the results of the FileSearch.

I'd like to build my own code, but snippets of code that anyone here can provide to any aspect of the above problem would very interesting to see. I can piece the puzzle together and when I'm finished I will post the finished code here for other newbies. Thanx!

[gwhitfield]

Rob,

Here's a starting point:

<pre>Dim sFileName As String
Dim i As Long
Dim sPath As String

sPath = "c:afpforms97"
sFileName = Dir(sPath)
Do While sFileName <> ""
If Not (GetAttr(sPath & sFileName) And vbDirectory) = vbDirectory Then
If sFileName <> "." And sFileName <> ".." Then
i = i + 1
Cells(i, 1) = sFileName
Cells(i, 2) = FileDateTime(sPath & sFileName)
End If
End If
sFileName = Dir ' Get next entry.
Loop
</pre>

[Rob A]

Thanks Geoff, this got me started. I don't have a lot of time right now to tinker with this, but I attached this code to a button control and it worked like a charm. Gratefully it doesn't use FileSearch at all.

Thanks for the tip, I can definately start my project with this.