updating range value via knowledbase 278973 fails when cell contains formula

  • Thread starter Thread starter Ian Ornstein
  • Start date Start date
I

Ian Ornstein

I'm using ADO from Access to update some massive Excel worksheets.
The adodb connection object points to the workbook.
The recordset points to a named range.
I read through the recordset and do a
oRS.Fields(0) = newIntergerValue
oRs.Update.


This technique works well for a few cells and then fails.
The cell it fails on contains a formula something like
=Sum(....

From what I have read this evening, I am suspecting that this is being caused
by different data type in the cell. Am I able to acertain the data type of the
cell by reading it and examining some ADO property? If so then I could adjust
the type of the data I use for the replacement, most likely string.

Suggestions welcome.
Thanks in advance
 
I don't think the problem in your case is the column's datatype. The
datatype should in theory be based on the values returned by cell
values and formulas in that column (but in practise is dependent on
your connection properties and registry settings).

Rather, I think your problem is that the column for the row where the
underlying cell contains a formula is not updateable. In short, you
can't update a formula cell using ADO.

Also note, the same column may be updateable for other rows where the
underlying cell is not a formula, however if you do update these, ADO
will not recalculate the formulas. They will only recalculate when the
workbook is subsequently opened in Excel and a recalculation occurs.
 
Back
Top