Apache OpenOffice Community Forum

Posted: **Fri Jan 18, 2013 2:41 am**

I need to harvest and colate data from an oOcalc workbook. Part of the information is presented as comments on the cell.

I cant figure out a formula to do it. So I tried to make a macro function to facilitate my aims but I cant make my code work.
Im not familiar with oOcalc DOM's to manipulate the item.

Hope someone can help me out. Thanks.

Code: Select all

Function noteparse( ocol As Variant )

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSelectedCells = ThisComponent.CurrentSelection
oActiveCell = oSelectedCells.CellAddress
dispatcher.executeDispatch(document, ".uno:DrawEditNote", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertAnnotation", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:DrawEditNote", "", 0, Array())
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = ocol
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = oActiveCells
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
End Function

Posted: **Fri Jan 18, 2013 7:11 am**

I'm not entirely sure what you are trying to do. This code prints out the text of all the comments on Sheet1.

Code: Select all

oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
oAnno = oSheet1.getAnnotations()
Enum = oAnno.createEnumeration()
While Enum.hasMoreElements
	Comment = Enum.nextElement()
	Print Comment.String
WEnd

Can you use something like that or explain more about what you need?

Posted: **Wed Jan 30, 2013 3:03 am**

FJCC wrote:I'm not entirely sure what you are trying to do. This code prints out the text of all the comments on Sheet1.
Code: Select all
oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
oAnno = oSheet1.getAnnotations()
Enum = oAnno.createEnumeration()
While Enum.hasMoreElements
	Comment = Enum.nextElement()
	Print Comment.String
WEnd
Can you use something like that or explain more about what you need?

Thanks FJCC,

I was trying to create a function to retrieve the comments from the cell I specified in the funtion.
The usage I was planning for it is as follows:

if supporse i will type =noteparse(A1), it is intended to return the annotations from A1

I still cant figure out how to do it.

Posted: **Wed Jan 30, 2013 5:24 am**

This can be done but there is not a simple solution. The While loop in my previous code will return every comment on the sheet. Each comment has a Parent property that is the cell containing the comment. The cell has an AbsoluteName of the form $Sheet1.$A$1. To see the cell name you could write

Code: Select all

oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
oAnno = oSheet1.getAnnotations()
Enum = oAnno.createEnumeration()
While Enum.hasMoreElements
   Comment = Enum.nextElement()
   Print Comment.Parent.AbsoluteName
WEnd

With enough code you could parse that AbsoluteName and find the cell that matches the one you want.

Apache OpenOffice Community Forum

Retrieving text from cell comments (notes) via macro.

Retrieving text from cell comments (notes) via macro.

Re: Retrieving text from cell comments (notes) via macro.

Re: Retrieving text from cell comments (notes) via macro.

Re: Retrieving text from cell comments (notes) via macro.