Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

At the request of lapsedLibrarian I have created a macro to do multiple find & replace operation at one time. It is heavily commented and meant to provide you with a way to customize it for your own needs.

Please note the USER VARIABLE "AskMe" near the beginning of the file which controls whether you are asked about doing each of the operations.

Also note that my definition of the AskArray extends over several lines by using "_" to continue the definition to the next line. "_" must be the last character on the line and if is followed inadvertently by a space you will get an error.

Option Explicit 'You must Dim all variables.Sub CleanUpDim AskMe as Boolean'>>>>>>>>>>>>USER VARARBLE<<<<<<<<<<<AskMe = True 'Change to False to avoid the query for each find & replace.'>>>>>>>>>>END USER VARIABLE<<<<Dim oDoc,AskArray,SearchArray,ReplaceAray,FandR,c,iAns'Array contents:AskArray = Array("Replace multiple spaces with one space?",_"Delete spaces before paragraph breaks?",_"Delete spaces before tabs?",_"Delete spaces after tabs?")REM Place in quotes what you would put in the Search/Replace boxesREM in the Find & Replace dialog without the quotes. SearchArray = Array(" *"," *$"," *\t","\t *")ReplaceAray = Array(" " ,"" ,"\t" ,"\t" ) oDoc = ThisComponent 'Get the current active document.FandR = oDoc.createReplaceDescriptor 'Set up find & replace.FandR.searchRegularExpression = True 'Make it recognise regular expressions.For c = 0 to uBound(SearchArray) 'Loop through the arrays.If AskMe then 'If AskMe is True then ask. iAns = MsgBox (AskArray(c),3)'See Help in the Basic IDE for the various 'buttons available in MsgBox and the integers they return. If iAns = 2 then End 'Cancel button clicked so quit.EndIfIF iAns = 6 or AskMe = False then 'Yes button clicked, or assumed clicked, 'so do the find & replace. FandR.setSearchString(SearchArray(c)) FandR.setReplaceString(ReplaceAray(c)) oDoc.ReplaceAll(FandR)EndIf'The No button will simple fall through to here so there is no need to process it.Next cEnd Sub

I am still using several versions of the Find and Replace macro that you wrote for me to update documents encoded without out-of-date custom font encodings. It is very easy to edit for different tasks. I stripped all of the comments as I think it is fairly obvious what to edit in the two arrays. The speed of this macro is simply amazing, even on long documents.

I'm just posting to say I found this really helpful. I wanted to convert Latin text to Hebrew characters, but I use an idiosyncratic keyboard layout for Hebrew text entry, so there's nothing readily available to do the job.

I've never written a macro before, but I used the examples as a guide and eventually I got it to work!

The biggest issue was to get Search & Replace to play nice with the short vowel diacritics, as they apparently take different shape for this. Note: Shadda is represented as a 2 rather than the repeated letter - it's probably possible to solve that via a regular expression, but I cannot figure it out right now.

Robert Tucker wrote:Are you calling the macro from an open Writer document? Have a Writer document open, click in it with your mouse, then try to run the macro.

Hmmm...I think it might have actually worked. At least I don't get this 'not found' anymore...

Thank you very much

Now, just one more, extremelly important thing:

Could anyone please tell me, how to do the same in my spreadsheet, in Calc ?? I need to either find and replace the given values or, which would be even better, just find and highlight the found lines/records (to copy it).

I am quite desperate. I have searched the internet the best to my ability and didn't find anything quite this helpful (including this forum).

Could anyone please tell me, how to do the same in my spreadsheet, in Calc ??

Here's some modified code that will work in Calc.

Thank you for the code to replace in Calc. I'd like to adjust the list of my variing terms to search and to replace easily, without touching the code. For example working on spreadsheet 1 and referring to the listing of the search terms in column 1 of spreadsheet 2 and the corresponding replace terms in columns number 2 of spreadsheet 2?Thank you for your help