I have built a dashboard type of excel tool that has data connections to 35 other excel files. The excel files are stored on a sharepoint site and users can go update the status of their task. I can use the refresh all and pull in updates. This has worked great for 3 years. This year I started running into all kind of memory errors. I included a URL at the bottom that really sounds like the problem I am running into. I don't understand how or why I am exceeding excel's memory/ability. The files I am reading are only about 30k each. I have tried about 25 different items to fix the problem and haven't hade much luck. A few of the main items I have tried:

changed all sheets formatting to 1 font 1 color.
upgraded os to windows 7 - 64 bit and upgraded office from 07 to 2010
changed the refresh to do 1 sheet at a time

when I watched taskmanager with doing the refreshing something odd caught my eye
there are about 4 connections that are causing the problem, most of the refreshes take 2 seconds and memory/cpu barely move, but those 4 cause memory to spike from 300 MB or so to nearly 1.6 GB and cpu goes from 3% to nearly 25%. There has to be a better way of doing this or at least something I can do to get to root cause. The only way I can do the mass updates/refreshes now is to boot to safemode with networking enabled. Thank you for reading my long post I greatly appreciate any thoughts you might have!!!


http://www.decisionmodels.com/memlimitsc.htm