Creating a list of found files
 

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.doc"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i,"A").Value = FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Should work.

your .Range is the problem. the scope of the With statement when you issue
that is a filesearch object which doesn't have a range property. If you
remove the dot, it would probably work, but then you would write each name
to A1, so only the last file name would remain.

--
Regards,
Tom Ogilvy


"Doug" wrote in message
...
I am trying to create a list of files found with the
filesearch object. I can find the files and list them but
I want to write each file name to a cell in the active
workbook. Here's the code from MS to show the files found:
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.doc"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Now I want to take that list and one at a time drop the
found filenames into column A starting at row 1. When I
insert .Range("A1") = .FoundFiles(i) it won't run. I've
tried several different options but nothing seems to work.
Any suggestions or help would be greatly appreciated.
Doug

Creating a list of found files
 

I have these lines at the end of my file search:

Range("B:B").Clear
Set rngFiles = Range("B15")
rngFiles.Offset(0, 0) = "Found " & .FoundFiles.Count & " containing
" & .TextOrProperty
For lngIndex = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex, 0),
Address:=.FoundFiles.Item(lngIndex)
Next

This writes the names of the documents into Col. B beginning in B15 as
hyperlinks. (I have it set up earlier to give me the whole name including
file path.)

HTH
Ed


"Tom Ogilvy" wrote in message
...
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.doc"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i,"A").Value = FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Should work.

your .Range is the problem. the scope of the With statement when you
issue
that is a filesearch object which doesn't have a range property. If you
remove the dot, it would probably work, but then you would write each name
to A1, so only the last file name would remain.

--
Regards,
Tom Ogilvy


"Doug" wrote in message
...
I am trying to create a list of files found with the
filesearch object. I can find the files and list them but
I want to write each file name to a cell in the active
workbook. Here's the code from MS to show the files found:
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.doc"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Now I want to take that list and one at a time drop the
found filenames into column A starting at row 1. When I
insert .Range("A1") = .FoundFiles(i) it won't run. I've
tried several different options but nothing seems to work.
Any suggestions or help would be greatly appreciated.
Doug