I occasionally produce macros that produce large sets of results data (e.g. workbooks that are 100's MBs in size). My current strategy is to limit the size of the resultant workbooks to around 600MBs by breaking the resultant data set up in to separate workbooks as in the past I've experienced problems with excel when files are larger than this. for example not being able to save them.
I have recently been thinking about learning how to store the data in a database e.g. in an access database created with ado or creating an sql database which I believe can handle even larger file sizes. Does this seem like a sensible way forward?
Note, I am using 32bit excel, I believe that moving to 64bit excel may go someway to improve the size of the files excel can handle but I can't guarantee the version of excel that I'd be using at work.