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

If you have a large workbook with many formulas, Excel does sometimes seem
to unilaterally reset the calculation mode to manual.

--

Vasant


"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

You sure? I've never seen that.

But I have seen this:

http://support.microsoft.com/?scid=243495
XL: Calculate Message Remains in Status Bar If 65,536 Formula References

From that KB:

Microsoft Excel will correctly calculate all formulas. However, the "Calculate"
message in the status bar indicates that Microsoft Excel can no longer track the
formula dependencies and is calculating every formula in the workbook after each
change. If you are unsure when the last calculation event took place and you
want to be sure your formulas are up to date, simply start a calculation
manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells: n%"
disappears and "Calculate" returns to the status bar.

Vasant Nanavati wrote:

If you have a large workbook with many formulas, Excel does sometimes seem
to unilaterally reset the calculation mode to manual.

--

Vasant

"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





--

Dave Peterson

That may not have been what I said, but it was probably what I meant! <g

--

Vasant


"Dave Peterson" wrote in message
...
You sure? I've never seen that.

But I have seen this:

http://support.microsoft.com/?scid=243495
XL: Calculate Message Remains in Status Bar If 65,536 Formula References

From that KB:

Microsoft Excel will correctly calculate all formulas. However, the
"Calculate"
message in the status bar indicates that Microsoft Excel can no longer
track the
formula dependencies and is calculating every formula in the workbook
after each
change. If you are unsure when the last calculation event took place and
you
want to be sure your formulas are up to date, simply start a calculation
manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells:
n%"
disappears and "Calculate" returns to the status bar.

Vasant Nanavati wrote:

If you have a large workbook with many formulas, Excel does sometimes
seem
to unilaterally reset the calculation mode to manual.

--

Vasant

"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





--

Dave Peterson

Ahhh. I've suffered/enjoyed the same symptom.



Vasant Nanavati wrote:

That may not have been what I said, but it was probably what I meant! <g

--

Vasant

"Dave Peterson" wrote in message
...
You sure? I've never seen that.

But I have seen this:

http://support.microsoft.com/?scid=243495
XL: Calculate Message Remains in Status Bar If 65,536 Formula References

From that KB:

Microsoft Excel will correctly calculate all formulas. However, the
"Calculate"
message in the status bar indicates that Microsoft Excel can no longer
track the
formula dependencies and is calculating every formula in the workbook
after each
change. If you are unsure when the last calculation event took place and
you
want to be sure your formulas are up to date, simply start a calculation
manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells:
n%"
disappears and "Calculate" returns to the status bar.

Vasant Nanavati wrote:

If you have a large workbook with many formulas, Excel does sometimes
seem
to unilaterally reset the calculation mode to manual.

--

Vasant

"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





--

Dave Peterson


--

Dave Peterson

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

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

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

Where did you put that:
Application.Calculation = xlCalculationAutomatic
line?

Was there an activeworkbook at the time?


Hari wrote:

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









--

Dave Peterson

Hi Dave,

This problem was ( and still does) troubling me like heaven.

I grew so desperate with this that I added "Application.Calculation =
xlCalculationAutomatic" in literally every other line.

But even after such .... measures ".foundfiles()" doesnt display the correct
number of files in ".newsearch".

There is one active workbook other than the personal macro folder. Im
running all this code from the personal macro folder to the active workbook.

Regards,
Hari
India
"Dave Peterson" wrote in message
...
Where did you put that:
Application.Calculation = xlCalculationAutomatic
line?

Was there an activeworkbook at the time?


Hari wrote:

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









--

Dave Peterson

Hari,

I believe that you may find the answer to your problem here.

Description of how Excel determines the current mode of calculation
http://support.microsoft.com/default...b;en-us;214395


HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hi,


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