Ask Your Question
0

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

asked 2014-10-03 14:30:06 +0200

bigmarck gravatar image

updated 2014-10-07 13:49:18 +0200

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
ActiveSheet.OLEObjects.Add(ClassType:="soffice.StarMathDocument.6", Link:= _ True, DisplayAsIcon:=False).Activate

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 flag offensive 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

Comments

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.

Regina gravatar imageRegina ( 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.

bigmarck gravatar imagebigmarck ( 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

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

2 Answers

Sort by » oldest newest most voted
0

answered 2014-10-07 04:14:39 +0200

Regina gravatar image

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
if sDocType <> "com.sun.star.sheet.SpreadsheetDocument" then
    msgbox "Only for spreadsheets"
    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"
oDrawPage.add(oMathOLE)
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
if sDocType <> "com.sun.star.sheet.SpreadsheetDocument" then
    msgbox "Only for spreadsheets"
    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
edit flag offensive delete link more

Comments

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

bigmarck gravatar imagebigmarck ( 2014-10-07 11:11:16 +0200 )edit
0

answered 2014-10-07 11:08:51 +0200

bigmarck gravatar image

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
    Set Oggetto = .OLEObjects.Add(ClassType:="LibreOffice.MathDocument.1", Link:= _
        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
edit flag offensive delete link more

Comments

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?

Regina gravatar imageRegina ( 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.

bigmarck gravatar imagebigmarck ( 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!

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

Question Tools

1 follower

Stats

Asked: 2014-10-03 14:30:06 +0200

Seen: 809 times

Last updated: Oct 07 '14