ClearFormatting get's slower and slower

[Stephen Gray]

To speed up a Word report ( using VB ) I'm trying to get a table per
page rather then one big table as they have previously. The table has
over 11,000 lines on it so it gets slower and slower. I’ve managed
to create one table per page ( which speeds up the report a lot ) but
the problem that I need to apply the clearFormatting to each row in
order to get it’s correct size ( so I can see if I need a new table
or not ). Previously the developer was able could do this for the
whole table but now I have to do it on a row my row basis. Below is
one a routine that they used previously on the table. The problem is
that this call gets slower and slower, even though I’m working on
only one row at a time. That is if I run the code for the first table
it runs through about 10 rows per second but if I say apply it to table
20 ( on page 20 ) it then only handles 1 possibly 2 a second.

I also tried using RegExp to do the removal for me which was fast but
sadly it removed the formatting ï‹

Any help would be appreciated !

Private Sub mpTrimRow(ByVal oRow As Word.Row)
Const cProcedure As String = "mpTrimRow"
On Error GoTo Handler

With oRow.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.text = "^p" ' remove surplus CR
.Replacement.text = ""
.Forward = True
' .Wrap = wdFindContinue ' 12/05/03
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With

With oRow.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.text = "\{*\}" ' remove formatting commands using wildcard
.Replacement.text = ""
.Forward = True
' .Wrap = wdFindContinue ' rw 12/05/03
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True ' must be true to find wildcard
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With


Handler:
Throw Err, cMODULE, cProcedure
Exit Sub
End Sub

 

[Klaus Linke]

Hi Stephen,

Do you really need to search row by row instead of table by table? Or maybe
even the whole document (since the paragraph marks between tables can't be
deleted with your Find/Replace anyway)?

Apart from that: Your code for calling mpTrimRow (which isn't shown) might
be culpable for the slow-down, too, say if you used "For i = 1 to
someTable.Rows.Count ".

If you really need to do it the way you are currently doing it, an
"ActiveDocument.UndoClear" after each replacement might help... but you
should make sure you have backed-up the file since Undo won't work any more.

My feeling is: If none of your tables span several pages when you start the
macro, and with less than 100 pages, all the replacements you're doing
together shouldn't need to take more than a very few seconds.

Regards,
Klaus


To speed up a Word report ( using VB ) I'm trying to get a table per
page rather then one big table as they have previously. The table has
over 11,000 lines on it so it gets slower and slower. I've managed
to create one table per page ( which speeds up the report a lot ) but
the problem that I need to apply the clearFormatting to each row in
order to get it's correct size ( so I can see if I need a new table
or not ). Previously the developer was able could do this for the
whole table but now I have to do it on a row my row basis. Below is
one a routine that they used previously on the table. The problem is
that this call gets slower and slower, even though I'm working on
only one row at a time. That is if I run the code for the first table
it runs through about 10 rows per second but if I say apply it to table
20 ( on page 20 ) it then only handles 1 possibly 2 a second.

I also tried using RegExp to do the removal for me which was fast but
sadly it removed the formatting ?

Any help would be appreciated !

Private Sub mpTrimRow(ByVal oRow As Word.Row)
Const cProcedure As String = "mpTrimRow"
On Error GoTo Handler

With oRow.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.text = "^p" ' remove surplus CR
.Replacement.text = ""
.Forward = True
' .Wrap = wdFindContinue ' 12/05/03
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With

With oRow.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.text = "\{*\}" ' remove formatting commands using wildcard
.Replacement.text = ""
.Forward = True
' .Wrap = wdFindContinue ' rw 12/05/03
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True ' must be true to find wildcard
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With


Handler:
Throw Err, cMODULE, cProcedure
Exit Sub
End Sub

 

[Stephen Gray]

Hi Klaus,

Thanks for responding. Previously the developer had one massive table,
with the headers duplicated on each page and then he/she applied the
search and replace to the whole document it was fast. However the
table creation was very very slow ( + 8 hours ! ). This is a fairly
old system and they way they do it is export the data from Crystal in a
Word format. The word document has the text, a number of formatting
codes in and also has text coloured and bold. The app then takes that
document, creates a new document, formats it with the crystal codes
specified ( via translation code ) and then at the end it removes the
formatting chars and any carriage return codes. I’ve changed the
code to only produce one table per page, and copy the header rows from
the first table and it takes the time down to 20 mins, if I don’t
apply the search and replace. If I do it takes the total time to 2
hours! Sadly I do have to do it row by row as removing the formatting
does make the rows smaller and I have to work out if a new row will go
onto the next page, if it does remove it and create a table instead
with one row. The call to mpTrimRow is called after the row has been
created.

Using a simple search and replace in VB takes no time at all either on
a row by row but if I do that it for some reason looses any formatting
applied ( as you can see I don’t know that much about Word ï‹ )

As a side point the word document produced from Crystal has in it
11,000+ Paragraphs ( which get copied as rows in tables in the new
document ). The original developer was going through each paragraph
via the index and that alone was taking 40 minutes or so ! I changed
it to a For Each and it went down to 20 seconds.

Thanks again Klaus, or anyone, for any pointers you can give.