Any chance you're now using xl2007?
If you are, then application.filesearch won't work anymore.
Ron de Bruin shares some alternatives he
http://www.rondebruin.nl/fso.htm
and
http://www.rondebruin.nl/copy3.htm
Len wrote:
Hi,
Something has gone wrong with excel vba codes below that unable to
open excel files from a list index in sheet(Main)
Extract of vba codes
Sheets("Main").Select
FileList = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Cells(1, 1).Resize(FileList, 1)
Filenm1 = Cell.Value
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = "J:\BBT\LO\Budget\Budget Actual\Acad"
.FileType = msoFileTypeExcelWorkbooks
.Filename = Filenm1 & "*.xls"
If .Execute 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through
all
Workbooks.Open (.FoundFiles(lCount))
ActiveWorkbook.Activate
Sheets("P+L").Select
Dim i As Long
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, "A").End(xlUp).Row
If Lstrow 0 Then
For i = 5 To Lstrow
If Cells(i, 1).Value < "" Then
Cells(i, 1).Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Cells(i, 1).ClearContents
End If
Next
Else
MsgBox "It appears that the file is empty, check the file again"
End If
Any helps will be appreciated as I'm excel vba beginner
Thanks in advance
Regards
Len
--
Dave Peterson