Ask Your Question

Find and replace using a table

asked 2018-09-27 19:40:58 +0200

Somewhereintexas gravatar image

updated 2018-09-27 19:57:58 +0200

JohnSUN gravatar image

Can someone write a writer macro for me? I'll donate $100 to LibreOffice

Document 1 has two column table. Column 1 is "find" string. Column 2 is "replace" string. The find string would not be longer than 16 to 20 characters. The replace string might be 512 characters but I realize there might a limit of 256.

Document 2 is the working document. The macro called from macro 2 will find and replace all the items in document 2 using document 1 table.

I found this Word macro as a reference. I am trying to migrate my one-man company away from Word

Sub ReplaceFromTableList3()
 Dim oChanges As Document, oDoc As Document
 Dim oTable As Table
 Dim oRng As Range
 Dim rFindText As Range, rReplacement As Range
 Dim I As Long
 Dim sFname As String

 'Change the path in the line below to reflect the name and path of the table document
 sFname = "C:\reports\wreports\nameoffilewithfindreplacetable.doc"
 Set oDoc = ActiveDocument
 Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
 Set oTable = oChanges.Tables(1)

 For I = 1 To oTable.Rows.Count
     Set oRng = oDoc.Range
     Set rFindText = oTable.Cell(I, 1).Range
     rFindText.End = rFindText.End - 1
     Set rReplacement = oTable.Cell(I, 2).Range
     rReplacement.End = rReplacement.End - 1
     Selection.HomeKey wdStory
     With oRng.Find
             .Replacement.Font.Color = wdColorauto
             .MatchWildcards = False
             .MatchWholeWord = True
             .MatchCase = False
             .Text = rFindText.Text
             .Replacement.Text = rReplacement.Text
             .Forward = True
             .Wrap = wdFindContinue
             .Execute Replace:=wdReplaceAll
       End With
 Next I

 oChanges.Close wdDoNotSaveChanges

End Sub
edit retag flag offensive close merge delete


Do you know what is the main danger when using this macro? If you have (for example) a line "once" => "every time you need it", and then there's a line "once more" => "let's together", then the second replacement will not be executed. While the macro reaches this line, the word "once" will disappear from the source text.

JohnSUN gravatar imageJohnSUN ( 2018-09-27 20:04:48 +0200 )edit

Wow, that was fast. I'll try it. I think I understand the main danger but wouldn't matchwholeword = True prevent that? I'll try it tomorrow and let you know how it went. Thank you.

Somewhereintexas gravatar imageSomewhereintexas ( 2018-09-28 05:06:00 +0200 )edit

Trying to step thru it. Ran into this on the second line

Dim oChanges As Document, oDoc As Document

The first "document" in the line Error Unknown data type document

Somewhereintexas gravatar imageSomewhereintexas ( 2018-09-28 05:14:57 +0200 )edit

Perhaps, I did not quite express myself. I did not mean to say that this macro will work in LibreOffice. I meant that the algorithm that this macro implement has a very big drawback - as a result of its work you can get the wrong text, and with a large table of replacements it is very difficult to find the reason for this.

JohnSUN gravatar imageJohnSUN ( 2018-09-28 07:45:17 +0200 )edit

Thanks, JohnSun, the table I use in Word is only about 800 entries. Yes, you can scramble things if you're not careful. I use the table of a relatively small file.

Somewhereintexas gravatar imageSomewhereintexas ( 2018-09-30 03:42:01 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-09-30 21:56:19 +0200

Somewhereintexas gravatar image

The examples provided solved the problem with excellence. Thank you.

edit flag offensive delete link more

answered 2018-09-28 06:59:34 +0200

JohnSUN gravatar image

updated 2018-09-30 09:25:42 +0200

OK, please try this variant - C:\fakepath\Replacer.ods

Or for using Regexp and to prevent sort you can try this - C:\fakepath\Replacer_short.ods

edit flag offensive delete link more


Thanks. That is really nice work. I will send my $100 donation to OfficeLibre on Monday. Here is what I experienced using it.

Word allows me to find and replace paragraphs ^p. I found out that is tricky in this circumstance. Can the code "check" the regular expressions checkbox. I think I found a sloppy way to find/replace line breaks.

I also noticed the table seems to sort itself after the program runs. Can I prevent the sort? Again, very nice work.

Somewhereintexas gravatar imageSomewhereintexas ( 2018-09-30 03:57:24 +0200 )edit

I was reading about find and replace and ran into "algorithms". That is a whole new world for me but interesting. Thanks

Somewhereintexas gravatar imageSomewhereintexas ( 2018-09-30 03:58:39 +0200 )edit

About ^p - here you can use \n for line-break and $ for the end of the paragraph. Also with the second file, you can use any regular expressions The community is grateful to you for your help and support.

JohnSUN gravatar imageJohnSUN ( 2018-09-30 09:25:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-27 19:40:58 +0200

Seen: 253 times

Last updated: Sep 30 '18