PDA

View Full Version : Need Help on : Application.Filesearch



Crex
12-23-2013, 10:09 PM
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
12-24-2013, 03:14 AM
migrated from WIN XP to Win7this 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
12-24-2013, 05:05 AM
crossposted here:

http://www.mrexcel.com/forum/excel-questions/746795-application-filesearch-error.html#post3669394