![]() |
Telelogic DOORS (steve huntington) | ![]() |
new topic :
profile :
search :
help :
dashboard :
calendar :
home
|
||
Latest News:
|
|
Topic Title: Speed up Excel Export Topic Summary: Here's How to turn off Excel Undo Feature Created On: 21-Jun-2007 18:54 Status: Post and Reply |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() |
|
First off, this requires modifying the registry. If you are not comfortable with doing this, don't use these functions.
Second, if you cannot modify the registry on your PC, you cannot use these functions. I work in a corporate environment for a government contractor like many of you and am able to make the necessary modifications, so I hope you can. Third, you must turn off the Undo feature before launching Excel or the setting change will not be in effect. Subsequently, turn it back on after you have closed Excel (you can change it back with Excel open, but don't for safety's sake). How it works: Excel's undo feature works off a registry setting that specifies how many items of undo information are stored. Upon install of Excel, the key I am modifying does not exist and the default value is 16. Once created, I set this key to 0 to turn off the undo feature. Now for those of you who might have had this key created already and set to some specific value, don't worry, I save the old value. If there was an old value, when the call to restore it is made, I use the old value if it exists or the default. Also, I could only find information on this registry key for Office 2000 through Office 2007. If you use Office 97, look at the registry values I use and check your registry for the appropriate key, then modify the code to suit your needs. Please also send me the keys Office 97 uses so I can add it to my own. I added this code to Michael Sutherland's Enhanced Export to Excel and noticed a significant speed increase. Whereas before after 300 or so objects the export slowed down significantly, now it takes until the 2000-2500 objects before it starts to slow down. And by the way Michael, if you could email me I would appreciate it. I tried sending you an email regarding other changes to your tool, as well as an error I'm getting, and keep getting my emails kicked back. I believe it has something to do with the Galactic Solutions website being down. See code below. ------------------------- David Pechacek AAI Services Textron dpechacek@sc-aaicorp.com David.Pechacek@gmail.com Edited: 21-Jun-2007 at 19:02 by David Pechacek |
|
![]() |
|
![]() |
|
Excellent tip David, I've been trying to figure this out for some time with no success after I stumbled upon the similar problem with Word exports slowing down (there is a thread here on that somewhere). Also many thanks due to Michael for his excellent enhanced excel script. I have spent a lot of time raiding that script to find out how to do things like add worksheets, bold text, or add background colours to excel cells. Thanks to that I can now use overnight processing to produce very professional looking metrics spreadsheets which I post on our company web-pages.
------------------------- Andrew Tagg Thales Air Systems, Melbourne Australia. andrew.tagg@thalesatm.com |
|
![]() |
|
![]() |
|
Well with Word theres a lot more you can do. I haven't delved into Word as much as I have Excel but from what I've seen, you can do things like disable the AutoFormat options which you can't do in Excel (someone please kick Microsoft in the nuts for that). I also have Excel functions that disable things like AutoCorrect, Error Checking, and Auto Recover. They don't seem to give nearly the speed increase though that this does though.
I also have torn through the Enhanced Excel exporter and reused a lot of it. If you missed it, I posted a whole list of functions for Excel including some that Michael's script doesn't do (since he focused more on writing to Excel than reading from it). I have added more yet since posting that. Unfortunately, I apparently now have to go through an approval process to be able to give out code snippets that were developed on company time. I'm still figuring out what that process is so once I do, I'll get some more stuff up. Anyway, glad someone can use this. Feel free to email me with any questions. And someone who uses Office 97, please post up the registry entries (if they exist) for this so I can add them to my code. ------------------------- David Pechacek AAI Services Textron dpechacek@sc-aaicorp.com David.Pechacek@gmail.com Edited: 22-Jun-2007 at 14:13 by David Pechacek |
|
![]() |
Telelogic DOORS
» DXL Exchange
»
Speed up Excel Export
|
![]() |
FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.