Excel file goes from 12 to 38 MB after "Get External Data"

K

klgrube

Hi

In an excel spreadsheet, there are three worksheets that are populated with data in perhaps 20 columns and up to 3000 rows, (along with several other worksheets in the same spreadsheet) The spreadsheet is 12 MB with the hard-coded (hand keyed) data sitting in each of these three worksheets. We decided that we wanted to import the data to these pages from SQL Server rather than maintain it manually, so we wrote a macro that clears the three worksheets and then executes the "Get External Data" function for each of the three worksheets. However, when we run the macro and it replaces the existing hard-coded data with the results of the query, although the new data appears to take up no more space than the original data, the spreadsheet suddenly triples in size, even after you save it (or do a save-as to the same filename.) Any reason why this should be happening? Anything I can do to prevent it? The text of the macro itself is very small. Is there a different way I should be clearing these worksheets? Why is the same amount of data taking up three times the space

These spreadsheets are accessed remotely across a citrix network, and even at 12 mb, they take a while to open and save. We really need to resolve this sudden change in file size. Any suggestions

Thanks
Karen Grub
[email protected]
[email protected]
 
G

Gord Dibben

Karen

Excel has a habit of including rows and columns that were once populated but
no longer are in service.

Where does Excel take you on a sheet when you hit CRTL + END?

If far below and/or to the right of your actual data range, you can reset the
used range.

See Debra Dalgeish's site for info and instructions.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Gord Dibben Excel MVP
 
D

Dick Kusleika

Karen
The text of the macro itself is very small.
Click to expand...

Post the macro.
Is there a different way I should be clearing these worksheets?
Click to expand...

It depends how you are adding the query tables. Are you adding querytables
every time you run the macro? Are you only running the macro once, or
multiple times? I've seen this happen where a macro is run like

Sheet1.Cells.ClearContents
Sheet1.QueryTables.Add etc...

In very cases should you be adding a querytable in a macro. The preferred
way to do it is to set up a query table manually and change the properties
of that table through the code as opposed to trying to recreate the table
every time. The problem with the above code is that any existing query
table is not deleted. Clearing the cells doesn't delete the query table and
those old query tables can build up.
 
Top