Saving Excel (2003) File Causes Growing Size

[neuropunk]

Greetings,

Summary:
I essentially have Excel with a huge Macro that imports/exports (not
traditional excel import/export) various data and formats it (layout,
coloring, etc.) accordingly. The problem is that even when I do an
"import" process, and there's the same data on the backend, the
modified sheet (all imports in one sheet) will look exactly the same,
but when I save it, it'll grow.

I thought it was the 'used range', but I followed that trick (deleting
everything past end cell), and that didn't really help me.

This might help debugging. I have two approaches that I do formatting:

1.
I delete all the old data (clearAll). I then add everything and
'delete' relevent rows/cols and have it shift over. I don't like this
approach, since it causes any cell references to shift if linking in.
(I know I can use INDIRECT, but I'd like a more natural approach).

This approach doesn't grow with repeated imports/saving.

2.
I delete all the old data (clearAll). This method, instead of deleting
and shifting to align data, I will copy and paste, and then do a "soft"
delete of the irrelevant data (no shifting).

This approach causes growing with repeated imports/saving (even with
the same data on the backend).

Does anybody have any ideas?

If possible, I'd prefer to use #2, as it's a way of avoiding the usage
of INDIRECT, but the file is growing too quickly. As an example of the
growth of #2, in KB: 318 -> 398 -> 561 -> 889 -> 1500 -> 2780

Thanks!
Nam

 

[neuropunk]

I believe I found the issue.

I was originally importing the data as a QueryTable (which is what
Excel does when you go to Data -> Import External Data). The problem is
that after adding the information and shifting/playing around with it
(by copying and pasting as in options #2), I'd still leave the
QueryTable there, when I didn't really need it anymore. I keep on
saving, and the file would just grow.

After my importing process, I go through the task of deleting all
QueryTables, which seems to now stabilize my file size.

I want to say that when I was using approach #1, the delete (and
shifting) will somehow cause the QueryTable to kill itself, thus
stabilizing the file size. That is just pure speculation, so any
insight would be awesome. Doing #2 with my new approach, it will
actually cause the file size to shrink by a non-trivial amount and then
stabilize.

My Best,
Nam