Can Excel load data of volume 50 mb and analyse also by adding 25 mb more

[Bala SK]

Dear Madam / Sir

I would clarify few points mentioned below regarding the
development of MIS Application using Microsoft Excel 2000
or above version

Is it stable while trying to retrieve data of
volume say to start with 40 - 50mb per month and might
increase into 75 - 100 mb in 2 years into excel
formated sheet for analyzing

While you are analyzing this data of June 2004 say
50Mb, user wants to compare last year data of the same
month June 2003
either you can open a separate excel sheet
for June 2003, in this case two excel files of 50mb size
shall be opened

(or)

you can dynamically insert a column on the
exiting sheet of the user viewing and the size of the
existing sheet might go from 50mb to 70 mb
maximum


Is this the best way to do it, since users are more
comfortable with Excel and the volume of data can't be
decreased ??

(or)

better to use crystal reports or any other tool available
to take care of this as well graph display is also required

Hope you could understand my point and try to help me out,
how to go about it?

Thanks


With warm regards
Bala SK

 

[Andy Wiggins]

Some observations.

From what you say, Excel is not the right tool. Having said that, Excel does
have a large comfort factor for users and shouldn't be discounted. I have
found that my clients are willing to forsake the speed, bells and whistles
of a more "appropriate" tool simply to stick with Excel, a tool that they
know and have confidence in.

Have you thought about retaining Excel as the front end and using an
MsAccess backend to hold the data? Follow this link for some sample code on
using a database within Excel. http://www.bygsoftware.com/examples/sql.html

Bear in mind that you are limited to 65k rows. If your data growth suggests
that you might get near to or breach that limit then you will have to move
to something else, in which case do it sooner rather than later.

If you stick with Excel be prepared to use Excel 2003 (I haven't had an "Out
of memory" issue with 2003, yet) and throw lots of RAM at the problem. This
will be cheaper than the cost of purchasing and training users in a new
tool, but probably won't be as fast or efficient.

Is Excel stable and can it cope? Almost certainly.
Will it be slow? Almost certainly.
Will your users be happy with Excel and its drawbacks? Almost certainly.
Is Excel the correct solution? Almost certainly not!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"