Solved: Excel Application.Filesearch

[jreichenbach]

Hi all,

I know there have been some posts on this subject and I've tried to implement some of the ideas but I have had no luck. We just upgraded to Office 2010 and now a routine of ours won't run. How do I modify the below code to get it to work with excel 2010:


Set fs = Application.FileSearch
With fs
.LookIn = "C:\Bankcsv\Monthly Downloads\" & olddirectory & "\" & carrierdirectory
.Filename = "*_JH*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
response = MsgBox("There were " & .FoundFiles.count & _
" file(s) found. Is this OK?", 1, "Number of Files Found")
If response = 1 Then
For i = 1 To .FoundFiles.count
Workbooks.Open Filename:=.FoundFiles(i)
mybook = .FoundFiles(i)
Selection.QueryTable.Refresh BackgroundQuery:=False
Cells.Find(What:="Policy Value", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Replace What:="Policy Value", Replacement:=monthenddate, LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "mm/dd/yy"
mybooklength = Len(mybook)
myfile = Mid(mybook, 49, mybooklength - 44)
ChDir "C:\Bankcsv\Monthly Downloads\" & newdirectory & "\" & carrierdirectory
Sheets("Sheet1").Range("A1").Select
ActiveWorkbook.SaveAs Filename:=myfile
ActiveWorkbook.Close
Next i
Else
End If
Else
MsgBox "There were no files found."
End If

Any help would be greatly appreciated! Thanks in advance for helping me out.


Jay

[GarysStudent]

See:

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/i-have-a-spreadsheet-which-uses/669118de-e3a5-4815-bb95-0a83eb6bf984

[Kenneth Hobs]

This class worked ok for me. http://www.mrexcel.com/forum/showthread.php?t=369982

[jreichenbach]

Thank you GarysStudent it worked perfectly!