Solved: 2010 application.filesearch

[jmenche]

Howdy,

I just got upgraded from 2003 to 2010....and now this routine doesn't work :-(. I "borrowed" this routine from the web to begin with so i'm not savvy enough to upgrade it.

Can someone help me out?


Thanks!

[VBA]Sub UpdateDashboards()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet
Dim qt As QueryTable
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\jeff.menche\Desktop\Revenue Tracker\Dashboards"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)

For Each ws In wbResults.Worksheets
ws.Unprotect Password:="itsc"
For Each qt In ws.QueryTables
qt.BackgroundQuery = False
qt.Refresh
Next
Next

wbResults.RefreshAll

For Each ws In wbResults.Worksheets
ws.Protect Password:="itsc", AllowUsingPivotTables:=True
Next

wbResults.Close SaveChanges:=True

Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
[/VBA]

[shrivallabha]

Application.Filesearch is no longer available (2007+). But google search still works

Here is one promising thread:
http://www.mrexcel.com/forum/showthread.php?t=332942

[Kenneth Hobs]

In this thread, I gave an example on how to use a class in a similar way.
http://www.vbaexpress.com/forum/showthread.php?t=37784

[jmenche]

I redid the routine using the Dir method and it works now. I probably should have done it this way before.

Thanks for the help!

[VBA]Sub UpdateDashboards2()
Dim strPath As String
Dim strFile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim qt As QueryTable

Application.ScreenUpdating = False

strPath = "C:\Documents and Settings\jeff.menche\Desktop\Revenue Tracker\Dashboards\"
strFile = Dir(strPath + "*.xls")

Do While strFile <> ""
Set wb = Workbooks.Open(strPath & strFile)

For Each ws In wb.Worksheets
ws.Unprotect Password:="itsc"
For Each qt In ws.QueryTables
qt.BackgroundQuery = False
qt.Refresh
Next
Next

wb.RefreshAll

For Each ws In wb.Worksheets
ws.Protect Password:="itsc", AllowUsingPivotTables:=True
Next

wb.Close True
strFile = Dir
Loop

Application.ScreenUpdating = True
End Sub[/VBA]