excel 2007 - web queries - bloated file size & memory

[wekkew]

Hi

macro-enabled sp which is built through vb/ie7 calling web queries. After the data has been pulled, all querytables are deleted.

Despite the above, the sp takes an age to open and is currently running at 409k in memory.

Anything else that should be deleted in addition to the queryTables? Seems to make no difference whether I enable data connections on opening or not.

thanks

 

[kjv1611]

There are many different variables involved in this one, it sounds like. You've got network latency, data size, etc...

Here are some things for looking at the actual Excel document, just to deal with your memory usage:
[ol][li]Object variables - be sure to erase any object variables in your VBA code when they are no longer needed - at the end of the procedure(s), at least.[/li]
[li]If you've got a lot of loops in your code, you might watn to look at changing some of those if possible - look for ways of doing things quicker/more efficiently[/li]
[li]If you're returning data you don't need, then you're using more RAM and resources, so make sure you're not returning more records and/or more columns than necessary.[/li]
[li]Keep in mind - It might just be necessary - if it's a large data set.[/li]
[li]If it ends up seeming to be too much for Excel, you can always upsize to using Access, and then spit out reports in Excel format from your Access Database.[/li]
[/ol]

Anyway, that's just some of my thoughts.

--

"If to err is human, then I must be some kind of human!" -Me