PDA

View Full Version : Solved: 2010 application.filesearch



jmenche
06-15-2011, 08:19 AM
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?

:help
Thanks!

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

shrivallabha
06-15-2011, 08:55 AM
Application.Filesearch is no longer available (2007+). But google search still works :devil2:

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

Kenneth Hobs
06-15-2011, 09:03 AM
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
06-15-2011, 10:37 AM
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!

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