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
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