Alexxu
08-10-2008, 06:01 AM
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
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