File unable to open

[Len]

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]

Any chance you're now using xl2007?

If you are, then application.filesearch won't work anymore.

Ron de Bruin shares some alternatives here:

http://www.rondebruin.nl/fso.htm
and
http://www.rondebruin.nl/copy3.htm

 

[Len]

Any chance you're now using xl2007?

If you are, then application.filesearch won't work anymore.

Ron de Bruin shares some alternatives here:

http://www.rondebruin.nl/fso.htm
andhttp://www.rondebruin.nl/copy3.htm


















--

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