I'm continuing to get a "CellExceptions" thrown by the component when I'm pushing a string form a forumla. This is only happening when the string is very long or has many arguments for the function that I'm trying to call.
I've chased this down to the number of arguments. In Excel 2003, the limit of number of arguments is 30, while in Excel 2007 it is 255. My question is how does the system know which format you are working to when you create a blank workbook in memory? How are the different versions being handled?
In my application, if I capture the string that forms the formula and handle the exception to not place the summary ( =SUM(A1,B2,C3, ..... AB31)) and apply this same formula to the final spreadsheet which is saved to disk (Excel 2007), it works just fine. But if the file format is 2003, it does not work fine, due to the limitiation of 30 arguments.
It seems as if the component is not handling the versions properly, or I simply just don't know how to get it into one mode or another depending on desired format level.
The following is the method that I'm creating the new workbook.
' create the basic workbook
Dim workbook As Workbook = New Workbook()
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = worksheet.Cells