Excel 2003 Macro Converted to Excel 2007 Outputs Hugely Bloated File

**IanWF** · 02-08-2011, 09:10 AM

My employer is converting to Office 2007 from 2003. I am converting and testing Excel 2003 macros / Access 2003 databases to ensure they will work under 2007. One macro which under 2003 outputs a 76K .xls file, when converted to 2007 (with minimal changes) outputs a 126MB .xlsx file.

The output contains 7 tabs, 3 of which contain a single chart, the rest of the tabs have flat tables. There are no pivot tables, no shapes, and no images. The set up is exactly the same in each case other than the few changes made to enable the macro to run under 2007 (pulling data from Access 2007, outputting a 2007 format file instead of 2003 format, etc).

The computer is running Windows XP. The macro is contained in a separate spreadsheet and so is not in the output.

The save command is this:
xlWkb.SaveAs _
Filename:="C:\RptsMn\AST\AST.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Can anyone suggest why the file is bloating so much?

**romperstomper** · 02-08-2011, 09:29 AM

How do you extract the data from Access to the workbook?

**IanWF** · 02-08-2011, 11:03 AM

I use ADO. I send a string containing the SQL to a module. The recordset is opened like this:

      With adoRS
         .ActiveConnection = adoCn
         .CursorLocation = adUseClient
         .CursorType = adOpenForwardOnly
         .LockType = adLockReadOnly
         .Open (strSQL)
      End With

The connection uses the adoCmd active connection set up with this:

      With adoCmd
         .ActiveConnection = strCn
         .CommandType = adCmdText
         .CommandTimeout = 100
      End With

The "Provider" is using the correct string for Access 2007

The problem appears to be the charts. When I stepped thru the VBA, stopping and saving the file periodically, it remained small until the first tab with a chart was created, then it went up by 17MB! Each of the other two charts bloated it by an additional 17MB.

The spreadsheet is created dynamically each time, so I am not reusing an existing workbook or a template.

I found that by saving it as an .xlsb file, the file size dropped to 50MB, but that's still way too large.

At present I'm thinking I need to recode the portion which actually creates each chart. I understand that charting has changed somewhat in Excel 2007, and it may be the old code which makes the charts which is the problem, but I have yet to investigate this.

Thanks for your interest.

**IanWF** · 02-08-2011, 11:51 AM

I stepped through this macro again just now and discovered that it isn't the charts which are bloating this, but a table I add after the chart is created.

Prior to the table, and with a chart included, the file size is <20K. After one (flat, not pivot) table was added, the file size bloated to 42MB. The table is seven columns wide and four rows deep, and there was nothing in it when I saved it, merely the formatting (cell borders added at top, bottom, and sides, and cells formatted for text, numbers, etc.)

Apparently something I'm doing in that formatting is swelling the file size, but this only happens in 2007, not in 2003! The investigation continues.

**IanWF** · 02-08-2011, 12:14 PM

Well it turns out that it wasn't the table, but the formatting on the sheet as a whole.

In order to meet an end-user request I was clearing the sheet of the standard Excel gridlines and leaving it pure white, and this format was apparently being applied individually to every cell on the sheet.

This this is what was bloating it; as soon as I removed that one format, the workbook size shrank dramatically! I guess that's what happens when you unintentionally tell it to format a million plus rows and 32 thousand some columns, huh?

Thanks for your attention.