Writer: Automated Find Replace


I have a Writer document which is fairly long (500 000 words) in which I need to do a substantial number (a few thousands) of FIND REPLACE. A separate Calc spreadsheet has the FIND in one column and the REPLACE in the next one.

Column A Column B

Alpha Alpha123

Beta XYZBeta

I am looking for an automated way to do these FIND REPLACE. I guess it is done through a macro, but I have never done it and don’t know basic.

Can you help me ?

Many Thanks

It should be preferable to look at this task from the viewpoint of a batch file containing the replacement commands and organising the access to the workfile, the processing, the saving …
A Calc document is the proper means to act as such a batch file and at the same time to include the needed user code in a script container.
If interested you can download this master batch sheet (as an .ods) and this first example Writer file. Place the two files anywhere on your system and run the batchReplace.ods. Adapt the file location in column H and close the circuit by typing Go! into K10. The batch replaced file will be stored in the folder where the workfile resides.
The code is contained in the batchSheet document. It is similar to the previously published code below.

Original answer:
I don’t know how to help in this case. The one hint I can give: Read the famous texts by Andrew Pitonyak.

Beyond that I can only make a demo. I did.

Sub batchSearchReplace() 
REM Rough! No error catching! Be careful!

theBatchFilePath = "Your system's folderpath for the .ods file containing the replacements in the format exemplified in the example/S_R_Batch.ods" 
On Error GoTo errorExit
Dim theLoadParams(0) as New com.sun.star.beans.PropertyValue
theLoadParams(0).Name  = "Hidden"
theLoadParams(0).Value = True
theBatchDoc   = StarDesktop.LoadComponentFromURL(ConvertToURL(theBatchFilePath), "_blank", 0, theLoadParams())
theBatchSheet = theBatchDoc.Sheets().GetByName("BatchSheet")
theBatchRange = theBatchSheet.GetCellRangeByName("A2:E42")
theBatchArray = theBatchRange.DataArray
theWorkDoc    = ThisComponent
theRD         = theWorkDoc.CreateReplaceDescriptor()
theKindDefault= theBatchArray(0)(2)
theWordDefault= theBatchArray(0)(3)
theCaseDefault= theBatchArray(0)(4)
For j = LBound(theBatchArray()) + 1 To UBound(theBatchArray())
    If (theBatchArray(j)(0)="") Then 
        theRD.SearchString  = theBatchArray(j)(0)
        theRD.ReplaceString = theBatchArray(j)(1)
        theKind = theBatchArray(j)(2)
        Select Case theKind
           Case "literal": theRD.SearchRegularExpression = False
           Case "regex"  : theRD.SearchRegularExpression = True
           Case Else     : theKind = theKindDefault
                           GoTo repeatKind
        End Select
        theWord = theBatchArray(j)(3)
        Select Case theWord
           Case "ignore" : theRD.SearchWords             = False
           Case "whole"  : theRD.SearchWords             = True
           Case Else     : theWord = theWordDefault
                           GoTo repeatWord
        End Select
        theCase = theBatchArray(j)(4)
        Select Case theCase
           Case "ignore" : theRD.SearchCaseSensitive     = False
           Case "regard" : theRD.SearchCaseSensitive     = True
           Case Else     : theCase = theCaseDefault
                           GoTo repeatCase
        End Select
    End If
Next j
End Sub  

Ther are also demo files. A little ‘Writer’ file also containing the “macro” and an .ods file demonstrating in what format the replacements are eexpected by the “macro”.

Thank you Lupp. I will do my homework, study your leads and revert if i’m stuck.

This is not an anwer please comment on the answer. And If the answer solves your question please tick the :heavy_check_mark:.

I couldn’t get this to work. Is there a more detailed tutorial on how to customize and run this macro?
Does it also work for Calc or just for Writer?

Hello @durecu1300,

the following macro does basically the same as Lupp’s macro above, with some improvements in error checking and general usability. Also @likespike, please see if this works for you:

Function Batch_Replace( strCalcFilePath$, iSheetIndex%, strCellRange$, bUseRegularExpressions As Boolean, bCaseSensitive As Boolean, bWholeWords As Boolean ) As Long
REM	Perform multiple Find/Replace operations within the current Office document, using a Calc spreadsheet that holds the terms to Find/Replace.
REM Tested once on small batch; This works on Writer documents and Calc documents ( active sheet only ).
REM <strCalcFilePath>:	Full Path pointing to the spreadsheet file that contains the terms to Find/Replace.
REM <iSheetIndex>:		0-based index of the Sheet that contains the specified CellRange.
REM <strCellRange>:		CellRange containing the terms to Find/Replace, must have at least 2 columns, e.g. "A1:B4999".
REM						The 1st column of this range should contain the terms to Find, the 2nd column should contain the terms to Replace.
REM <bUseRegularExpressions>:	Pass TRUE if the terms to Find/Replace should be treated as Regular Expressions.
REM <bCaseSensitive>:	Pass TRUE if the terms to search are Case Sensitive.
REM <bWholeWords>:		Pass TRUE if the terms to search must be whole words.
REM This function returns the total number of found/replaced occurrences.
REM Example call:		Batch_Replace( "/home/user/Desktop/Find_Replace_List.ods", 0, "A1:B20", false, false, false )
	Dim oFileAccess as Object
	oFileAccess = createUnoService( "com.sun.star.ucb.SimpleFileAccess" )
	If Not oFileAccess.Exists( strCalcFilePath ) Then
		Msgbox( "The specified file does not exist: '" & strCalcFilePath & "'.", 16, "macro:Batch_Replace()" )
		Exit Function
	End If
	Dim aProps(0) As New com.sun.star.beans.PropertyValue	REM Open the spreadsheet file invisibly.
	aProps(0).Name = "Hidden"
	aProps(0).Value = True
	Dim oCalcDoc As Object
	oCalcDoc = StarDesktop.loadComponentFromURL( ConvertToURL( strCalcFilePath ), "_blank", 0, aProps() )
	If IsNull( oCalcDoc ) Then
		Msgbox( "Could not open the specified file: '" & strCalcFilePath & "'.", 16, "macro:Batch_Replace()" )
		Exit Function
	End If
	If Not oCalcDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then
		Msgbox( "The specified document must be a SpreadsheetDocument: '" & strCalcFilePath & "'.", 16, "macro:Batch_Replace()" )
		GoTo Exit_Function
	End If
	If iSheetIndex < 0 Or iSheetIndex > oCalcDoc.Sheets.getCount() - 1 Then
		Msgbox( "Invalid Sheet index: " & iSheetIndex & ".", 16, "macro:Batch_Replace()" )
		GoTo Exit_Function
	End If
	Dim oSheet As Object, oRange As Object, aData As Variant
	oSheet = oCalcDoc.Sheets.getByIndex( iSheetIndex )			REM sheet containing the Find/Replace terms.
	oRange = oSheet.getCellRangeByName( strCellRange )
	aData = oRange.getDataArray()
	Dim oDoc As Object : oDoc = ThisComponent					REM Assume oDoc = Writer document.
	If oDoc.SupportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then
		oDoc = oDoc.CurrentController.ActiveSheet				REM oDoc = Calc document.
	End If
	Dim oReplace as Object
	oReplace = oDoc.createReplaceDescriptor()
	oReplace.SearchRegularExpression = bUseRegularExpressions
	oReplace.SearchCaseSensitive	 = bCaseSensitive
	oReplace.SearchWords			 = bWholeWords
	Dim i As Integer, nReplaced As Integer, aRow As Variant
	If uBound( aData ) >= 0 And uBound( aData(0) ) > 0 Then		REM	Require at least 1 row and 2 columns.
		For i = 0 To uBound( aData )							REM Traverse rows.
			aRow = aData( i )
			oReplace.setSearchString( aRow( 0 ) )
			oReplace.setReplaceString( aRow( 1 ) )
			nReplaced = nReplaced + oDoc.replaceAll( oReplace )	REM Perform a Find & Replace.
		Next i
	End If

	Batch_Replace = nReplaced
	If Not IsNull( oCalcDoc ) Then oCalcDoc.Close( True )
End Function

I just can’t get this to work. Probably because I’m too dumb. Does anyone have a step by step guide on how to implement this into libreoffice calc? I’m happy to $ for the help. Please PM me.