Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using VB.Net OleDbDataAdapter and Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using VB.Net OleDbDataAdapter and Excel
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using VB.Net OleDbDataAdapter and Excel
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 "onedaywhen" wrote in message om... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
Excel Problems | Excel Discussion (Misc queries) | |||
Excel problems | Excel Discussion (Misc queries) | |||
Mac to PC Excel problems | Excel Discussion (Misc queries) | |||
Problems using Excel 2000 to open/save file saved in Excel 2003 | Excel Discussion (Misc queries) |