I’ve build xls with 15-20 SQL queries inside which were created using MS Query. The problem I have is that I cannot refresh all queries at once because I get msgbox titled “Microsoft Excel” with message “Not enough free memory”. The same situation if I try to execute queries one-by-one. I’ve found a partial solution to this by refreshing 3-5 queries than closing excel, re-opening it & refreshing next 3-5 queries etc. My guess is that MS Query uses some kind of cache and after that cache is down to 0 – no more queries could be executed. Closing excel flushes that cache – that’s why I can proceed with further queries. So does anyone know how can I increase size of that cache or how to clear it without closing excel?

What version of Excel are you using?

2000

Any chance you can upgrade it? 2000 only allows excel to utilize about 80mb of memory. If you could upgrade to 2003 the limit would increase to 1GB, which should hopefully be enough.

I'm not sure - it's company PC I don't know if there are any free 2003 licences. So no way to somehow flush that memory in VBA?

To be honest I'm not sure, I just knew the 2000 had a memory limit because it came up where I work. We don't use excel for anything more then some basic forumla so I've no experiance with VBA.

ok, thx for help. Didn't know about that 2000 limitation - I'l try to google it out.

Been a long time since I used MS Query but . . . you might solve the problem by creating 4 or 5 workbooks each with 3 or 4 queries set to refresh on open and return data to Excel (or not). Create a master workbook to retrieve the data from the pulling workbooks. If each Excel instance has its own memory allocation you should be able to work out a semi-automated solution for the memory problem with each wb doing some of the work and all the data ending up in the master wb.

I would give it a try using only one pulling wb with 4 queries and one master to retrieve. If you get no joy you haven't wasted too much time . . . =)

Or my favorite solution . . . use Access . . . =)