Retrieving text from cell comments (notes) via macro.

[sourcecode.9999]

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

[FJCC]

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?

[sourcecode.9999]

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.

[FJCC]

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.