More than 65536 Lines in Excel 2000?

Hi,

I use Excel 2000 to manage detailed data requests from our database of 120,000 records.  The problem ive encountered is that a lot of the CSV files i get from our database exceed 65536 lines of information, meaning its getting more and more hard to be able to run queries and data requests and be able to manipulate that data in excel 2000.  Therefore I was wondering whether there is a similar microsoft product which could handle more than 65536 lines of data as i would prefer to stay with microsoft as our lengthy procedures are tailored to Excel and rely on processes such as 'sort', 'filter', subtotal etc.....

So i guess what im saying is that im sure other spreadsheets probably have similar functions but im not sure my boss will let the procedures change as she relies on Excel a lot too and i don't think would want to change- therefore i will have to split my reports over mulitple workbooks and merge later- which as you can guess is very messy and far from ideal given the complexty of some of the requests i get and the timespan i often have.

Ive heard that there is something that exists in Web Tools in Windows 2000 that is essentially a spreadsheet that can handle many more lines of data but between me and one of our tech guys have not be able to locate this tool, which i was told is simply called 'spreadsheet'.

Can anybody help?

Greg

DubsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thedevilinthebelfryCommented:
MS Access - esceeds the 65536 record barrier
0
thedevilinthebelfryCommented:
And you can do most(all?) of the things you do in excel with Access.
0
thedevilinthebelfryCommented:
Also - depending on what this other database is - you may be able to link to the tables in it with Access - not necessarily a solution depending on what you want to do (or not do, more precisely)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ElectroNitzCommented:
What about using Excel's "External Data" or ODBC to query your database directly instead of using CSV files? As long as the end result of your SQL query is <65536 rows, it will work. If you must use CSV files, and must use Excel, what you can do is link the CSV files to an MS Access Database, and then use Excel to query the Access Database
0
thedevilinthebelfryCommented:
ElectroNitz is correct - both ways - as long as your queries stay within the limits of Excel.

I misread your problem... thought it was your queries that were exceeding the limits.
0
DubsAuthor Commented:
Thanks for the reply's- the problem is that often my queries will come out over 65536 rows- as we have at least 90,000 live records on your database which is growing everyday and some of the data requests are large and complex.  Although ive used Access i would prefer not to for this as I would like to just keep using two bits of software, our database has a direct link into Excel and this makes the processes a little more stream-lined, bringing Access into the equation will cause extra training and a change in the way we work- which we would like to avoid as we are always against the clock.

Does anybody know about this 'Spreadsheet' found in the web tools of Windows 2000?

cheers

Greg
0
thedevilinthebelfryCommented:
>>'Spreadsheet' found in the web tools of Windows 2000?

Never heard of it - does not mean it does not exist - though... maybe somebody else. Plus if it does exhist, it probably does not have all of the same abilities as Excel - at least if it is put out by MS. Why would they "give away" a product that is superior (same functions more records) to their commercial product?.

If your queries are consistently going over the limit though, I still stand by MS Access ... if you wish to stay within the MS line of products.

>>database has a direct link into Excel

You can do that with Access as well; and, IMHO, I also feel that Access gives the user more functionality... and I don't think that your average user would need too much training to perform queries or manipulate them (sorting, filtering, subtotalling).
0
ElectroNitzCommented:
I also could not find anything on "Webtools Spreadsheet".

I know you stated that you don't want to get away from MS, but I saw through a google news search that Quatro Pro limit is 1,000,000 rows, and Lotus 1-2-3 limit is 400,000.

If it helps, there are two ways to  automatically create multiple sheet workbooks.

XL: Importing Text Files Larger Than 16384 Rows (also works for 65536 by changing one number)
http://support.microsoft.com/default.aspx?scid=kb;en-us;120596

If you were using XL 2002, you could use the XML file format instead, and it can create multiple sheets.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;285891

0
DubsAuthor Commented:
Thanks for your replies-

I have been looking at Quatro Pro 9 as it looks v good- with a million lines limit i can't see myself exceeding that.  Problem its part of the office package with no option to try before you buy...

Ill check out those links and let you know how i get on.

Regards

Greg
0
DubsAuthor Commented:
I have been looking at other packages since im not au fait enough to try the macro option and think that if im gonna buy Excel 2002 i might as well spend the money on a superior spreadsheet provided they do similar things.  Quattro Pro looks like the champ but having no way to trial these applications im dubious to take the plunge and buy it outright.

So i have two options- buy 2002 and work out how to use xml, this isn't ideal as i will still be working with multiple sheets, or take the plunge with either quattro or lotus, unfortunatly on each respective site i can't find any free trials.

I must say Microsoft have dissapointed me here as the new excel looks ideal, i just can't believe they haven't made it bigger.

Cheers

Greg

0
thedevilinthebelfryCommented:
>>Microsoft have dissapointed me here

they do that sometimes...

since you are now leaning away from MS now - I would go with Quatro - like ElectroNitz said...

Good luck
0
ElectroNitzCommented:
Downloads - WordPerfect Office 2002 Trial Version

The trial version of WordPerfect Office 2002 installs the following core applications:

WordPerfect® 10
Quattro® Pro 10
Corel® Presentations™ 10
CorelCENTRAL™ 10
Paradox® 10

http://www.corel.com/servlet/Satellite?pagename=Corel/Downloads/Details&id=1047021685911

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbase118Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Points split between ElectroNitz and thedevilinthebelfry
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.