geeky notes on web (drupal) and .net (c#) programming, whichever prevails at the moment

 

Search and process all numbers in a word document (vba word macro)

Note committed on 30 January 2011, tagged vb

My wife has had a mindblowingly tedious task at her job: to lower all the prices by 10% in her word file (some sort of a tourist agency price list document). The document was about 70 pages long with numbers scattered all around the document: in paragraphs as well as in tables, so effectively exporting it to excel was not an option.

The thing I've noticed that gave me the idea of trying VBA macros, was that the numbers to be cut by 10% had the format of XXXX.XX, like 500.00, so I decided to google it and compile a VB macro that could probably be of some (future) use too.

When the document was processed in only couple of seconds, it was one of those moments when I felt that what I did was some kind of magic: "How nice it is to have a programmer by my side!" :) This piece of code saved her a day or two of her life.

Sub decrease10()

Set objWdDoc = ActiveDocument
Set objWdRange = ActiveDocument.Content
Do
    With objWdRange.Find
        .MatchWildcards = True
        .Text = "[0-9]{1;5},[0-9]{2}"
        bFound = .Execute
        If Not bFound Then
            Exit Do
        End If
        .Execute Replace:=wdReplaceOne, replacewith:=Format(Round(objWdRange.Text * 0.9 + 0.0000001, 2), "Standard")
        Set objWdRange = objWdDoc.Range(objWdRange.Start + 6, objWdDoc.Range.End)
    End With
Loop
End Sub

======= Add new comment =======

Comments

===== Commented by Anonymous on 26 September 2011 =====

very useful.. :) is it possible to change the macro to round all the numbers to three decimal places ? im not a programmer either ;) i tried following but it didn't work. do you have an idea?

Sub round00()

Set objWdDoc = ActiveDocument
Set objWdRange = ActiveDocument.Content
Do
    With objWdRange.Find
        .MatchWildcards = True
        .Text = "[0-9]{1;5},[0-9]{2}"
        bFound = .Execute
        If Not bFound Then
            Exit Do
        End If
        .Execute Format(Round(a, 2), "0.00")
    End With
Loop
End Sub

===== Commented by Alex@vo1dmain on 12 October 2011 =====

Hi, if you want to just replace the existing 2-digits by reduced 3-digits, I guess you just change 2 with 3 in the rounding call:

.Execute Replace:=wdReplaceOne, replacewith:=Format(Round(objWdRange.Text * 0.9 + 0.0000001, 3), "Standard")

Haven't tried it though and honestly, dont have a wish to, the macro language looks again completely unknown to me after half a year of non-usage.

======= Add new comment =======

}