# Find and replace using a table

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 .ClearFormatting .Replacement.ClearFormatting .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 close merge delete ## Comments 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. ( 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. ( 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 ( 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. ( 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. ( 2018-09-30 03:42:01 +0200 )edit ## 2 Answers Sort by » oldest newest most voted The examples provided solved the problem with excellence. Thank you. more 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 more ## Comments 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.

( 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

( 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.

( 2018-09-30 09:25:07 +0200 )edit