Saving large spreadsheets takes a long time.

[Eric Sten]

I hope somebody can help answer my questions...

First off... the way I understand Excel saves files is
that Excel saves to a temporary file first and when the
temporary file is sucessfully saved, the original is
deleted and the temporary file is renamed to the original
file name.

Now with a very large file, 400MB+, creating this
temporary file, deleting the original, and renaming the
temporary can take a long time, even with a higher end PC
(P4, 2.0 Ghz w/ 1GB RAM). Correct?

Now I have a user who is doing the above and is a
bit "miffed" about the amount of time it takes to save
the file. Is there anything that can be done to decrease
the time it takes? My boss thought maybe if Excel can
create the temporary file at the start and update it as
changes are made it would make the saving quicker. From
what I have seen, there is no option for doing this,
correct?

I know that this may be a bit confusing, please respond
for any clarifications.

Thank you,

Eric Sten
Desktop Systems Analyst
Ohio National Financial Services

 

[Mike A]

The reason for the temporary file is that you cannot overwrite an open
file. Rather than close your file and overwrite it (and hope for the
best), Excel uses this temporary file method. Even a direct save to a
400MB file would take a while. 400MB is 400MB regardless of the
filename. Excel only writes the data once per save. File deletion
and renaming involve changing a few bytes in the File Allocation
Table. If this file is on a network, a 100mbps ethernet link will
take 40+ seconds to transfer the data, not counting network overhead.
Even saving locally involves paging in/out of the swapfile, disk head
movement, etc.

There may be something wrong with your file to make it so big. If you
selected an entire sheet or group of sheets then applied formatting to
the cells, the file will get extremely bloated. I saw a 43K file go
to 2.7MB this way (with no data added).

What is in this file? Pictures, charts, etc. will add to a file
quickly. Maybe the file can be split into smaller, linked files.

If you truly have 400MB of data in that file, you should probably have
that running on a database server. That way you could connect to the
server from Excel and just retrieve the data you need to work with.


-Mike




Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Dave Peterson]

I don't think you can change excel's behavior for saving.

And I've never, ever seen a workbook that even approached 100Meg, much less
400Meg.

Are you sure excel is the best application for this?

If you are, is there anyway that you could split it up into smaller workbooks?
(maybe 100 smaller workbooks!).