Since I was using ODBC to read/write Excel documents recently I was surprised not to have too much problems with it. Today a problem occured - funny thing because I was nearly through with changing the application to finally use SqlCE as persistency layer...
The problem was that we needed some large texts placed in some columns but excel just truncated it down to 255 chars (256 if you count the ' thats visible in excel). I didn't had much time to write some workaround at this point so I tested a little bit hoping to find a shorter workaround.
Of Course todays excel can handle more than 256 chars in a cell but when you save the document you'll get a warning for some incompatibility issues. Nice - file saved in "new" format - opened my program again - inserted a large text and ye......no - again truncated to 256 chars. So the ODBC driver seemed to switch back to old format. Since the driver seemed capable of handling xls files of office 12.0 (2007) according to the descriptions in the ODBC-Administration dialog I was looking for a simple way to make him use the new format. Finally I found something on the web saying that the driver determines the format by scanning some rows and while reading this I rememberd something I red earlier; there is an option "Rows to scan" in the ODBC-config and/or in the connection-string which was always set to 8. Well putting a large text in one cell in the first 8 rows and SUCCESS now even my application can write texts with more than 256 chars...
At this point I stopped testing so maybe it is possible to increase the RowsToScan variable to a max value or something - for me my quick and dirty workaround was done here