Hi,

Im using the following code to open files automaically. The folder refferred
here has excel files only and the folder will always exist before the
runningof the code.

Initially I had 20 files in the folder then my code will show that there are
20 files and it will perform operation on all the 20 files.

I changed the folder contents that is one time removed some excel files and
made it to 15 and one time added more excel files to make it to 30 in the
folder

Strangely, Even after the changes the "foundfiles(i)" staetment still shows
20 files only and it was referring to the names of the old 20 files.

a) Some of the old 20 files I had deleted from the folder , so the code also
stopped abruptly.

When I proceeded to run the code with the set of 30 files , again it was was
referring to the names of the old 20 files.

b) Since, none of the old 20 files were there it gave me a runtime error
'1004': 'c:\ccapps\ttlview\2004-06-11\999.xls' not found

When I tried the same code in some other computer by varying the number of
the files in the folder the code worked smoothly. Is this a computer
specific problem. Do I have to change some settings somewhere to tell excel
to consider it as a newsearch ( though the code below already includes
newsearch)

The problem is that the whole pattern of not working is random. Once, I
tried retsarting excel but no result. I have tried deleting temp files,
temporary internet files, history, cookie, but it didnt work. Then I started
working on something else. After some time when I again tried it it
worked!!. Now, I went back to the original folders and made some changes to
it ( added/ deleted) files and ran the code but was getting the same runtime
error.

I have excel 2002.I dont have any addins installed.

Please tell me how to overcome the same.

Regards,
Hari
India

Here is the code which is one of the modules in the personal folder. Second
code is clumsy ( but executes fine) because I recorded it. First code I got
from the NG)


Sub OpenWorkbooksInLocation()

Application.ScreenUpdating = True
Dim i As Integer
Dim p As String

Workbooks.Open "C:\Documents and
Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls"
Windows("IEX Format.xls").Activate
Range("A3:F7000").Select
Selection.Clear
Application.Goto Reference:="R1C1"
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd")
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count

Set wb = Workbooks.Open(filename:=.FoundFiles(i))
p = .FoundFiles(i)
Call TransferIEXExceldata(p)
Next i
End With
Application.ScreenUpdating = True
Windows("IEX format").Activate
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\Janice\Project
comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")),
FileFormat:=xlNormal
Application.DisplayAlerts = True




End Sub


Public Function TransferIEXExceldata(ByVal p As String)
'
' TransferIEXExceldata Macro
' Macro recorded 6/9/2004 by Hari Prasadh
'

'
Dim q As String
Windows("IEX Format.xls").Activate
Application.Goto Reference:="R1C1"

p = Application.WorksheetFunction.Substitute(p,
"c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "")

Windows(p).Activate
Application.Goto Reference:="R1C1"
Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited,
_
TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False,
Semicolon _
:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers _
:=True
Range("D3").Select
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 5).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Application.Goto Reference:="R13C1"
Range("A13:E13").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
Windows(p).Activate
Application.Goto Reference:="R3C4"
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Rows("3:3").Select
Selection.Clear
Application.Goto Reference:="R1C1"
Workbooks(p).Close SaveChanges:=False
Windows("IEX format.xls").Activate

End Function