Delete COM files in run time?

[Doug]

Hi,

I asked a question like this before, but noone could help!

I have some code that opens a spreadsheet with numerous
pivot tables that the code puts in it. Once the procedure
is finished and the spreadsheet is complete, the amount
of RAM Excel is using can be anything from 20mb to 100's
of mb depending on the size of the ranges the pivot
tables are looking at. The only way to dump this memory
from RAM is to quit Excel. Not ideal for me.

I now know that when you create pivot tables / pivottable
caches during VBA runtime, Excel is creating COM objects
containing the information in your pivots - these COM
objects are essentially the 'extra' memory I'm seeing
Excel taking up once the code/macro is complete.

Does anyone know if I can write some VB code that will
delete the COM files at run time?

One of my programmer amigos said that - "Problem is that
in VB script you do not need to explicitly create and
destroy your IDispatch interfaces to all the COM objects
it creates to do the job required. It will create them
automatically, use them but never destroy them. I am not
sure if you can destroy them if you haven't created them
explicitly."

I can see what he means, I can't see how I would refer to
the COM objects at run time in order to delete them , as
unless I was conventionally naming them myself how would
I know what they were called?

Any ideas would be a great help. The reason I'm asking is
that I've noticed a number of other people asking
questions that relate to this in this forum and without
decent answers! I think at the end of the day, this is
just a limitation of Visual Basic, you can release the
COM objects in c++ etc.

Thanks
Steven D

 

[Doug]

If anyone was wondering what this's about, or how to fix
it. You need to set the pivotcaches up as objects, and
clear out the objects after physically creating the pivot
tables on the spreadsheet. That frees up the RAM they
would otherwise still be taking up. It's a nightmare to
do...