Handling large excel files

[sundarvenkata]

Hi All,

I handle large excel files on a daily basis ( 20 - 40 megs) and
opening them takes a painful lot of time in my Thinkpad. Most of the
time I just need to work with only 1 or 2 work sheet in a file. Is
there a way I can make the Excel application load worksheets in an on-
demand basis?

Thanks,
Sundar

 

[Nick Hodge]

Sundar

There is no build in facility to do this and I suspect any code solution
will take just as long. What you could do if there are a regular two or
three sheets is have some workbook_close() event code to move those sheets
off each time so you have a fresh separate workbook to view these sheets but
you would need to be sure no links travelled with the sheets or that may be
just as slow.

Probably it's not worth it, but you may be able to speed up what you have by
disabling auto calculation, auto updating of links, etc, etc. The virus
scanner you run can also make a big difference to file opening (Norton AV
bad, Nod32 good), as can the speed of any network as Excel copies any
network files to the local machine before opening them, so all 20-40 meg has
to travel down the pipe before it starts to open.

I run 100+mb files with just data and they open and close very quickly, so
structure of workbook and formulas has much to do with speed.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk