Apache OpenOffice Community Forum

Posted: **Wed Jul 12, 2017 9:51 pm**

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

Posted: **Fri Jul 14, 2017 6:48 am**

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

Posted: **Fri Jul 14, 2017 6:57 am**

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

Posted: **Sat Jul 15, 2017 10:21 am**

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

Posted: **Sat Jul 15, 2017 11:23 am**

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:

Posted: **Sat Jul 15, 2017 11:44 am**

2) Start each sentence on a new line:

Do you meant a NEW LINE or a NEW PARAGRAPH?

Posted: **Sun Jul 16, 2017 12:03 am**

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

Posted: **Wed Aug 02, 2017 9:11 pm**

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.

Apache OpenOffice Community Forum

Word/VBA > LibreOffice Macro Conversion

Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion

Re: Word/VBA > LibreOffice Macro Conversion