asked on

Word Document with many tables takes a long time to save

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?

ASKER CERTIFIED SOLUTION

Raheman M. Abdul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

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?

GrahamSkan

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.

MikeDiggins

ASKER

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.

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeDiggins

ASKER

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!

MikeDiggins

ASKER

Thanks for going the extra distance with some great suggestions, and in particular giving the reasoning behind them.