Hello,
I have the following codes to process all XLS files in "/Users/lcw/Desktop/excel", but it does not work for unopened xls files (it seems like the entire Application.FileSearch mechanism does not work at all). Can someone give me a hint how to make it work?
Thank you!
Chi-Wai
_______________________________
[VBA]
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
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 = "/Users/lcw/Desktop/excel"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Sheets("US").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
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]