Hi,
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.
ex:
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
Editing:
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
Else
theRD.SearchString = theBatchArray(j)(0)
theRD.ReplaceString = theBatchArray(j)(1)
theKind = theBatchArray(j)(2)
repeatKind:
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)
repeatWord:
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)
repeatCase:
Select Case theCase
Case "ignore" : theRD.SearchCaseSensitive = False
Case "regard" : theRD.SearchCaseSensitive = True
Case Else : theCase = theCaseDefault
GoTo repeatCase
End Select
theWorkDoc.ReplaceAll(theRD)
End If
Next j
errorExit:
theBatchDoc.Dispose
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 
.
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
Exit_Function:
If Not IsNull( oCalcDoc ) Then oCalcDoc.Close( True )
End FunctionI 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.