Word/VBA > LibreOffice Macro Conversion

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
REH-CAE-RPT
Posts: 14
Joined: Wed Jul 12, 2017 9:28 pm

Word/VBA > LibreOffice Macro Conversion

Post by REH-CAE-RPT »

So, I hadn't heard of LibreOffice until I was told we had to start using it with basically no warning.

I had developed some capacity to modify existing macros in Word – I frequently use a combination of a dozen or so relatively basic VBA macros.

I'm hoping to duplicate them in LibreOffice. I'm just gonna paste the main ones below, in case anyone wants to help out.

In all cases, I'd be applying these functions to the entire document; there'd be no headers/footers or anything like that – I usually clear all formatting within each macro.

If there's somewhere else I should be asking, an automated way to convert them (!), or a comprehensive list of basic equivalents somewhere, please let me know.

Appreciate any help...

1) Remove any formatting and change all text to lower case, Arial font, size 11:

Code: Select all

Selection.WholeStory
Selection.ClearFormatting
Selection.Range.Case = wdLowerCase
Selection.LanguageID = wdEnglishUS
ActiveDocument.Range.Font.Color = wdColorBlack
ActiveDocument.Range.Style = "No Spacing"
ActiveDocument.Range.Font.Name = "Arial"
ActiveDocument.Range.Font.Size = "11"
ActiveDocument.ShowSpellingErrors = True
ActiveDocument.ShowGrammaticalErrors = True
Options.CheckSpellingAsYouType = True
2) Start each sentence on a new line:

Code: Select all

Dim myStoryRange As Range
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
    .MatchWildcards = False
    .Execute findtext:=". ", ReplaceWith:=".^p", Replace:=wdReplaceAll
    .Execute findtext:="? ", ReplaceWith:="?^p", Replace:=wdReplaceAll
    .Execute findtext:="! ", ReplaceWith:="!^p", Replace:=wdReplaceAll
    .Execute findtext:=" # ", ReplaceWith:="^p# ", Replace:=wdReplaceAll
    .Execute findtext:=".^034 ", ReplaceWith:=".^034^p", Replace:=wdReplaceAll
    .Execute findtext:="?^034 ", ReplaceWith:="?^034^p", Replace:=wdReplaceAll
    .Execute findtext:="!^034 ", ReplaceWith:="!^034^p", Replace:=wdReplaceAll
   End With
3) Remove empty spaces at start/end of paragraphs and have a maximum of two lines between paragraphs:

Code: Select all

Dim myStoryRange As Range
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
    .MatchWildcards = False
    .Execute findtext:=" ^p", ReplaceWith:="^p", Replace:=wdReplaceAll
    .Execute findtext:="^p ", ReplaceWith:="^p", Replace:=wdReplaceAll
    .Execute findtext:="^p^p^p", ReplaceWith:="^p^p", Replace:=wdReplaceAll
    .Execute findtext:="^p^p^p", ReplaceWith:="^p^p", Replace:=wdReplaceAll
    .Execute findtext:="^p^p^p", ReplaceWith:="^p^p", Replace:=wdReplaceAll
   End With
4) Highlight spelling errors:

Code: Select all

Selection.HomeKey Unit:=wdStory
    Dim oSE As Range, oGE As Range
    For Each oSE In ActiveDocument.SpellingErrors
        oSE.HighlightColorIndex = wdYellow
    Next
lbl_Exit:
    Exit Sub
End Sub
5) Extract highlighted words:

Code: Select all

Dim oRng As Word.Range
    Dim oCol As New Collection
    Dim oDoc As Document, lngIndex As Long
    Set oRng = ActiveDocument.Range
    With oRng.Find
        .Highlight = True
        While .Execute
            oCol.Add oRng.Duplicate
            oRng.Collapse wdCollapseEnd
        Wend
    End With
    Set oDoc = Documents.Add
    For lngIndex = oCol.Count To 1 Step -1
        oDoc.Paragraphs.Last.Range.FormattedText = oCol.Item(lngIndex)
        oDoc.Paragraphs.Add
    Next
lbl_Exit:
    Exit Sub
End Sub
6) Various punctuation formatting...

Code: Select all

Dim myStoryRange As Range
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
    .MatchWildcards = True

'change dashes
    .Execute FindText:="^0151^13", ReplaceWith:="...^p", Replace:=wdReplaceAll
    .Execute FindText:="^0151", ReplaceWith:=" - ", Replace:=wdReplaceAll
    .Execute FindText:="^0150", ReplaceWith:=" - ", Replace:=wdReplaceAll
    .Execute FindText:=" -^13", ReplaceWith:="...^p", Replace:=wdReplaceAll

'change ellipses
    .Execute FindText:="^0133", ReplaceWith:="...", Replace:=wdReplaceAll
    .Execute FindText:=" ...^13", ReplaceWith:="...^p", Replace:=wdReplaceAll
    .Execute FindText:="^0133", ReplaceWith:="...", Replace:=wdReplaceAll

'change quotes
    .Execute FindText:="[^0145^0146]", ReplaceWith:="^039", Replace:=wdReplaceAll
    .Execute FindText:="[^0147^0148]", ReplaceWith:="^034", Replace:=wdReplaceAll
    
'em dashes replaced with hyphen and spaces either side
    .Execute FindText:="^0151", ReplaceWith:=" - ", Replace:=wdReplaceAll
    
'en dashes
    .Execute FindText:="([!\-])- ", ReplaceWith:="\1^0150 ", Replace:=wdReplaceAll
    .Execute FindText:="^0150", ReplaceWith:="-", Replace:=wdReplaceAll
     End With
7) Remove umlauts, macrons, etc...

Code: Select all

Dim myStoryRange As Range
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
    .MatchWildcards = True
    .Execute FindText:="[áàâäãå]", ReplaceWith:="a", Replace:=wdReplaceAll
    .Execute FindText:="[ÁÀÂÄÃÅ]", ReplaceWith:="A", Replace:=wdReplaceAll
    .Execute FindText:="[ç¢]", ReplaceWith:="c", Replace:=wdReplaceAll
    .Execute FindText:="[Ç]", ReplaceWith:="C", Replace:=wdReplaceAll
    .Execute FindText:="[éèêë]", ReplaceWith:="e", Replace:=wdReplaceAll
    .Execute FindText:="[ÉÈÊË]", ReplaceWith:="E", Replace:=wdReplaceAll
    .Execute FindText:="[íìîï]", ReplaceWith:="i", Replace:=wdReplaceAll
    .Execute FindText:="[ÍÌÎÏ]", ReplaceWith:="I", Replace:=wdReplaceAll
    .Execute FindText:="[ñ]", ReplaceWith:="n", Replace:=wdReplaceAll
    .Execute FindText:="[Ñ]", ReplaceWith:="N", Replace:=wdReplaceAll
    .Execute FindText:="[ðóòôöõø]", ReplaceWith:="o", Replace:=wdReplaceAll
    .Execute FindText:="[ÐÓÒÔÖÕØ]", ReplaceWith:="O", Replace:=wdReplaceAll
    .Execute FindText:="[úùûü]", ReplaceWith:="u", Replace:=wdReplaceAll
    .Execute FindText:="[ÚÙÛÜ]", ReplaceWith:="U", Replace:=wdReplaceAll
    .Execute FindText:="[ýÿ]", ReplaceWith:="y", Replace:=wdReplaceAll
    .Execute FindText:="[ÝŸ]", ReplaceWith:="Y", Replace:=wdReplaceAll
    .Execute FindText:="[š]", ReplaceWith:="s", Replace:=wdReplaceAll
    .Execute FindText:="[Š]", ReplaceWith:="S", Replace:=wdReplaceAll
    .Execute FindText:="[ž]", ReplaceWith:="z", Replace:=wdReplaceAll
    .Execute FindText:="[Ž]", ReplaceWith:="Z", Replace:=wdReplaceAll
    .Text = ChrW(256)
    .Replacement.Text = "A"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(257)
    .Replacement.Text = "a"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(274)
    .Replacement.Text = "E"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(275)
    .Replacement.Text = "e"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(298)
    .Replacement.Text = "I"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(299)
    .Replacement.Text = "i"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(332)
    .Replacement.Text = "O"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(333)
    .Replacement.Text = "o"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(223)
    .Replacement.Text = "ss"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(362)
    .Replacement.Text = "U"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(363)
    .Replacement.Text = "u"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(562)
    .Replacement.Text = "Y"
    .Execute Replace:=wdReplaceAll
    .Text = ChrW(563)
    .Replacement.Text = "y"
    .Execute Replace:=wdReplaceAll
End With
8) Delete identical paragraphs:

Code: Select all

Dim AmountMoved As Long
    Dim myRange As Range
    
    'start with first paragraph and extend range down to second
    Set myRange = ActiveDocument.Paragraphs(1).Range
    AmountMoved = myRange.MoveEnd(Unit:=wdParagraph, Count:=1)
    
    'loop until there are no more paragraphs to check
    Do While AmountMoved > 0
    
        'if two paragraphs are identical, delete second one
        'and add the one after that to myRange so it can be checked
         If myRange.Paragraphs(1).Range.Text = _
         myRange.Paragraphs(2).Range.Text Then
         myRange.Paragraphs(2).Range.Delete
         AmountMoved = myRange.MoveEnd(Unit:=wdParagraph, Count:=1)
        Else
            
            'if two paragraphs aren't identical, add the one after
            'that to my range, so it can be checked, and drop the first one,
            'since it is no longer of interest.
            AmountMoved = myRange.MoveEnd(Unit:=wdParagraph, Count:=1)
            myRange.MoveStart Unit:=wdParagraph, Count:=1
       End If
    
    Loop
LibreOffice 5.2.7.2 (x64) on Windows 7 Professional
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Word/VBA > LibreOffice Macro Conversion

Post by Zizi64 »

I had developed some capacity to modify existing macros in Word – I frequently use a combination of a dozen or so relatively basic VBA macros.

I'm hoping to duplicate them in LibreOffice.
The StarBasic+API (Application Programming Interface) and the MS VBA are NOT compatible. ("StarBasic" was the original name of the built-in Basic IDE). You need rewrite (or re-record) all of your VBA macros for the Apache Openoffice or for the LibreOffice.
The LibreOffice has a littlebit higher compatibility with the foreign fileformats, and with the VBA macros, but there is not (never was and newver will be) 100 % compatibility:
Try to use the "Option VBASupport 1" option at the beginning of the original VBA macro code in all of the macro Modules.


Note:
- The Macro Recorder can work only in the Writer and Calc applications of the AOO and LO.
- It is better to WRITE your macros than to record them. But you must study the API functions first... (The StarBasic is a very simple Basic intepreter and IDE with a few built-in functions and procedures only; the real "knowledge and potential" is in the API functions. You need call the API functions from your StarBasic macro code or from other supported programming languages like the BeanShell, JavaScript, Python).)
Last edited by Zizi64 on Fri Jul 14, 2017 7:05 am, edited 4 times in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Word/VBA > LibreOffice Macro Conversion

Post by Zizi64 »

7) Remove umlauts, macrons, etc...

Code: Select all

Dim myStoryRange As Range
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
    .MatchWildcards = True
    .Execute FindText:="[áàâäãå]", ReplaceWith:="a", Replace:=wdReplaceAll
    .Execute FindText:="[ÁÀÂÄÃÅ]", ReplaceWith:="A", Replace:=wdReplaceAll
    .Execute FindText:="[ç¢]", ReplaceWith:="c", Replace:=wdReplaceAll
    .Execute FindText:="[Ç]", ReplaceWith:="C", Replace:=wdReplaceAll
    .Execute FindText:="[éèêë]", ReplaceWith:="e", Replace:=wdReplaceAll
    .Execute FindText:="[ÉÈÊË]", ReplaceWith:="E", Replace:=wdReplaceAll
    .Execute FindText:="[íìîï]", ReplaceWith:="i", Replace:=wdReplaceAll
    .Execute FindText:="[ÍÌÎÏ]", ReplaceWith:="I", Replace:=wdReplaceAll
    .Execute FindText:="[ñ]", ReplaceWith:="n", Replace:=wdReplaceAll
    .Execute FindText:="[Ñ]", ReplaceWith:="N", Replace:=wdReplaceAll
    .Execute FindText:="[ðóòôöõø]", ReplaceWith:="o", Replace:=wdReplaceAll
    .Execute FindText:="[ÐÓÒÔÖÕØ]", ReplaceWith:="O", Replace:=wdReplaceAll
    .Execute FindText:="[úùûü]", ReplaceWith:="u", Replace:=wdReplaceAll
    .Execute FindText:="[ÚÙÛÜ]", ReplaceWith:="U", Replace:=wdReplaceAll
    .Execute FindText:="[ýÿ]", ReplaceWith:="y", Replace:=wdReplaceAll
    .Execute FindText:="[ÝŸ]", ReplaceWith:="Y", Replace:=wdReplaceAll
    .Execute FindText:="[š]", ReplaceWith:="s", Replace:=wdReplaceAll
    .Execute FindText:="[Š]", ReplaceWith:="S", Replace:=wdReplaceAll
    .Execute FindText:="[ž]", ReplaceWith:="z", Replace:=wdReplaceAll
    .Execute FindText:="[Ž]", ReplaceWith:="Z", Replace:=wdReplaceAll
...

...
Here a topic with similar macro like your find-and-replace macro:

viewtopic.php?f=21&t=2437
Last edited by Zizi64 on Sat Jul 15, 2017 11:41 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
REH-CAE-RPT
Posts: 14
Joined: Wed Jul 12, 2017 9:28 pm

Re: Word/VBA > LibreOffice Macro Conversion

Post by REH-CAE-RPT »

Thanks.

For the seventh one, I've basically changed the find/replace characters, and this seems to work:

Code: Select all

 Sub basic_letter_characters_only
    oDoc = thisComponent
    aFind = Array("  ", "á", "à", "â", "ä", "ã", "å", "ā", "Á", "À", "Â", "Ä", "Ã", "Å", "Ā", "ç", "¢", "Ç", "é", "è", "ê", "ë", "ē", "É", "È", "Ê", "Ë", "Ē", "í", "ì", "î", "ï", "ī", "Í", "Ì", "Î", "Ï", "Ī", "ñ", "Ñ", "ð", "ó", "ò", "ô", "ö", "õ", "ø", "ō", "Ð", "Ó", "Ò", "Ô", "Ö", "Õ", "Ø", "Ō", "ú", "ù", "û", "ü", "ū", "Ú", "Ù", "Û", "Ü", "Ū", "ý", "ÿ", "ȳ", "Ý", "Ÿ", "Ȳ", "š", "Š", "ž", "Ž", "ß")
    aReplace = Array(" ", "a", "a", "a", "a", "a", "a", "a", "A", "A", "A", "A", "A", "A", "A", "c", "c", "C", "e", "e", "e", "e", "e", "E", "E", "E", "E", "E", "i", "i", "i", "i", "i", "I", "I", "I", "I", "I", "n", "N", "o", "o", "o", "o", "o", "o", "o", "o", "O", "O", "O", "O", "O", "O", "O", "O", "u", "u", "u", "u", "u", "U", "U", "U", "U", "U", "y", "y", "y", "Y", "Y", "Y", "s", "S", "z", "Z", "ss")
    aRayCount = 0
    FandR = oDoc.createReplaceDescriptor
    FandR.SearchCaseSensitive = true
    FandR.SearchRegularExpression = true
    While aRayCount <= uBound(aFind)
    FandR.setSearchString(aFind(aRayCount))
    FandR.setReplaceString(aReplace(aRayCount))
    aRayCount = aRayCount + 1
    oDoc.ReplaceAll(FandR)
    Wend
    End Sub
LibreOffice 5.2.7.2 (x64) on Windows 7 Professional
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Word/VBA > LibreOffice Macro Conversion

Post by Zizi64 »

1) Remove any formatting and change all text to lower case, Arial font, size 11:
The 'Styles' is the most valuable feature of the AOO and LO. Use them instead of changing the manual (direct) formatting properties of a text document.

To reset all of the direct formatting properties:
Ctrl-A (select all) - Ctrl-M (reset all of the direct formatting property to the default properties of the applied style.
Then choose/create a paragraph style, adjust its properties and apply it for the selected paragraph. And use the other types of the styles (character-, frame- and page styles).


And there are two icons in the toolbar set (maybe you need to toolbar settings, if you want to display these icons), that can change the upper/lover case state of the selected characters:
UPPER_lower.png
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Word/VBA > LibreOffice Macro Conversion

Post by Zizi64 »

2) Start each sentence on a new line:
Do you meant a NEW LINE or a NEW PARAGRAPH?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Word/VBA > LibreOffice Macro Conversion

Post by JeJe »

See this book on how to enumerate paragraphs so you can compare paragraph strings and so on

http://www.pitonyak.org/OOME_3_0.pdf

This for regular expressions which should help remove spaces before and after paragraphs

https://wiki.openoffice.org/wiki/Docume ... t_.5Cn_.24
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
REH-CAE-RPT
Posts: 14
Joined: Wed Jul 12, 2017 9:28 pm

Re: Word/VBA > LibreOffice Macro Conversion

Post by REH-CAE-RPT »

Do you meant a NEW LINE or a NEW PARAGRAPH?
New paragraph. Sorry.
This for regular expressions which should help remove spaces before and after paragraphs

https://wiki.openoffice.org/wiki/Docume ... t_.5Cn_.24
This is excellent. Thank you.
LibreOffice 5.2.7.2 (x64) on Windows 7 Professional
Post Reply