Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic DOORS (steve huntington)
Decrease font size
Increase font size
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
Search Topic Search Topic
Topic Tools Topic Tools
Quick Reply Quick Reply
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
 21-Jun-2007 18:54
User is offline View Users Profile Print this message


David Pechacek

Posts: 674
Joined: 5-Dec-2006

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
Report this to a Moderator Report this to a Moderator
 22-Jun-2007 14:04
User is offline View Users Profile Print this message


Andrew Tagg

Posts: 151
Joined: 26-Oct-2004

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
Report this to a Moderator Report this to a Moderator
 22-Jun-2007 14:11
User is offline View Users Profile Print this message


David Pechacek

Posts: 674
Joined: 5-Dec-2006

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
Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic DOORS forum.
There are currently 2 users logged in.
The most users ever online was 15 on 15-Jan-2009 at 16:36.
There are currently 0 guests browsing this forum, which makes a total of 2 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.