Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading range w/ 255+ characters throws error
Hi, I need to read an Excel range into an array which I then use to modify
it's data and formulas. I then write the modified data back to Excel. This code: shtValues = (System.Array)row.Formula; fails when it encounters 255+ characters in a cell. My code is below. Please tell me if there's a way to work around this. try { // Creates and initializes a new CollectionBase. this.oSheetRangeCollection = new RangeCollection(); System.Array shtValues=null; int iLastCol = ws.UsedRange.Columns.Count; int iLastRow = ws.UsedRange.Rows.Count; for (int i = 1; i <= iLastRow; i++) { Excel.Range row = (Excel.Range) ws.get_Range(ws.Cells[i, 1], ws.Cells[i, iLastCol]); //insert the data into the object[,] one row at a time shtValues = (System.Array)row.Formula; this.oSheetRangeCollection.AddRow(shtValues); nav.ReleaseComObj(row); } } |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading range w/ 255+ characters throws error
The limit of a formula array is 255 characters, or a even slightly less.
If the formula includes sheet names you might be able to cheat slightly by temporarily renaming the sheet name to a single character, applying your formula with the single character names, then renaming the sheet. Having done that you would not be able to programmatically read the 255+ array formula until you again reduced the sheet names. Regards, Peter T "jarabe28" wrote in message ... Hi, I need to read an Excel range into an array which I then use to modify it's data and formulas. I then write the modified data back to Excel. This code: shtValues = (System.Array)row.Formula; fails when it encounters 255+ characters in a cell. My code is below. Please tell me if there's a way to work around this. try { // Creates and initializes a new CollectionBase. this.oSheetRangeCollection = new RangeCollection(); System.Array shtValues=null; int iLastCol = ws.UsedRange.Columns.Count; int iLastRow = ws.UsedRange.Rows.Count; for (int i = 1; i <= iLastRow; i++) { Excel.Range row = (Excel.Range) ws.get_Range(ws.Cells[i, 1], ws.Cells[i, iLastCol]); //insert the data into the object[,] one row at a time shtValues = (System.Array)row.Formula; this.oSheetRangeCollection.AddRow(shtValues); nav.ReleaseComObj(row); } } |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading range w/ 255+ characters throws error
If I were using VBA...
Take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. jarabe28 wrote: Hi, I need to read an Excel range into an array which I then use to modify it's data and formulas. I then write the modified data back to Excel. This code: shtValues = (System.Array)row.Formula; fails when it encounters 255+ characters in a cell. My code is below. Please tell me if there's a way to work around this. try { // Creates and initializes a new CollectionBase. this.oSheetRangeCollection = new RangeCollection(); System.Array shtValues=null; int iLastCol = ws.UsedRange.Columns.Count; int iLastRow = ws.UsedRange.Rows.Count; for (int i = 1; i <= iLastRow; i++) { Excel.Range row = (Excel.Range) ws.get_Range(ws.Cells[i, 1], ws.Cells[i, iLastCol]); //insert the data into the object[,] one row at a time shtValues = (System.Array)row.Formula; this.oSheetRangeCollection.AddRow(shtValues); nav.ReleaseComObj(row); } } -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading range w/ 255+ characters throws error
"Dave Peterson" wrote in message
If I were using VBA... Take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. I forgot about that, yes a cunning trick (also a clever calendar formula in the demo) "Dave sent me a solution to this problem that's shown below." Don't suppose you know which "Dave" ...? Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading range w/ 255+ characters throws error
I don't think it was me.
Peter T wrote: "Dave Peterson" wrote in message If I were using VBA... Take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. I forgot about that, yes a cunning trick (also a clever calendar formula in the demo) "Dave sent me a solution to this problem that's shown below." Don't suppose you know which "Dave" ...? Regards, Peter T -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code window changes case and throws error | Excel Programming | |||
Find Throws Error 91 | Excel Programming | |||
How do I perform a certain function if VBA throws up an error? | Excel Programming | |||
Range.Formula in Excel 2003 gives Error reading formulas just below 1024 bytes | Excel Programming | |||
Format statement no longer throws an error | Excel Programming |