calc: how can I pass the content of a cell to oleobject math and paste the result in a sheet using a macro? [closed]

Calc: how can I pass the content of a cell to oleobject math and paste the result in a sheet? I want to see the formula contained in a cell in a Math object near the cell both for formatting the document and checking the formula. (I work alost only with names in calc).

Math is fine for me and it is easy to programmaticaly format simple formulas so they become accetable for math, but I would like to link the content of the object to the cell without doing the cut paste and edit every time i make a change to a formula.

something like

I have found something here : https://wiki.documentfoundation.org/i... at page 45, but i need someone else advice and help to make it work. I believe it could be of help for many people.

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-04 19:16:37.522455

It is not clear, what you want to do. Calc formulas are for calculating with numbers and texts. Math is for typesetting formulas and do not have a 'result'. If you want that the formula contained in cell A1 is shown in cell A2, you would write in A2 =formula(A1) . There is no Math involved.

( 2014-10-04 16:03:15 +0200 )edit

thanks for answering!. From A Libreofficebasic or java macro I want to 1. read the formula in cell A1 "pi()/64*(D_e^4-d_i^4)" 2. translate in a string that Math can understand "%pi over 64 cdot (D_e^4-d_i^4)" 3. send to Libreoffice Math 4. receive the object and attach over cell A2. I want to make it with macro in order to refresh the many formulas of my sheet every time i need it without doing the job by keyb. and mouse.

( 2014-10-06 12:22:09 +0200 )edit

for anybody intersted https://wiki.documentfoundation.org/i... is an up to date guide for libreoffice 4.1x

( 2014-10-07 14:15:39 +0200 )edit

Sort by » oldest newest most voted

That is a complex task. You will find enough information how to read a formula from a cell or write a formula. Here some additions for the Math object. The code is not ready to use, but shows you some solutions.

The first one is for first time writing the Math objects.

sub AddMathToCell(byval nCol as long, byval nRow as long, byval nSheet as long)
rem Make sure it is a spreadsheet document and sheet exists
dim oDoc as variant: oDoc=thisComponent
dim sDocType as string: sDocType = oDoc.Identifier
exit sub
end if
dim oSheets as variant: oSheets = oDoc.Sheets
if oSheets.count <= nSheet then
msgbox "Sheet does not exists"
exit sub
end if
dim oOneSheet as variant: oOneSheet = oSheets.getByIndex(nSheet)
dim oOneCell as variant: oOneCell = oOneSheet.getCellByPosition(nCol,nRow)
dim oDrawPage as variant: oDrawPage = oOneSheet.DrawPage
dim oMathOLE as variant: oMathOLE = oDoc.createInstance("com.sun.star.drawing.OLE2Shape")
dim oPos as new com.sun.star.awt.Point
Dim oSize as new com.sun.star.awt.Size
oPos.x = oOneCell.Position.X
oPos.y = oOneCell.Position.Y
oMathOLE.Position = oPos
oSize.Width = oOneCell.Size.Width
oSize.Height = oOneCell.Size.Height
oMathOLE.Size = oSize
oMathOLE.CLSID = "078B7ABA-54FC-457F-8551-6147e776a997"
dim oMathDoc as variant: oMathDoc = oMathOLE.Model
dim sFormel as string: sFormel ="{a^2-b^2}over{a-b}"
oMathDoc.Formula = sFormel
oDoc.Modified = true
end sub


The second one is for accessing the existing Math objects.

sub ShowAllMathFormulas(byval nSheet as long)
rem Make sure it is a spreadsheet document and sheet exists
dim oDoc as variant: oDoc=thisComponent
dim sDocType as string: sDocType = oDoc.Identifier
exit sub
end if
dim oSheets as variant: oSheets = oDoc.Sheets
if oSheets.count <= nSheet then
msgbox "Sheet does not exists"
exit sub
end if
dim oOneSheet as variant: oOneSheet = oSheets.getByIndex(nSheet)
dim oDrawPage as variant: oDrawPage = oOneSheet.DrawPage
dim nDrawObjectCount as integer: nDrawObjectCount = oDrawPage.count
dim i as integer
dim oShape as variant
dim oModel as variant
dim sMathFormula as string
for i = 0 to nDrawObjectCount - 1
oShape = oDrawPage.getByIndex(i)
if oShape.supportsService("com.sun.star.drawing.OLE2Shape") then
if oShape.CLSID = "078B7ABA-54FC-457F-8551-6147e776a997" then
rem It is a formula object
oModel = oShape.model
sMathFormula = oModel.formula
msgbox(sMathFormula)
else
rem external OLE do not have a model, but all have a CLSID
msgbox(oShape.CLSID)
end if
else
msgbox(oShape.ShapeType)
end if
msgbox(oShape.Anchor.AbsoluteName)
next i
end sub

more

thanks a lot for your help. I will try to use and develope it further and give further feedback! danke Viel Mal!

( 2014-10-07 11:11:16 +0200 )edit

I did it in Excel. It is quick and dirty, using sendkeys which do not work well nor is reliable, I wrote the macro in VB for Excel and called LibreOffice.MathDocument The math document stays open after the sendkey sequence so I can finish the edit. I do not know how to "exit and Return Data to microsoft Excel" except clicking on Excel. Anybody that would translate the macro for Calc is really wellcome. I have found important help on stackoverflow and msdn searching "OLEObject" "VBA" "Excel".

Sub insertFormula()

Dim ws As Excel.Worksheet
Dim Oggetto As OLEObject

Set ws = ActiveWorkbook.ActiveSheet
With ws
True, DisplayAsIcon:=False)
End With

Dim equation As Variant
equation = " " & Selection.Formula

ActiveCell.Copy
ActiveCell.Offset(0, 1).Select

Oggetto.Activate
'Oggetto.name is the name of the object in case of later needs
' it would be useful to know more properties, but in excel 2003
'       do not exist the way to enumerate them

For i = 1 To Len(equation)
carattere = Mid(equation, i, 1)

Select Case carattere
Case "~"
spedisci = "{~}"
Case "("
spedisci = "{(}"
Case ")"
spedisci = "{)}"
Case "["
spedisci = "{[}"
Case "]"
spedisci = "{]}"
Case "+"
spedisci = "{+}"
Case "^"
spedisci = "{^}"
Case "%"
spedisci = "{%}"
Case Else
spedisci = carattere
End Select

'MsgBox " - " & carattere & "  " & spedisci & "  -"
SendKeys spedisci, True     ' Send keystrokes to Calculator
Next
AppActivate "Microsoft Excel"    ' Attiva Microsoft Excel

End Sub

more

Do you want to run the macro only on a selected cell?

Do you want to place the Math object always one column to the right of the formula cell in Calc?

Do you want to edit the Math object immediately when creating it?

( 2014-10-07 12:40:20 +0200 )edit

Gruss Regina! I would like to do all of the Three and more :-)! What I need: see the formula in the cell in a human readable format so that me and who else read the sheet can understand so that it's possible to write printable sheet where the calculation are documented. A further step is to "Update" the math objects in case the cells are changed. For Now I just delete the old object and remake it. A better way could be to link somehow the object to the specific cell.

( 2014-10-07 13:19:02 +0200 )edit

There is a lot of improvement possible for this. A skilled programmer could write a nice and very useful add on here. I will pick up this thing later and try to develop it a little further. Unfortunately I have been using excel for tenth of years and VBA is documented and deeply tested by millions of users, while macro for calc are young and, to me, an unknown territory and a strange foreign language. Danke Viel Mal, Bis nexte Mal!

( 2014-10-07 13:28:39 +0200 )edit