Originally Posted by
XLGibbs
Stanl , I would think you can calculate the rows columns and use math and R1C1 referencing to replace the refers to..adding the stored variables for rng.Rows and rng.Columns separate and simply adding those to the top left R1C1 address for the right half of the address..
That is exactly what I ended up doing. The fabricated recordset is something like:
oRS.Fields.Append("xlFile",200,100) ;fullname and path of Excel Workbook
oRS.Fields.Append("rName",200,50) ;Named range
oRS.Fields.Append("rSheet",200,30) ;parent (worksheet range found in)
oRS.Fields.Append("rAddress",200,30) ;range address, referstorange
oRS.Fields.Append("rRef",200,50) ;refersto
oRS.Fields.Append("rRefRC",200,50) ;Relative address, R1C1 format, based on
;Excel's ConvertFormula() function
oRS.Fields.Append("rCols",131,,2|32|64) ;number of columns in range
oRS.Fields("rCols").Precision =3
oRS.Fields("rCols").NumericScale =0
oRS.Fields.Append("rRows",131,,2|32|64) ;number of rows in range
oRS.Fields("rRows").Precision =6
oRS.Fields("rRows").NumericScale =0
oRS.Fields.Append("rData",203,100000) ;memo field to hold range data
;a 2-dim array in csv format
oRS.Fields.Append("rXML",203,100000) ;memo field range XML, so you end
;up with XML within XML - cool!
It is a bit overkill, but the speed of persisting and restoring is amazing even though the xml can be 10 times the size of the original workbook. If range data has to be re-positioned in an entirely different workbook, I have the necessary information to calculate.
To address questions concerning 'why not just keep it all in Excel?', you would be surprised how the ability to handle range data in this fashion can come in handy. Stan