Growing file size

Hi,

I have created (Excel XP) a "report generator" which grows oddly in size.
The basic procedure (this is supposed to run twice a week or so) is:

1. fetch data to a temp sheet from different dBase files using queries
2. calculate something based on this data to the main sheet
3. clear the temp sheet and goto 1
4. after everything is calculated, save the front sheet as a separate report
file

Basically everything runs fine, the generator file is around 120KB and the
report file around 80KB. However, the generator file grows in size when more
reports are generated. After around 10 test runs the size got up to 750KB. In
the long run, this is going to cause problems. One solution could be to
publish the generator as read-only (?), but I am willing to find the solution
to the problem first.

I've searched the newsgroup, tried all possible solutions and found nothing.
The generator obviously saves something "to itself" after every run, but what
on earth could it be?

Thanks in advance!

-Anssi K.

I had the same problem with queries. The Temp sheet kept filling up with
references to old queries. Another problem could be in the way you clear the
Temp sheet. Excel remembers the "Used Range" of a sheet even though it
appears empty. Hit Ctrl+End on that sheet to find the lower right corner of
the Used Range. The answer to both problems is to not keep the Temp sheet.
Add the sheet to do your queries and delete it when you are through with it.

Mike F
"AK" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have created (Excel XP) a "report generator" which grows oddly in size.
> The basic procedure (this is supposed to run twice a week or so) is:
>
> 1. fetch data to a temp sheet from different dBase files using queries
> 2. calculate something based on this data to the main sheet
> 3. clear the temp sheet and goto 1
> 4. after everything is calculated, save the front sheet as a separate
> report
> file
>
> Basically everything runs fine, the generator file is around 120KB and the
> report file around 80KB. However, the generator file grows in size when
> more
> reports are generated. After around 10 test runs the size got up to 750KB.
> In
> the long run, this is going to cause problems. One solution could be to
> publish the generator as read-only (?), but I am willing to find the
> solution
> to the problem first.
>
> I've searched the newsgroup, tried all possible solutions and found
> nothing.
> The generator obviously saves something "to itself" after every run, but
> what
> on earth could it be?
>
> Thanks in advance!
>
> -Anssi K.
>
>

"Mike Fogleman" wrote:

> I had the same problem with queries. The Temp sheet kept filling up with
> references to old queries. Another problem could be in the way you clear the
> Temp sheet. Excel remembers the "Used Range" of a sheet even though it
> appears empty. Hit Ctrl+End on that sheet to find the lower right corner of
> the Used Range. The answer to both problems is to not keep the Temp sheet.
> Add the sheet to do your queries and delete it when you are through with it.

Thanks! I thought that Selection.ClearContents would do the same as delete,
but it seems that it leaves the references (etc) there.