Excel search files

[Alexxu]

Hello. I have a macro code that search all the excel files in the given location...but... i have an error all the time.
The code is:


Private Sub ExcelSearch()

Dim i As Integer
Application.Workbooks.Add

With Application.FileSearch
.NewSearch
' select the location.
.LookIn = "D:"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
ActiveSheet.Range("A1").Value = "There were " & .FoundFiles.Count & " file(s) found."
ActiveSheet.Range("A1").Select

For i = 1 To .FoundFiles.Count
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Activate

Next i
Else
MsgBox "No excel files were found."
End If
End With

End Sub


The code is made to search all the excel files and copy their names in another sheet.
How could a make it when i press run, to open a window(like open, save as...)in which he will show me all the excel files he has found in that windows.

Thanks

[Bob Phillips]

You don't say what the problem is, but this works for me



Private Sub ExcelSearch()
Dim i As Long
Application.Workbooks.Add

With Application.FileSearch

.NewSearch
' select the location.
.LookIn = "D:"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then

ActiveSheet.Range("A1").Value = "There were " & .FoundFiles.Count & " file(s) found."
ActiveSheet.Range("A1").Select

For i = 1 To .FoundFiles.Count

ActiveCell.Offset(rowOffset:=i, columnOffset:=0).Value = .FoundFiles(i)
Next i
Else

MsgBox "No excel files were found."
End If
End With

End Sub

[mdmackillop]

You also don't say what version of Excel you are using, but FileSearch is not supported in Excel 2007

[Alexxu]

Yes..this doesnt work in 2007, that is way y was receving that err msg.
But my question is: what should i do when i launch the code to open a file(just like open, save as..)in which he will show me the files found.
Thanks.

[Bob Phillips]

Application.FileDialog(msoFileDialogFilePicker)

[Alexxu]

I added that to the code and i get un error:invalid use of property.
When i select debug the bold words appear like un error.
Where i go wrong?
Many thanks for your help.


Private Sub ExcelSearch()
Dim i As Long

Application.Worksheets.Add
Application.FileDialog (msoFileDialogFilePicker)
With Application.FileSearch

.NewSearch
' select the location.
.LookIn = "D:"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then

ActiveSheet.Range("A1").Value = "There were " & .FoundFiles.Count & " file(s) found."
ActiveSheet.Range("A1").Select

For i = 1 To .FoundFiles.Count

ActiveCell.Offset(rowOffset:=i, columnOffset:=0).Value = .FoundFiles(i)
Next i
Else

MsgBox "No excel files were found."
End If
End With

End Sub

[Bob Phillips]

You asked for code to get a filedialog like the SaveAs dialog. It is a different thing to FileSearch. Check VBA help.

[Alexxu]

Ok.10x for your time