Macro resets my calculation to manual

[Hari]

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

 

[Vasant Nanavati]

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

 

[Hari]

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

 

[pfsardella]

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.aspx?scid=kb;en-us;214395


HTH
Paul

 

[Cecilkumara Fernando]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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

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

 

[Hari]

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

 

[Dave Peterson]

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

Was there an activeworkbook at the time?

 

[Hari]

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]

I'm not sure why the calculation won't stick. You may want to post a little of
your code and give a short description of the current configuration.

You have another reply at your other thread (multiple threads are a pain!).