Hi,

Please note this is a repost of earlier message.

Im still encountering this problem.

Just to add I tried this at my home computer and (sadly) it didnt work at my
home also.

But I have tried the code at my colleagues computer a zillion times and runs
without any hitch and gives the expected results.

Regards,
Hari
India

PS : Could it be related to the fact that both my home and work computers
are set to manual calculation(??), while my colleagues computer is not. To
check the same before running the code I changed it to automatic. In the
folder there was only 1 file and I added 44 more files. Then I proceeded to
run the code but only the original excel file was accounted for. The newly
added files were ignored.



----- Original Message -----
From: "Hari"
Newsgroups: microsoft.public.excel.programming
Sent: Friday, June 11, 2004 11:19 AM
Subject: Automatic file opening procedure seeming to hold cache


Hi,

Im using the following code to open files automatically. The folder
referred
here has excel files only and the folder will always exist before the
running of the code.

Initially I had 20 files in the folder and my code will show correctly
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)" statement 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.

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

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 restarting 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
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