Problems using VB.Net OleDbDataAdapter and Excel

[Terry H]

Hi

I have a VB.Net app that uses Jet and Ole DB to interact with Excel
worksheets.

I use an OleDb.OleDbDataAdapter to fill a DataTable, allow users to make
changes to the data via a grid, then call the DataAdapter's Update method to
pass the changes back to Excel.

I wrote the Insert and Update command queries myself, adding all the
parameters, and it all works perfectly.

However, I now find that it all fails if any of the worksheet rows being
updated contain formulas. It seems that if a row contains any cell whose
value is an Excel formula (i.e. beginning with '=') the entire row is
read-only to ole db.

Does anyone else have this problem? How can I get round it?

As many real-world Excel spreadsheets contain formulas (like Total columns
etc), this seems to render OLE DB impractical to use for interacting with
Excel in real-world apps; and this should be documented far more clearly.
I've only found one line, tucked away in an MSDN knowledge base article,
that documents this problem with formulas.

Any advice would be much appreciated.

Sam B

 

[onedaywhen]

Formula cells cannot be updated using ADO.

Using ADO classic, Jet and OLE DB, I get slightly different behaviour
to that which you describe. I find that if a row contains any cell
whose value is an Excel formula, only the formula cells are read-only,
the non-formula cells in the same row are read-write. It might be
worth you re-testing with ADO.NET to be sure the entire row is
read-only.

Note that although you can change non-formula cells via ADO, any Excel
formulas which use these cells will not be updated to show the changes
until the workbook is opened in Excel and the formulas recalculated.

 

[Terry H]

Thanks for the reply

Because of this problem with formulas, I am having to re-write my class to
use the Excel Object Model instead of OleDb.
I now write the changes to the data back to the worksheet cell-by-cell,
checking first that the cell doesn't contain a formula. I then recaluculate
all the formulas and pull the new dataset back into my app.

It's so much more messy and slow than my nice neat Ole Db solution, but
that's programming eh

Terry