Loop not working

[jmenche]

Howdy,

Everything about this sub works except for the part where it is supposed to refreshall. The maddening part is that i can refreshall while in the workbook. Something is not right when I run this sub from another workbook.

Anybody have any thoughts?



[VBA]Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Revenue Tracker\Dashboards"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE

wbResults.Sheets("Dashboard").Unprotect Password:="itsc"

wbResults.RefreshAll

wbResults.Sheets("Dashboard").Protect Password:="itsc"

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]

[p45cal]

Something is not right when I run this sub from another workbook.

Well, 'something' is not very informative!
Are all the things which are refreshing, background refreshes? So, is the code waiting for all things to be refreshed before continuing?
On Error Resume Next isn't helping you to determine what's going wrong. Knock it out while developing/testing.