Hi all, I'm working with Excel using the Spreadsheet functions in CF 9. I have some weird behavior going on with formulas, and I have a workaround, but I don't like it, so I'm looking for some direction.
I have an Excel 2007 spreadsheet. The spreadsheet has simple formulas on it that sum other cells. Here's what I do: 1. Open the Excel 2007 with ColdFusion 9 SpreadsheetRead() 2. Set some values in some of the cells 3. save the file with SpreadsheetWrite() Now, if I open the spreadsheet with Excel 2007 (on my desktop), the cells with the formulas have not 'recalculated' to reflect the newly entered values in the cells which they are summing. I don't know whether this is intended behavior or not, but it's not the behavior I want. So, here's my workaround. 1. On my desktop, I open the file in Excel 2007. 2. I save the file 'as' Excel Type 97-2003 (.xls) 3. When I save as this type I get a dialog asking me if I want to check compatibility when opening this file. I 'uncheck' the option and click continue. 4. I then get a dialog asking if I want this workbook to 'recalculate' every time it is opened. I say 'YES'. 5. The file is now saved as an .xls file. Now, when I work with this .xls file, and update the values in the respective cells with CF and then open the final version, all the formulas reflect the updated values. So, the bottom line is this issue is forcing me to use an older version of Excel files. I know that Excel 2007 files are built differently and utilize XML, but what I don't understand is why the recalculation is not happening automatically. Is there some 'underlying' function in the code CF is using that will force a 'recalculate' in the Excel 2007 file when I have it open in CF? Or is there some other solution someone has an idea for? Thanks! Dave Phillips ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351290 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
