FileSearch Object - Cannot assign to variable

[Alan]

Hi All,

Can anyone help me understand why the following code does not work:

It bombs on the penultimate line (myFiles = ...)

I was hoping to use myFiles to recursively open each file in that
collection, and then copy / paste to a master file (new worksheet for
each file) to get each separate xls file into a single workbook.

BTW, for the avoidance of doubt, every file in the C:\Temp folder *is*
an excel file.

Thanks,

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Sub GetFiles()

With Application.FileSearch

.LookIn = "C:\Temp"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute

End With

myFiles = Application.FileSearch.FoundFiles

End Sub

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

 

[Tom Ogilvy]

Sub GetFiles()
set fs = application.FileSearch
with fs
.NewSearch
.LookIn = "C:\Temp"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
if .Execute > 0 then
for i = 1 to .foundfiles.count
set wkbk = worbooks.open .foundfiles(i)
'
wkbk.close Savechanges:=False
next
End if
End With