Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Word Document with many tables takes a long time to save

Posted on 2014-04-08
7
Medium Priority
?
916 Views
Last Modified: 2014-04-10
I am using VBA (Office 2010 version) to assemble a large document with many tables, some of which themselves have up to 10,000 rows (typically 4 columns only).
I've been through the recommendations for optimisation, implemented most of them, and assembly is quite fast. However, the save time at the end is up to 45 minutes for a document around 50MB.
Any ideas on how to reduce this, please?
0
Comment
Question by:MikeDiggins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 19

Accepted Solution

by:
Raheman M. Abdul earned 1200 total points
ID: 39985984
Check the document for extension. if it is .docx or not.  
.docx version are better with compression than .doc file

Increase virtual memory on a PC can reduce time:
Go to Start > Control Panel > Advanced tab > Performance Settings button > Advanced tab > Virtual Memory Change button.
Click 'Custom Size' and set Initial and Max to 4096.
Click the Set button. OK. Reboot and see how it goes.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39986014
While 50MB is a big Word document, I've never seen a Save operation take such a long time.  Are you sure the time is being spent on just the Save operation and not something else?  Depending on your algorithm, you might be spending the majority of your time concatenating strings, rather than doing I/O.

Check to see if Word is repaginating the document.  I've done some Word automation and had to put a wait into the process in order to give Word the time to repaginate.  The larger the document, the greater the wait.

Are you writing to a local hard drive or some file server storage (NAS, SAN, etc.)?

What optimizations have you implemented?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39986049
There may be some sort of corruption in the file. Try saving it in a different format (e.g. RTF), reopening it, and saving it again in Word format. This should force Word  to restructure the file.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MikeDiggins
ID: 39989681
Thank for your comments. I probably should have mentioned this is running on a Citrix farm so I don't believe memory is an issue. The document build is reasonably fast, its only the saving I have an issue with. I'll try the suggestions on pagination and .docx save format - both make good sense.

I'm showing progress messages, and the save operation is the only code between two of them, so I'm sure the time is spent on just that instruction - but good point.

Optimisations I'm suppressing are spelling, grammar, repagination, screen updating, minimising size of individual tables (the document eventually contains around 80 tables). THe document build is in Draft view. If there are other things I should try or any pet articles in this area, I'd love to hear about them.
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 39990557
* Make sure that your Citrix session has been allocated enough memory.
* Ask the Citrix and network admins to look at your process from their perspective.
* Start looking at the OpenXML SDK for .Net applications.  If we can't get this performance problem straightened out, then the next step is to look for alternative solutions and OpenXML eliminates the use of the Word application executable.
* You should run this application in visible mode to make sure it isn't doing something that you think it isn't (shouldn't be) doing.  I've experience Word automation scenarios where I've explicitly turned off a property only to find that my VBA statement had no effect. :-(
* You might need to add some DoEvents into your code to allow Word to catch up to your document.  
* Even though you have turned off repagination, it only means that background repagination is suspended.  When Word needs to repaginate, it will repaginate.  You will have to wait for the repagination to finish.  Repagination happens even when a document shrinks.

=============
You should probably do some testing to see if you can isolate which parts of the document creation process are causing you to wait.  How big does a table need to be before you can detect it?  Does the placement of these tables matter? (early, middle, or at the end of the document)
0
 

Author Comment

by:MikeDiggins
ID: 39991002
Thanks again - our timeframes didn't allow enough to look at OpenXML this time round but its certainly on the list.
Repagination doesn't seem to be the issue; I've asked VBA to repaginate before saving and it only takes a few seconds. The issue seems to be the format; saving as .docx takes about six minutes as opposed to 45, and a save of that document back to .doc form is still running after over 30 minutes.
The way I'm working the tables means that anything over 2,000 rows slows down but I think I have the measure of that. Calculating the number of rows and formating the table in advance cut the run time from several hours to 45 minutes when I tried it (with much less data than at the moment).
This won't just help this project, it will make a big difference to storage needs across the organisation. Thanks, everyone!
0
 

Author Closing Comment

by:MikeDiggins
ID: 39991012
Thanks for going the extra distance with some great suggestions, and in particular giving the reasoning behind them.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question