Jen,
I can't answer your questions with hard numbers. In theory the maximum file
size should be based on what your operating system can handle and store and
retrieve. In reality it is probably going to be smaller than that. A lot
regarding responsiveness and the ability to deal with all of the data will be
governed by the power of your CPU, the memory installed in your computer and
how much free space is available on your hard drive.

It is possible that the apparent loss of functionality by PivotPlay may be
due to memory constraints and to a lesser degree by CPU speed: it may just be
taking a lot of time to rearrange the data and find places in memory/on the
hard drive to store and retrieve it.

Two things I'd recommend in light of your concerns:
#1 make regular backups of all (both?) files concerned. And you have to
exercise a little caution even in doing that - before backing the files up
after working with them and closing them, open them back up and use Save As
to save the backup copy. This helps assure you that your backup is of a
working version. If you cross some limit somewhere and suddenly you can't
access the file, if your backup was based on that file it would be
conceivable that the backup was similarly inaccessable.
#2 (after backing up these files to another location, hard drive, CD, etc)
defragment the hard drive you work from. Getting these large files into a
more orderly condition will help with data retrieval, especially from the
45MB file if it is now fragmented.

For better performance (not something you mentioned, but a consideration
anyway) you might consider increasing the amount of physical RAM in the
system. With files that large I'd consider 2 or 3 GB of RAM installed to be
helpful.

As for moving it all into Access, with the amount of data you have (6 * 15 *
35000 = 3,150,000 individual data items) it is definitely something to give
consideration to. The maximum file size in recent versions of Access is 2GB
which is also the limit on table size - but you can build even larger
databases by placing tables into separate files and linking it all together.
But this shouldn't be a factor in your case: 45 or even 145 MB is a small
fraction of 2GB.

I wish I could provide more concrete information for you, but I simply
cannot. The Excel Help topic "excel specifications and limits" does not
mention a maximum file size, although it goes into detail on other limits (#
of sheets, pivot tables, etc), and many of those are "limited by available
system memory". You may want to read through that topic on your system to
see if you think you're getting close to any of the limits it mentions.

I will repeat one thing: it sounds like this file is very important to you.
The amount of data you have alone probably represents a considerable
investment of time and effort. With that in mind, I repeat my recommendation
to develop and adhere to a good backup strategy. To fail at the most
inopportune moment is typical of a computer - to have a recent backup of
critical files at those inopportune moments is simply divine!

"Jen" wrote:

Hello There,

I am using Essbase to retrieve data from our DB... etc etc. It works as an
Excel-add-in and dumps my data on my workbook.

The dumpdownload I store in 6 different Ranged Names on 6 different
sheets... each 15 columns wide, and 35000 rows deep.
This results in a 45MB-size file!

I use MSQuery to "join" my data from this file and upload it into a Pivot
table into another file. (DataPivotGet External Data).
As such I do not have any problem with the size of this file ... as I do not
have to work with this file actively. It stays closed and get accessed with
another file via MSQuery.

I don't see many ways to reduce the filesize of the data-file. I´ve re-set
the last cell on every sheet too. When I take a blank Excel file and file
the same ranges with a number, I get the same file-size.
So far this is working without any significant problems... (*) but I am a
bit worried that I will run out of luck rather sooner than later.
(I see postings from people who get worried already with filesizes of 2 MB
....?)

My File with PivotTable has just 1 Pivottable, no charts, no other
calculations and is approx. 18 MB!
BUT it works fine I do not have any problems so far. The PivotCache
MemoryUsed is approx 7.5MB ..i've no idea what that means exactly....
The files are newly build (no old cluttering whatever), contain no
VBA-code,...

I just would like to know what file-sizes Excel can handle?
Will the big Datafile be a problem at a certain level? (by preference I
would like to double the amount of data in it for more comparative analysis
but I am a bit scared)
...it is a passive file. Or the PT-file?
Are there ways I could upload my data from Excel into Access eg.? ... Other
solutions?

(*) Ron Coderre has an Add-in PivotPlay (super!, thanks Ron) that allows you
to automatically rewrite the queries ... but this functionality does not
work anymore, which I think is simply due to the filesize. It works great
with smaller Files.

Jen