Hi Cecil,

Problem is specific to my computer. The macro runs well at other
workstations.

I have tried even inserting statements like "Application.Calculation =
xlCalculationAutomatic" but to no avail.
I save the personal macro folder with calculation set to automatic still the
program does not generate results as expected.

Should I reinstall my Excel 2002.

Regards,
Hari
India


"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
Hari,
This is guess work,
Try opening the offending workbook, changing the calculation to automatic
and save it.
Check that book has any event handlers that make calculation to manual.
Cecil

"Hari" wrote in message
...
Hi,

This time I ran the macro step by step and identified the point where
the
calculation changed from automatic to manual ( by using F8 and going
back
to
excel , tools, options etc after each F8 pressing.)

It was just after execution of the statement --Workbooks.Open
"C:\Documents
and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls"

I changed the setting back to automatic and contiued the macro
execution.
After that the calculation did not change back automatically in any of
the
statements. But my old problem still persists.

So, I have 2 problems now:-

1. How/Why was my calculation option changing automatically at the
execution
of this macro

2. Please tell me if there is any way out for me here.

Regards,
Hari
India


"Hari" wrote in message
...
Hi,

For the last 4 or 5 days I was running "all over the place" literally
as
I
had a code which was executing as expected in my Colleagues workplace,
but
at my computer and home computer it wasnt. Please refer to "Automatic
opening of files seeming to have cache" posted 3 or 4 days back.

I think I might have finally found the reason to this. ( Please also
refer
Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn"
posted today.

When I use to run the code (pls see below) then even if my calculation
was
set to automatic before the macro was run, excel would change it to
manual
during the course of its running ( I checked the calculation setting
at
when
the macro had executed) and probably it wasnt reading all the files as
expected.

Please tell me why this macro is behaving the way it is and also how
to
rectify the same. Interestingly the same macro when run in my
colleagues
computer will not tamper with the calculation settings(!!)


Regards,
Hari
India

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