[Solved] Macro with alternative searches and replacements

[Davidcannon65]

I'm new to this game. I'm trying to make a macro for a text full of initials, to be replaced with full names.

This is what I've written :

args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "([FDR])\1([JFK])\2|([LBJ])\3"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "[Franklin D. Roosevelt]$1|[John F. Kenney]$2|[Lyndon B. Johnson]$3"

Replacing FDR with Franklin D. Roosevelt, JFK with John F. Kennedy, and LBJ with Lyndon B. Johnson. But it DOES NOT WORK. Can you please tell me what I'm doing wrong? Thanks!

[RoryOF]

Why bother with a macro? Three separate searches using Find and Replace and the job is done - perhaps two minutes total. Much quicker than writing and debugging a macro.

If your problem is actually knowing why the macro doesn't work, you need to consult Andrew Pitonyak's macro books, which is where anyone helping will probably start.

[Zizi64]

And - if you want to substitute the initial string to full names while you typing them -, you can use the Autocorrect feature.

[John_Ha]

Try running each of the three searches independently, one after the other, instead of using triple arguments, as it will be very easy to debug. Get the first working, and copy it twice for the other two.

Code: Select all

Change JFK
...
Change FDR
...
Change LBJ

I have this comment in a macro I copied.

Code: Select all

'  REMEMBER - the called sub uses Regular Expressions, so be sure to use \. and not . etc 

Why bother with a macro? Three separate searches using Find and Replace and the job is done

You might even be able to record that as a macro [Tutorial] How to record a macro (and Regular Expressions)

Hacking a recorded macro is much easier than writing from scratch.

[RoryOF]

The three searches could be put into a batch file in Alt Search, if there are multiple files to correct.

[Davidcannon65]

Thanks guys. Yes, I know there are workarounds, and in fact I've made some myself (using the find and replace boxes, etc.) I've even made a macro that works around it by putting each set of initials in a separate Args. But I guess I'm the kind of person who isn't satisfied with workarounds. For me, a problem has to be SOLVED, not worked around, and if I have an idea that I SHOULD be able to make a macro that does something simply and efficiently, I find myself losing sleep until I've got the solution. That's why I was hoping that someone here might have done something similar before. But if not? Never mind

[RoryOF]

If you are that interested in macros, read Andrew Pitonyak's books. These are the source we all go to.

[John_Ha]

That's why I was hoping that someone here might have done something similar before

I have - by using the KISS principle. Do it one small bit at a time.

You are asking for your code to be debugged without showing the entire macro. I don't know what is wrong with what you have written. But I do know how to record a macro and hack it to do exactly what you want. It may use a few more CPU cycles but I have several billion a second waiting to be used so who cares?

If that's not good enough for you go to https://www.pitonyak.org/oo.php

NB It took me less than one minute to create this macro to replace all instances of JFK with John Fitzgerald Kennedy. That's less time and far less keystrokes than it took you to write your post.

Code: Select all

sub JFK
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(18) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "JFK"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "John Fitzgerald Kennedy"
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1024
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3
args1(18).Name = "Quiet"
args1(18).Value = true

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())

end sub

[JeJe]

If you use Useful Macro Information For OpenOffice By Andrew Pitonyak (and his other books)
In my edition there's this listing 7.14.1. Replacing Text which I've modified slightly replacing the array values with yours

Code: Select all

Sub AtToUnicode
'Andy says that sometime in the future these may have to be Variant types to work with
'Array()
'Dim numbered(5) As String, accented(5) As String
Dim n as long
Dim oDocument as object, oReplace as object
'numbered() = Array("A@", "a@", "I@", "i@", "U@", "u@", "Z@", "z@", "O@", "o@", "H@",_
'"h@", "D@", "d@", "L@", "l@", "M@", "m@", "G@", "g@", "N@", "n@", "R@", "r@",_
'"Y@", "y@", "S@", "s@", "T@", "t@", "C@", "c@", "j@", "J@")
numbered() = Array("FDR","JFK","LBJ")
accented() = Array("Franklin D. Roosevelt","John F. Kenney","Lyndon B. Johnson")
'accented() = Array(Chr$(256), Chr$(257), Chr(298), Chr$(299), Chr$(362), Chr$(363),_
'Chr$(377), Chr$(378), Chr$(332), Chr$(333), Chr$(7716), Chr$(7717), Chr$(7692),_
'Chr$(7693), Chr$(7734), Chr$(7735), Chr$(7746), Chr$(7747), Chr$(7748), Chr$(7749),_
'Chr$(7750), Chr$(7751), Chr$(7770), Chr$(7771), Chr$(7772), Chr$(7773), Chr$(7778),_
'Chr$(7779), Chr$(7788), Chr$(7789), Chr$(346), Chr$(347), Chr$(241), Chr$(209))
oReplace = ThisComponent.createReplaceDescriptor()
oReplace.SearchCaseSensitive = True
For n = LBound(numbered()) To UBound(accented())
oReplace.SearchString = numbered(n)
oReplace.ReplaceString = accented(n)
ThisComponent.ReplaceAll(oReplace)
Next n
End Sub

[Davidcannon65]

Thank you so much, everybody, for your comments, and especially @JeJe, for giving me a solution that WORKS!

Yes, I am currently taking an interest in macros — we're in Covid lockdown and I'm trying to teach myself some new skills while I've got time on my hands. But I'm not very good at learning academically in a vacuum — what I'm learning has to be related to what I'm working on. In this case, a document full of presidents' initials which I want to make more formal. I've learned to write a number of macros for documents I'm working on, but this one had me stuck. So thank you SO MUCH!

@John Ha — yes, the macro you made is identical to the one I made, except that it has only one search item. Mine had three — and didn't work. My workaround was to create a LOOOOOONG macro with three Args, each search and replace item taking up a separate Args. It worked, but it was clumsy and cludgy.

And thanks to everybody for suggesting Andrew Pitonyak's books! I've decided to do some serious study of his materials, starting today.

[John_Ha]

My workaround was to create a LOOOOOONG macro with three Args, each search and replace item taking up a separate Args. It worked, but it was clumsy and cludgy.

Clumsy and cludgy????

It is called "in-lining" and is a technique frequently used to enhance performance for performance benchmarks. In-line code removes all the overheads, forks, repetition, tests etc and produces the minimum, most streamlined, fastest executable code. It also makes the code far easier to maintain.