Word Automation - Performance Problem

Posted on 2011-09-30
Last Modified: 2012-05-12
Hello, Experts,

I have just begun creating a report in Microsoft Word (2000, yes I know it's old) from data held in a VB .Net application (v2005, also becoming dated).

Things were going well until I started the second page of the report.  The information here is formatted as a table, and I am populating it row-by-row with a loop like:

        For intColumn As Integer = 1 To intColumns
            P2TableRow.Cells.Item(intColumn).Range.Text = objaValues(intColumn - 1).ToString
        Next intColumn

After inserting a performance counter, I find that it is taking greater than 150 msec to populate each cell.  Formatting statements such as:

            P2TableRow.Cells.Item(intColumn).VerticalAlignment = ...
            P2TableRow.Cells.Item(intColumn).Range.Bold = ...
            P2TableRow.Borders.Item(Word.WdBorderType.wdBorderTop).LineWidth = ...

also take an inordinate length of time to execute.

Can anyone offer suggestions as to what might be causing this poor performance, or how to populate the table more efficiently.

Question by:omegaomega
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
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 500 total points
ID: 36895373
A lot of things.

Everytime you have a dot (.) in a command, you have a communication from your .NET application to the Interop to Ole to Word and back the reverse way. A lot of intermediaries.

In P2TableRow.Cells.Item(intColumn).VerticalAlignment, that is 3 calls (3 dots), that go through 3 dll/applications twice (back and forth). 18 communications between different components. In some cases, conversions need to be done because the format of some types of data (such as dates) is not the same in .NET as it is in COM (the standard running under Word).

There are many ways to go around that.

The best one is to have a VBA macro in the document or the template used to create the document, and call the macro from .NET, passing it the necessary parameters. Instead of having 20 lines that all start the back and forth dialog, you have only one.

<WordApplication>.Run("<MacroName>", "foo", True, False, 23, 3, True, vbRed)

Instead of passing each String individually as I do here, you could also pass them in one shot by having the first parameter of the macro as an array.

There is but one call through the components, most of the job is done inside of Word with almost no back and forth communications.

Another one is the With construct, that enables .NET to grab a pointer to the object you are working with and call it directly instead of having to do a few back and forth trips just to know where the object is in memory.

P2TableRow.Cells.Item(intColumn).VerticalAlignment = ...
P2TableRow.Cells.Item(intColumn).Range.Bold = ...

could be written

With P2TableRow.Cells.Item(intColumn)
   .VerticalAlignment = ...
   With .Range
      .Text = ...
      .Bold = ...
      .Italic = ...
      .FontSize = ...
   End With
End With

This may make a big difference. It always did in VB6/VBA. In .NET, because the compiler does a better optimization job, sometimes you do not feel a difference, because the compiler might have put a With even if you did not. But it is worth a try.

The idea, you probably got it, is to try to limit as much as possible the number of times there is a call between your application and Word.

Another trick that changes things a lot is to make Word invisible and/or turn of ScreenUpdating.

<WordApplication>.Visible = True
<WordApplication>.ScreenUpdating = False

Not interesting in debugging because you do not see what is happening, but in real life, since word does not have to care about refreshing the screen ant take care of the margins, page breaks, pagination and all the likes, you can get a great boost of performance. Tablea are specially sensitive to that, because of the need to mix text with graphics. I got a 20x increase in speed in one of my applications that generated a 32 pages table once, just by turning off ScreenUpdating.

Just be sure to turn everyting back to True in case of an Exception and after the job is done.

LVL 12

Author Comment

ID: 36895439
Hello, JamesBurger,

Many thanks.  Those all sound like very good suggestions.  The Word application is already hidden, but I had not turned off ScreenUpdating.  I had hoped to avoid creating a template for this report.  My reluctance was just to avoid having to change the installation application.  But if moving the work to a Word macro solves the performance problem a template is certainly an option.  (This would also allow me to pre-format my tables.)  

I will give your suggestions a try and report back next week on my progress.  Thanks again.

LVL 83

Expert Comment

ID: 36902427
You can try to set the formatting on the table overall rather than at the cell level.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Expert Comment

ID: 36908902
Using com interop is very computing expensive. Also, can't be used in server side.
If you can output word 203 or above as final documents, I really recommend using xslt transforms to generate it. It's really fast, and as it's template based, you don't need to recompile your application to make future changes on the document.
Find an example here.
Best regards.
LVL 12

Author Comment

ID: 36913638
Hello, JamesBurger et al,

Thanks for all your input.   I have now had a chance to try some of the suggestions.

I repeated tests a few times get an impression of the effect of changes.  Although I tried to keep the test system otherwise stable I still found big differences in timing from one test to the next.  (It was taking between 35 and 65 seconds to create/fill a table with five columns and 35 rows.)  Even so, I
was able to get a rough idea of the benefits of the suggestions.  

By applying the suggestions such as turning off screen updating, eliminating unnecessary repetition of "dots" and minimizing the total number of calls made, I was able to reduce the time by about 50%.  This wasn't nearly enough to make the generation of a large number of tables feasible.  

So I finally tried JamesBurger's "best one" -- i.e. use a VBA macro in a template used to create the document.  I also followed his suggestion and passed in all the table data in an array, so only one call was required.  With this change, I was able to fill the table in approximately 0.3 seconds.

This two order-of-magnitude improvement solves the problem for me, so special thanks to JamesBurger for that excellent idea.  




(though not enough to claim great statistical significance).  My tests
By reducing the number of "dots" in my
LVL 12

Author Closing Comment

ID: 36913643
Many thanks for your suggestions.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

691 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