If you haven't already posted the question in Excel Programming, I can
probably help you if you want. You are correct that using VBA is more
manageable in the long run - the constants and variables are more visible to
you and so can be altered more easily.
You'd need to make some decisions as to either what method needs to be used
to transfer which data in which direction (from excel to access/access to
excel). It may even be necessary for someone to see what you're doing in the
'active' workbook to generate your pivot table.
If you seek help elsewhere first, that's fine - if you get hung up or don't
get help you need, keep me in mind as a possible resource. You can reach me
via emal at (remove spaces) HelpFrom @ jlathamsite.com
"Jen" wrote:
Hi Shane,
Thank you for sharing your experience... makes me feel more comfortable!
I have been reading up in the meantime on how to export Excel-sheets to
Access, just in case ;)
http://office.microsoft.com/en-us/ac...CH063648351033
It is rather straightforward at least via the "Use a Macro"-procedure ...I
just fear that it is just a pain to update the paths and Named Ranges
manually...It does not have an Error-handling. My guess is that updating
such a database via a VBA-procedure is better manageable.
Just would love to see a simple example of how someone does it via VBA. I'll
better start a post about that topic in NG excel.programming.
Jen
"ShaneDevenshire" wrote in
message ...
Hi,
The file size limit for Excel 2003 and lower is 1GB. That has changed in
Excel 2007 but Microsoft has given various number: I have seen 2G and
"Limited by available memory. In Windows, (not 64 byte) systems the
available memory is 3 GB.
As for pivot tables - it is restricted by the same limits, but remember
that
the file size, when saved is affected by whether you choose to Save Data
with
Layout, a pivot table option. The pivot table cache is part of the Excel
file.
I have worked with Excel files containing very large cache's - which made
the file size 300MB. This was not a problem, although some things may run
more slowly.
--
Cheers,
Shane Devenshire
"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