Page 1 of 1

Retrieving text from cell comments (notes) via macro.

Posted: Fri Jan 18, 2013 2:41 am
by 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

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

Posted: Fri Jan 18, 2013 7:11 am
by 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?

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

Posted: Wed Jan 30, 2013 3:03 am
by sourcecode.9999
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.

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

Posted: Wed Jan 30, 2013 5:24 am
by 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.