macros - How to autofill OpenOffice Math formula editor in OOo Calc? -

i using openoffice calc spreadsheet formulas psuedo-random numbers generate arrays of arithmetic problems can update creating new worksheets (i'm teacher)

problems output formula mark-ups in string form. ooo math formulas use these string commands typed editor display nicely formatted maths expressions.

i can next step manually:

1) go source cell , copy string mark-up clipboard   2) select target cell , clear existing contents , objects   3) create new math object anchored target cell   4) open math editor window , paste in mark-up string   5) exit math editor window , return cursor source cell 

result: nice maths expression of given arithmetic problem.

i need able entire columns of source cells on various sheets. ...even better, add listener dynamically update sources updated.

i found code here: cell content inside formula achieves fixed pair of cells, despite best efforts, have had admit defeat - generalising code beyond expertise!

the absolute ideal macro function call spreadsheet function; input arguments (sourcecell, targetcell, listeneron/off) run above algorithm , dynamically update if required.

can me? solution this, or kind of workaround immensely helpful.

---

update 2016/10/27

thank jim k, did work, use of dispacher comes whole host of difficulties hadn't foreseen. found charlie young's post in openoffice forum makes use of api. have included adaptation of code below.

can me integrate function in similar way i've described? don't know how solve placement of math object in target cell.

the api code great create new math object each time code updated. existing ones need deleted though.

i think limitation of not being able delete existing objects within function going persist. case if done subroutine called function?

function insertformula(parafromcell, paratocell)    dim odoc object    dim osheet object    dim oshape object     odoc = thiscomponent    osheet = odoc.sheets(0)     oshape = odoc.createinstance("com.sun.star.drawing.ole2shape")    oshape.clsid = "078b7aba-54fc-457f-8551-6147e776a997"     osheet.drawpage.add(oshape)    oshape.model.formula = parafromcell     oshape.setsize(oshape.originalsize) end function 

---

next update

i've been managing solve own problems quite now...

i've decided go sub, not function, can access sheet delete existing objects. code attached - source cells in column c , target cells in matching rows of column a. far able send objects $a$1.

how anchor each new object specific cell?

rem  *****  basic  *****  sub insertthisformula    dim odoc object   dim osheet object   dim oshape object   dim sourcecell object   dim targetcell object    odoc = thiscomponent   osheet = odoc.sheets(1)    dim n integer   n = 1 'number of rows of formulas    = 0 n-1      rem loop through cells      sourcecell = osheet.getcellbyposition(2, i)      targetcell = osheet.getcellbyposition(0, i)       rem clear target cell object/s      targetcell.clearcontents(128)       oshape = odoc.createinstance("com.sun.star.drawing.ole2shape")      oshape.clsid = "078b7aba-54fc-457f-8551-6147e776a997"       osheet.drawpage.add(oshape)      oshape.model.formula = sourcecell.string       oshape.setsize(oshape.originalsize)    next  end sub 

starting mifeet's example, add my macros:

rem ---------------------------------------------------------------------- rem creates math formula text function insertformulafromcell(paramcellfrom, paramcellto)     dim document object     dim dispatcher object     document = thiscomponent.currentcontroller.frame     dispatcher = createunoservice("com.sun.star.frame.dispatchhelper")      rem go cell containing markup , copy     dim fromcellargs(0) new com.sun.star.beans.propertyvalue     fromcellargs(0).name = "topoint"     fromcellargs(0).value = paramcellfrom     dispatcher.executedispatch(document, ".uno:gotocell", "", 0, fromcellargs())     dispatcher.executedispatch(document, ".uno:copy", "", 0, array())      rem go cell want formula displayed     dim tocellargs(0) new com.sun.star.beans.propertyvalue     tocellargs(0).name = "topoint"     tocellargs(0).value = paramcellto     dispatcher.executedispatch(document, ".uno:gotocell", "", 0, tocellargs())      rem open star.math     odesk = createunoservice ("com.sun.star.frame.desktop")     dispatcher.executedispatch(document, ".uno:insertobjectstarmath", "", 0, array())     document = thiscomponent.currentcontroller.frame     dispatcher = createunoservice("com.sun.star.frame.dispatchhelper")      rem paste clipboard using array() place-holder variable name     dispatcher.executedispatch(document, ".uno:paste", "", 0, array())      rem exit star.math     dispatcher.executedispatch( _         document, ".uno:terminateinplaceactivation", "", 0, array())     insertformulafromcell = "math formula updated " & now() end function 

to run it, put formula in cell c5:

=insertformulafromcell("$c$3","$c$20") 

now when values updated, creates formula.

note: not .uno:delete section of mifeet's code work, perhaps because functions not supposed access other cells. may require manually deleting formulas before creating new ones.