Exceeding limits of Excel

[klamerus]

We've got an application that is very data heavy.

This has been done in Excel 2002, where we have sheets for the filtering and processing of data to prepare it for optimization with a tool called GAMS.

We've hit limits on this with the number or rows with which we can work given that each row can have 20-35 formula (for computing values). We have one sheet that should have 50,000 rows on it. Excel (or probably the VBA behind it) simply can't handle the information. We have VBA arrays that we think we've exceeded the limits of as well. Excel has simply run out of steam.

At the same time, the interface of grids with data and formula that users can enter is exactly what we need.

We're looking at what we can do to size this up. MS Access isn't working either. It appearently works with internal data structures that leave it growing and growing until it stops working. The Excel itself is around 100 MB in size.

We're wondering what other people have done perhaps in a situation like this.

Does Excel 2003 not have these limits?

Can we write something in VB.Net and make use of Excel grids (and yet still have access to formula).

Are there any data grids that we can use in VB.Net that support formula and huge #s of rows and stuff that will blow the doors off of Excel's sizing limits (and processing limits)?

We really would like to hear of options and what others have done?

 

[SkipVought]

Hi,

Your application is not necessarily DATA heavy, but FORMULA heavy.

When the user enters data in the interface, does that cause formulas to recalculate?

Can you isolate the rows or columns that are affected by calculations? In other words, an entry might cause 100 rows and 10 columns to calculate, so why calculate the entire sheet?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fredericofonseca]

but answering to your other question, yes there are grids out there that will bypass those limits, but the formulas you have may not all be supported.

look in

http://www.vbxtras.com

and

http://www.componentsource.com

and search for grids and try them out.

But Skip has probably hit your problem.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fneily]

You state that Access isn't suitable either. Really? After normalization, your 50,000 rows would probably be a tenth of that in Access. First you must learn the protocols of a RELATIONAL database management system before saying it's not suitable. The "growing and growing" you're talking about is probably from someone doing alot of deletions which is probably from bad database design.
I'll bet you have alot of duplicate data in your Excel. That's the nature of Excel. Access doesn't have duplicate data thus is quite compact compared with Excel. By the way, you do not copy an Excel spreadsheet into Access and then stop. You must normalize. One spreadsheet will usually create two or more tables in Access. AND NO DUPLICATE DATA.

 

[xlbo]

I would agree with fneily on this. Excel is not a database and can therefore be very resource hungry whjen made to try and act like one

50k+ rows in excel is bad enough if you're not doing much with it - to try and use a lot of calcs as well.....just asking for trouble

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[klamerus]

We are not trying to use Excel as a database.

The spreadsheet is a vehicle for "prepping" data for submission to a modeling/optimization tool. It so happens that creating the input data takes quite a bit of work as does prepping it.

The spreadsheet is the tool for this final step, after which we submit the work. Later, we analyze the results in Excel again.

Excel is being used as a GUI/application (at least as best we are able to do so). The problem is that it's been added to and added to, etc. The benefit of Excel has always been that it's quite flexible. The scientists are able to modify the data, the formulas, etc. all without the assistance of IS.

 

[klamerus]

It's this last bit, which is why MS Access is an issue.

We're quite familiar with Relational databases. I've used them for > 15 years going back to vendors like Ingres, etc. all long gone.

Unless we can use MS Access in such a dynamic way (the end users using it I mean), then it won't work for us. I don't know that it isn't this flexible, but I do know a pure database (like SQL Server or Oracle) isn't.

 

[xlbo]

If excel is the 'front end' why on earth does it need 50k rows of data ????

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question