[Solved] Mass "find and replace" for formulas

[foreverconfused]

So, I have a Draw/Impress Document with a few hundred formulas on it. I would like to mass replace all instances of {n over 2} in the formulas with {1 over 2n}.
Can this be achieved with macros?

[FJCC]

Can it be done? Yes. Might it take far longer to write the macro than to do the task manually? Also yes.
Here is some code that iterates over the slides of an Impress presentation, iterates over the objects on each slide, checks if the object is an OLE, if it is, it gets its Component and tests if "{n over 2} is in the formula.

Code: Select all

oDrawPages = ThisComponent.getDrawPages()
For PageIdx = 0 to oDrawPages.Count - 1
  oDPage = oDrawPages.getByIndex(PageIdx)

  For ObjIdx = 0 to oDPage.Count - 1
    oObj = oDPage.getByIndex(ObjIdx)
    If oObj.supportsService("com.sun.star.drawing.OLE2Shape") Then
      oEmbeddedObject = oObj.EmbeddedObject
      oComponent = oEmbeddedObject.getComponent()
      sFormula = oComponent.Formula 
      print "Position of {n over 2} = " & InStr(sFormula, "{n over 2}"
    End If
    
  Next ObjIdx

Next PageIdx 

That isn't exactly what you want. Changing the string using Basic would require using the Len(), Left(), and RIght() functions, I think.
I do not know if testing for the service OLE2shape is enough to pick out formulas in your document.
There may be many other problems handling the details of our document. If you start on this project, make a backup of your file first.

[Lupp]

Are you (the questioner) the same person posting under the UserName "ExtremeConfusion" in ask.libreoffice.org ?

[foreverconfused]

Can it be done? Yes. Might it take far longer to write the macro than to do the task manually? Also yes.
Here is some code that iterates over the sides of an Impress presentation, iterates over the objects on each slide, checks if the object is an OLE, if it is, it gets its Component and tests if "{n over 2} is in the formula.

Code: Select all

oDrawPages = ThisComponent.getDrawPages()
For PageIdx = 0 to oDrawPages.Count - 1
  oDPage = oDrawPages.getByIndex(PageIdx)

  For ObjIdx = 0 to oDPage.Count - 1
    oObj = oDPage.getByIndex(ObjIdx)
    If oObj.supportsService("com.sun.star.drawing.OLE2Shape") Then
      oEmbeddedObject = oObj.EmbeddedObject
      oComponent = oEmbeddedObject.getComponent()
      sFormula = oComponent.Formula 
      print "Position of {n over 2} = " & InStr(sFormula, "{n over 2}"
    End If
    
  Next ObjIdx

Next PageIdx 

That isn't exactly what you want. Changing the string using Basic would require using the Len(), Left(), and RIght() functions, I think.
I do not know if testing for the service OLE2shape is enough to pick out formulas in your document.
There may be many other problems handling the details of our document. If you start on this project, make a backup of your file first.

Thank you, I was hoping that a macro to just replace a given string with something else would be relatively simple. I can see all the formulas that have "n over 2" (almost all of them). The problem is to find a script to actually replace them.

Are you (the questioner) the same person posting under the UserName "ExtremeConfusion" in ask.libreoffice.org ?

Yes

[foreverconfused]

After many mostly unsuccessful tries, I managed to produce this one. This actually works and replaces all the formulas accordingly, at least in the same page/slide. I haven't tried it with multiple pages. There are 3 cases of

Code: Select all

sNewFormula = Replace(sOldFormula,  "n over 2", "{1 over 2n}")

in the script. And the credit goes to Qwen/Qwen2.5-72B-Instruct (most others I have tried failed to produce any executable result):

Code: Select all

Sub ReplaceFormulasInDocument()
    Dim oDoc As Object
    Dim oDrawPages As Object
    Dim oDrawPage As Object
    Dim oShape As Object
    Dim i As Integer
    Dim j As Integer
    Dim sOldFormula As String
    Dim sNewFormula As String
    Dim oFormula As Object
    Dim oForms As Object
    Dim oForm As Object
    Dim oControls As Object
    Dim oControl As Object

    ' Get the current document
    oDoc = ThisComponent

    ' Check if the document is a Writer, Impress, Draw, Calc, Math, or Base document
    If oDoc.SupportsService("com.sun.star.text.TextDocument") Then
        ' Handle Writer document
        oDrawPages = oDoc.DrawPages
    ElseIf oDoc.SupportsService("com.sun.star.presentation.PresentationDocument") Then
        ' Handle Impress document
        oDrawPages = oDoc.getDrawPages()
    ElseIf oDoc.SupportsService("com.sun.star.drawing.DrawingDocument") Then
        ' Handle Draw document
        oDrawPages = oDoc.getDrawPages()
    ElseIf oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
        ' Handle Calc document
        oDrawPages = oDoc.getDrawPages()
    ElseIf oDoc.SupportsService("com.sun.star.formula.FormulaProperties") Then
        ' Handle Math document
        sOldFormula = oDoc.Formula
        sNewFormula = Replace(sOldFormula, "n over 2", "{1 over 2n}")
        If sOldFormula <> sNewFormula Then
            oDoc.Formula = sNewFormula
        End If
        MsgBox "All formulas have been replaced in the Math document.", 64, "Success"
        Exit Sub
    ElseIf oDoc.SupportsService("com.sun.star.sdb.DatabaseDocument") Then
        ' Handle Base document
        oForms = oDoc.DatabaseForms
        For Each oForm In oForms
            oControls = oForm.DrawPage.getDrawPage().getByIndex(0).getDrawPage().getByIndex(0).getControls()
            For Each oControl In oControls
                If oControl.supportsService("com.sun.star.drawing.OLE2Shape") Then
                    oFormula = oControl.Model
                    If oFormula.supportsService("com.sun.star.formula.FormulaProperties") Then
                        sOldFormula = oFormula.Formula
                        sNewFormula = Replace(sOldFormula, "n over 2", "{1 over 2n}")
                        If sOldFormula <> sNewFormula Then
                            oFormula.Formula = sNewFormula
                        End If
                    End If
                End If
            Next oControl
        Next oForm
        MsgBox "All formulas have been replaced in the Base document.", 64, "Success"
        Exit Sub
    Else
        MsgBox "This macro is designed to work with Writer, Impress, Draw, Calc, Math, and Base documents only.", 64, "Error"
        Exit Sub
    End If

    ' Loop through all draw pages
    For i = 0 To oDrawPages.Count - 1
        oDrawPage = oDrawPages(i)
        
        ' Loop through all shapes in the draw page
        For j = 0 To oDrawPage.Count - 1
            oShape = oDrawPage(j)
            
            ' Check if the shape is an OLE2Shape
            If oShape.supportsService("com.sun.star.drawing.OLE2Shape") Then
                ' Get the OLE2 object
                oFormula = oShape.Model
                
                ' Check if the OLE2 object is a Math formula
                If oFormula.supportsService("com.sun.star.formula.FormulaProperties") Then
                    ' Get the formula
                    sOldFormula = oFormula.Formula
                    sNewFormula = Replace(sOldFormula, "n over 2", "{1 over 2n}")
                    
                    ' If the formula has changed, update it
                    If sOldFormula <> sNewFormula Then
                        oFormula.Formula = sNewFormula
                    End If
                End If
            End If
        Next j
    Next i

    MsgBox "All formulas have been replaced.", 64, "Success"
End Sub