how to get excel to calculate from ASP?

[mike]

hello everyone,

I can put and read values from excel using ado. I think the main
point of using excel is you can use it do some calculations. however,
when i put values in some cells and read the values of cells that
normally calculate something using those inputs, it doesnt work. I
have to open excel manually, then close it, before the values of those
cells update. How do I get excel to calculate from ASP using ADO or
some other method so that I can read the updated values?

Thanks for any help,

Mike

 

[Bill Renaud]

After you put the values into the worksheet, use the following code:

Application.Calculate

.... to cause the recalculation engine to update all formulas before you
fetch the results. Normally, Excel waits until all macros have ended before
calling the recalculation engine.

Also, are you sure that your values are numbers, and not text values? You
can try the following routine to check. Simply select the range of cells
that are supposed to be numbers. It will stop and show each cell that is
not a number.

'----------------------------------------------------------------------
Public Sub CheckCells()
Dim rngCell As Range
Dim blnNonNumericFound As Boolean

For Each rngCell In Selection
If Not IsEmpty(rngCell) _
Then
If Not IsNumeric(rngCell) _
Then
blnNonNumericFound = True
With rngCell
.Activate
MsgBox "Cell " & .Address & " value is not a number!", _
vbInformation + vbOKOnly, _
"Check Cells"
End With
End If
End If
Next rngCell

If Not blnNonNumericFound _
Then
MsgBox "All cells in the selection were numbers.", _
vbInformation + vbOKOnly, _
"Check Cells"
End If
End Sub

 

[Bill Renaud]

<<Normally, Excel waits until all macros have ended before calling the
recalculation engine.>>

Experiment results: No longer true, in Excel 2000. Setting
Application.Calculation = xlCalculationManual stops calculation, but it
automatically resumes as soon as Application.Calculation =
xlCalculationAutomatic is executed.

Excel Recalculation articles:

"Excel Recalculation"
http://msdn2.microsoft.com/en-us/library/bb687891.aspx

"Recalculation in Microsoft Excel 2002"
http://msdn2.microsoft.com/en-us/library/aa140058(office.10).aspx