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

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

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