none
Replace Excel Formula with Text Value using OLEDB RRS feed

  • Question

  • I have an Excel spreadsheet that contains a cell with a formula.  The cell is formatted as Text.  From a vbscript application, I want to replace the formula in this cell with a specific text value.  However, when I attempt this, I receive the following error "Microsoft Jet Database Engine: Field cannot be updated."  If the cell in question only contains a text value then everything works fine.  But if the cell contains a formula, then I receive this error.  How can I force the cell contents to be replaced via OLEDB when the cell contains a formula?

    Thanks
    Kevin M.

    Thursday, February 25, 2010 9:37 PM

Answers

  • I'm not sure if there is a method for determining whether a Column/Cell is read-only through data access. You may want to simply trap the exception that occurs, using a Try...Catch block, when the column is read-only.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 1, 2010 2:06 PM

All replies

  • Cells with formulas are read-only and can't be changed through data access methods. You would have to remove the formula through Excel automation or the Excel application itself before updating these cells via DAO, ADO (OLEDB).
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, February 26, 2010 1:38 PM
  • Thanks for confirming my fears Paul.  I am very disappointed to learn that there is no mechanism to override this read-only behavior.  But since there isn't...

    From a vbscript application, how can I determine which columns are designated read-only, so that I can avoid attempting to update them?  I see there appears to be a GetColumnInfo method in oledb, but I can't make heads or tails of how to use that method from vbscript.  My vbscript application already opens, reads data from, and updates the spreadsheet using ADODB Recordsets.  It seems that there should be some way to access the GetColumnInfo method through a RecordSet, but I can't figure out how to do that in vbscript, and how to store/read/interpret the data that would be returned.  Any hints?

    Friday, February 26, 2010 11:30 PM
  • I'm not sure if there is a method for determining whether a Column/Cell is read-only through data access. You may want to simply trap the exception that occurs, using a Try...Catch block, when the column is read-only.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 1, 2010 2:06 PM