Excel 2007: Worksheet.Move functions differently to Excel 2003, wh

[Bernd Wechner]

I have been trying to work out why in migrating some of my Excel code from a
2003 platform to 2007 platform I saw a file blow out from 300KB to 11MB!

By examining the Open XML format contents I can trace the blowout to a
particular set of sheets which I moved into this sheet from another using the
Worksheet.Move() method.

On the 2003 platform this just moves the sheets from one workbook to another
as I intended.

On the 2007 platform it sort of does the same except now the sheets in the
target workbook seem to max out in rows and columns. That is the sheet is
saved as a 75MB XML inside the .xlsm file I'm using whereas comparable sheets
are like 5KB. Trying to look inside such a huge XML file is hard, and slow,
but clearly it is full of empty cell specifiers. The Move in 2003 just moves
the sheet and in 2007 it seems to recreate the sheet somehow.

Reading the documentation:

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.move.aspx

I see no new arguments to the Move method in 2007 to specify or alter this
behaviour. Is this a bug in Excel 2007?

It looks like I may need a work around somehow perhaps by copying just
specified ranges from the source workbook to the target (as I only use a
modest part of the top left of the sheet after all).

I'm wondering if anyone has any light to shed on this experience and if
there is in fact a new feature in Excel 2007 which has this as a surprising
side effect? Or if this should be somehow registered with MS as a bug?

 

[OssieMac]

Hi Bernd,

Try selecting all the rows below your data and Clear All (You will find
Clear all in the Editing Block far right of Home Ribbon. Click the drop down
on Clear icon and select Clear All).

Repeat above for all columns to right of your data.

Save the workbook and close and re-open it.

I have had mixed results. I will be interested in your results. I suspect it
has a lot to do with formatting entire rows and columns and the xl2007
worksheets are massive in size. Clear All gets rid of the formatting but as I
said, I only suspect that is the problem.

Note that deleting the unused rows and columns does nothing. Again I suspect
that this is because the new rows and columns inherit the formatting.