Need Help on : Application.Filesearch

[Crex]

Hi Guys,I am not good with VBA.Recently all are sytems are migrated from WIN XP to Win7.Since then whenever we run a specific macro we get an error.Can somebody help me fix the code..PFB the code :

Option Explicit
Dim fd As FileDialog 
 'Declare a variable as a FileDialog object.
Dim varSelectedItem As Variant 
' Path of folder
Dim wb As Workbook
Dim ws As Worksheet

Sub ImportData() 
   Dim i As Integer
Application.Calculation = xlCalculationManual 
   'Create a FileDialog object as a File Picker dialog box. 
   Set fd = Application.FileDialog(msoFileDialogFolderPicker) 
   'Use a With...End With block to reference the FileDialog object. 
   With fd 
       'Use the Show method to display the File Picker dialog box 
       'The user pressed the action button. 
       If .Show = True Then 
           .Title = "Select a Folder" 
           .AllowMultiSelect = False 
           varSelectedItem = .SelectedItems(1) 
       Else 
           MsgBox "Cancelled", vbApplicationModal, "Select Folder" 
       End If 
   End With'************************************
With Application.FileSearch    .NewSearch    .LookIn = varSelectedItem    .SearchSubFolders = False    .Filename = "*.xls" 
   .Execute    For i = 1 To .FoundFiles.Count 
       'Open each workbook 
       Set wb = Workbooks.Open(Filename:=.FoundFiles(i)) 
       'Perform the operation on the open workbook 
       wb.Worksheets("sheet1").Cells.Select        Selection.Copy 
       ThisWorkbook.Sheets("Dump").Activate        ThisWorkbook.Sheets("Dump").Range("A1").Select 
       Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
       Call Macro14 
       'close the workbook        wb.Close    'On to the next workbook 
   Next i
End With'**************************************
    'Set the object variable to Nothing. 
   Set fd = Nothing
For Each ws In Worksheetsws.Calculate
Next 
wsApplication.Calculation = xlCalculationAutomatic
End Sub

[westconn1]

migrated from WIN XP to Win7

this should not cause an issue, but change of office versions would as filesearch was discontinued in office 2007, you would need to use some other dialog for user to select files, or just loop through all files in selected folder, your code is hard to decipher, you should use code tags for it to display correctly

[snb]

crossposted here:

http://www.mrexcel.com/forum/excel-q...ml#post3669394