View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default File unable to open

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