excel (spreadsheet) alternatives

[hobgoblinvasya]

i'm not sure if this has been asked before, but my search didnt reveal any good info. thus my question is this:
for all you excel gurus, do you know any alternatives to using spreadsheets? (ex: anyway to recreate excel functionality on a web page, ms access, etc) that would allow the user to do pretty much the same things that you do in excel (ie. resize/add/delete/mask columns/rows, dynamic formulae, etc). perhaps even a commercial program?

the reason im trying to move away from excel is the fact that my file has become very big (>4mb), caused by the fact that i use alot of external references and one of the links points to a table of around 10000 rows by 40 columns.

also, needed is the ability to automatically update its data from an existing DB (reflecting any live changes) without the user having to manually update any source excel files.

basically, in the end, i need a file/program/web page that has an excel look and functionality and that fetches its info from a DB upon user input (ie. i enter a product number and i get its name, description, price, etc) and that it be small(ish) in its size and easy and fast to use.

there! lol ! im not sure it even exists but if any of you have came across similar needs and found various solutions, i would gladly listen to you

thanks in advance !

p.s. im fairly proficient in programming (VB, C#, as well as html et al.) so if you have examples of codes, etc, dont be shy

 

[SydneyGeek]

4 Mb isn't big, but linking to a large DB table will slow you down. You cuold try pulling just the data you need, using ADO; here is a link to a multi-part tutorial that should give most of what you need.

Denis

 

[hobgoblinvasya]

hey sydney

first of thanks for the reply and your suggestion. i was contemplating about using ado and i might very well go into that.

but, are there any other alternatives that you can think of, im open to all suggestions. im sure many of you have come to a point where excel ceases to be useful, thus im wondering what routes did you take to distance yourselves from it and towards other solutions.

sort of brainstorming of alternatives. (ie. cognos connections would create a live connection to mssql which would allow user to switch between excel and simple reporting). im looking towards suggestions of similar solutions.

as always any input is appreciated.

thanks

 

[Fazza]

As Denis wrote, ADO is one way.

It sounds like the approach currently used is not the most efficient. Excel can do what you've described without external reference formulas, it can automatically update its data from an existing database (reflecting any live changes) without the user having to manually update any source files.

A parameterised query table has this functionality without formulas and without VBA. Suggest you read up on these and you might find that Excel can already do what you want.

A little more sophistication can be achieved with VBA. And ADO then is another further option - VBA in addition to query tables might be OK without ADO.

HTH, F

 

[hobgoblinvasya]

thanks guys. thats what i though too.

as usual, your help is greatly appreciated :D and i think i will in the end go with some hybrid of ado/external links (to smaller files). this should reduce the size from 4mb to about 500kb, which is an acceptable number.

 

[Fazza]

With file size reduction a priority, you might consider some VBA on file closure to delete queried data and on file opening running the query to pull the data. Avoids having bulky data using memory when it doesn't need to. HTH, Fazza

 

[hobgoblinvasya]

unfortunately i can not do that since the file is sent to other clients that do not have access to the source data. thus is would result in either "#N/A" or a static file. im not sure they are willing to go either way. thus what i am thinking about implementing is searching for the data through vba via a UDF. once the data is in, i will try to trap the error that if the function returns an N/A i just put back what was previously in the cell. if not then look for new data.

the only thing im weary of is the fact that excel seems to recalculate much more often and i have no idea why. (volatile is set to off). will have to investigate a bit further

 

[Fazza]

OK. I realise I only vaguely understand what you are doing.

The approach I was thinking of, and it might be not suitable for you, was to have queries to do the work. So there might be no formulas in the spreadsheet. #N/A would never appear. There would never be any issues about the spreadsheet calculating because without formulas nothing would calculate. This is setting up the spreadsheet like a database. If it suits the work, this can lead to a much more efficient set up.

Another idea, and I hadn't considered this previously, if using pivot tables they could be set to not save the underlying data (to save on file size).

 

[hobgoblinvasya]

could you expand a little on your 1st sugegstion please

as far as pivot, no it will not work.

now a brief overview of what i am trying to achieve:
1. i have a source file that contains a whole lot of data.
2. a have another file that salespeople fill out to sell the product that are in the source file. the way they do it is simply entering the product code and all other info is fetched from the source file. once they are done they just send the result to the client. since the source file is around 15mb, just linking to it increases to resulting file size to at least 4mb.
3. now what i need is for salespeople to be able to continue to operate the way they are doing now, being able to copy paste sheets so that they can create as many layouts as they want in the resulting file (ie adding rows/columns, copying sheets, etc), and to be able to modify the file AFTER it is being sent to the client (ie. if the client wants to add/delete products).

thats pretty much it. hope it clarifies a bit more

 

[Fazza]

Well, it seems this is very much like the first replies you received. So, as Denis wrote, have some ADO to pull the data you want. Or as I mentioned, another (slightly different) implementation is with a query table. Either way, no formula links to the source file. I think if you want specific help then best to ask in the main forum: this one is for general discussion. Cheers, Fazza