How to speed up a Word macro

To my knowledge there is no way to group scripts into commands. Perhaps it might be faster to execute the macro from within word's VBA, rather than doing it from a completely standalone software that is creating the word object via a CREATEOBJECT("Word.Application") call... but I think you're already doing that.

I'm surprised that disabling screen updating slowed things down. Usually 90% of the slowdown of a macro is Word redrawing itself nonstop.

What you can try is keep word invisible while you work on it. E.g.

Application.Visible = False
' do your work
Application.Visible - True

This will completely hide word while your program works. If you don't like that, you can instead create a whole NEW instance of word, and work with THAT. E.g.

    Dim oWord As New Word.Application
    oWord.Visible = False
    oWord.Documents.Add
    oWord.Selection.TypeText ("Test!")
    oWord.Visible = True

Will make a new instance of word, make a new doc, add text, and then show it only when finished.

If you do it the second way, it's important you reference the oWord object, and not accidentally omit it, or you will be sending commands to the currently open Word document, and not the new instance you made.

Bah that's what I get for not reading your question completely. I didn't realize you're working from ASP.NET.

Just set the "isVisible" variable that was created in the example to false. And then at the end of the macro, set it's visibility back to true via oWordApp.Visible = True

Thanks.  Good points, however I left out a few details, sorry:

I was surprised too on the rendering slowdown. However, maybe it's not so surprising, since it actually IS running in the background (on a new instance of Word), no screen updates.  What my .NET code (adapted from that codeproject example) seems to do is start an instance of Microsoft Word in the background, and then it builds the document and saves it to a specified filename, all invisibly.  That's the part that takes 35 seconds.  Then I use Process.Start on that filename, and it is opened in a visible instance of Microsoft Word.

Oops, crossing emails.  I actually am working from VB.NET.

Am looking at your IsVisible property...

Screen updating can slow things down, so you can turn it off at the beginning. You could try minimising the application to make sure that screen updating is not involved.

However 35 to 50 seconds does sound like a long time to create a five-page document, but it would depend on exactly what you are doing.

There might be ways to speed the macro up, but it's hard to guess without knowing what is in it. Can you post it?

I now understand that you are not actually running a macro, but you are using Automation.  However, I would guess that ASP.net code is understandable with a knowledge of VBA.

Yeah, tried to turn off updating with "Application.ScreenUpdating = False", but that seemed to slow it down.  It's already completely in the background, better than minimized I would think.

It would be hard to post (or rather, hard to understand), since I've wrapped my functionality into a "myWordDocBuilder" class that just abstracts my report building.  It is merely combinations of the following calls:

wordapp.Selection.TypeText(txt)
wordapp.Selection.Font.Size = size
wordapp.Selection.Font.Bold = True
wordapp.Selection.Tables.Add(wordapp.Selection.Range, numrows, numcols)
Table.Columns.SetWidth...
Table.Style = "Table Grid"
Table.Cell(r, c).Range.Text = txt
wordapp.Selection.EndKey(Unit:=wdStory)   (to set up the next table insert, after messing around in the cells)

My sequencing might not be optimal, but shouldn't be 35 seconds inefficient.  Don't know, you think I could get a lot of improvement there?

It is using the exact macro language (I can more-or-less cut and paste from a Word VBA backend), but yeah it's automation.  Is that the big source of lag?  Maybe it's sending individual macro commands (using automation) from my .NET app across the Word instance, and there's a way to group the commands?

Researching alittle...

Thanks, will try that!

Actually it turns out the very slow part is

Table.Cell(r, c).Range.Text = txt

again and again.  Still profiling...

If that does turn out be be a slow process, it might be faster to create a tab-separated text block and then use Convert to table.

why are you using a macro to do this, surely what your after is a template document?

ManicD, the real answer is because I don't know how to work with templates.  I moved to this approach (generating word docs from scratch) after getting fed up with crystal reports (which is a kludge) and
microsoft reporting.  (So far it's a real improvement.)

However, there is a lot of variability in these reports that I could see using a template might not provide much benefit, and a needless complexity.  Columns are the same, but the number of tables, and number of rows in each table, vary wildly.  Text will completely change from report to report, and might or might not be printed between each table (based on things the user tells my program).  Aren't templates field-based?  This seems like it might not work well.

What I've done as an intermediate step is put the macro code on a background thread, and put a % progress in the status bar of my app.  This makes that 35 seconds much less painful.  But I still want to get that down and am trying your suggestions.  

If you think the template might be worth developing/learning for this, given the variability, then maybe I'll try it.

You can 'hop' from cell to cell using the For Each construct on the Cells collection in a Row or in a Table. That way you don't have to use the Selection.


If you do use a template, you can keep formatted boilerplate text ready for use as Autotext, but of course that won't help much if your bottleneck is table cell filling.

You could always try the DoEvents Method.  Although this might not speed up the actual run time (in fact, it could make it slower), it might give the program the appearance of being faster, especially if your program requires a lot of repainting and updating.  Certainly, it gives control back to the user while you wait for the code to finish.