Help me! Tricky problem with a deadline!

Posted by Rhona Mowatt on January 28, 2001 2:10 PM

Hi,
I need a macro which will allow me to input a path name into an input box e.g. m:\finance. The macro will then search that directory and produce a list of all files in an Excel spreadsheet for the directory in question. I know this can be done as I've seen it before but I'm stumped.

Any help will be greatly appreciated.

Thanks,
Rhona.

Posted by Tim Francis-Wright on January 29, 2001 6:52 AM

This will work: it puts the files in Column A,
starting with A1. It should be fairly clear
how to modify it to meet your needs.

Sub trythis()
Dim Path As String

Range("A1:A65000").ClearContents
Path = InputBox(prompt:="Enter the path of the folder to search:", Title:="Mrexcel.com")
With Application.FileSearch
.NewSearch
.LookIn = Path
.SearchSubFolders = True ' change as needed
.FileName = "*" ' Change as needed
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles ' change as needed
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i) ' results in cell A[1-n]
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Good luck!

Posted by Rhona Mowatt on January 29, 2001 12:59 PM

Re: Thanks!

Thanks Tim,

I knew there would be some clever person out there who'd know.

Thanks again,
Rhona.