I reproduced your steps exactly and the Excel report opened without any problems. The only differences are: 1) I used only 3 projects, plus the resource pool. 2) I’m running Excel 2010, not 2007. So the answer is yes, it should be possible to run a visual report from a common resource pool.
So now the trouble shooting begins! Some suggestions…
Try again with only a few projects attached to the resource pool. If it works, it could be that you need more memory (RAM) in your computer. Project gets pretty memory intensive doing this type of thing.
Though I don’t think this is the problem, you might try this on another machine running Excel 2010, or upgrade your own machine.
Check to make sure you have the latest Service Pack for Project / Office. (Windows Update or other method).
