Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Word Automation - Performance Problem

Posted on 2011-09-30
6
Medium Priority
?
487 Views
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.

Thanks,
Randy
0
Comment
Question by:omegaomega
[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
6 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 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.

0
 
LVL 12

Author Comment

by:omegaomega
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.

Cheers,
Randy
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36902427
You can try to set the formatting on the table overall rather than at the cell level.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:cubaman_24
ID: 36908902
Hello:
Using com interop is very computing expensive. Also, can't be used in server side.
http://support.microsoft.com/kb/257757
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.
http://weblogs.asp.net/cnagel/archive/2004/09/25/234188.aspx
Best regards.
0
 
LVL 12

Author Comment

by:omegaomega
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.  

Cheers,
Randy


 

 

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

Author Closing Comment

by:omegaomega
ID: 36913643
Many thanks for your suggestions.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Suggested Courses

597 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