On Jan 28, 9:40*pm, Dave Peterson wrote:
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
andhttp://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- Hide quoted text -

- Show quoted text -

Hi Dave,

Thanks for your advice.

I try to work around with the appropriate codes and will see later

Regards
Len