Ask Your Question

Writer: Automated Find Replace

asked 2017-08-02 18:38:18 +0200

durecu1300 gravatar image

updated 2017-08-02 19:03:45 +0200


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

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2017-08-10 20:30:40 +0200

librebel gravatar image

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( "" )
    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   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( "" ) 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( "" ) 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 ...
edit flag offensive delete link more


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.

likespike gravatar imagelikespike ( 2017-09-18 10:54:41 +0200 )edit

answered 2017-08-02 23:14:52 +0200

Lupp gravatar image

updated 2017-08-10 23:04:33 +0200

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

edit flag offensive delete link more

answered 2017-08-03 13:02:32 +0200

durecu1300 gravatar image

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

edit flag offensive delete link more


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

m.a.riosv gravatar imagem.a.riosv ( 2017-08-03 15:59:51 +0200 )edit

answered 2017-08-09 22:49:13 +0200

likespike gravatar image

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?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2017-08-02 18:38:18 +0200

Seen: 94 times

Last updated: Aug 10