Hi,

I initially posted this on a Reporting Services thread, but was adviced to
place it elsewhere. So here it is....

I am experiencing some strange Excel behavior with regards to file size
expansion, and in need of support from MS to see if they have a solution for
it. In essence, an Excel workbook's size gets bloated when a copy / paste
special is performed on cells which contain formulae.

Try the following:
1. Open a Workbook (and add some formulae to the first row)

2. Copy the first row (with the formulas)
3. Select all the rows inserted in step 2, right-click and select "Paste
Special". The options for Paste Special should be
Paste All, Operation None, No Transpose, No Skip Blanks
4. Once pasted, save the workbook (call it "Original Workbook") and close
it.
5. Make a copy of the workbook saved in step 5. Call it "Copy Workbook".
6. Open the copied workbook in step 6, save it and then close it.



You will notice a difference in file size between the "Original Workbook"
and the "Copy Workbook". Depending on the number of rows inserted and pasted
over, this difference could be very significant. In one trial we pasted
about 45 rows, and noticed the following:

Size of Original Workbook = 264KB
Size of Copy Workbook = 226KB
Difference = 38 KB (or about 17 %)

Comparing the file contents using a binary file comparer (like the one in
Source Control) revealed that there were certain chunks of data in the
Original Workbook that was not present in the Copy Workbook. Furthermore,
this data was not in a contiguous section of the file but rather interlaced
between other data.

Also, if the row that was copied in step 3 (that is the template row), did
not contain any formulas there was no difference in file size between the
Original and the Copy.
Additional Information: The behavior occurs only when copy and pasting
excel cells which contain formulae using named ranges.
That is, the Formula is of the form QTY_RANGE * PRICE_RANGE, instead of
using cell references like A1*B1



Thought:Can this have something to do with formulas, perhaps Excel tries to
recalculate all the cells and in the process leaves some scrap information
that gets saved with the file on the first save. Subsequent saves removes
this "scrap" data.


Any advice is appreciated.

Thanks,

Tim



--------------------------------------------------------------------------------
Systems Engineer

--
..NET Developer