Comment from a cell as a value into another cell

[YanDo]

Hi,

there is a Calc spreadsheet with comments to cells. We need to sort the sheet based on the data in comments. For this we have to put comments into the nearby cells as values.

Found the example of Excel function:

Function getComment(incell) As String
On Error Resume Next  
getComment = incell.Comment.Text 
End Function

But this one returns error (no object defined)

I’ve started to write something on myself, but havn’t found method for a cell how to get out its comment.

Solution should be very simple, any ideas?
fnx )

[JohnSUN]

Not Comment, but Annotation. Not Text, but String. So it will be

getComment = incell.getAnnotation().getString()

It will be so if incell really is a object cell. If it’s just a text string with cell address, you’ll need to write more and more… Something like as

Function getComment(incell As String) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oCell As Variant
Dim oAnnotation As Variant
	On Error Resume Next 
	getComment = ""
	oSheets = ThisComponent.getSheets()
	oCellRangesByName = oSheets.getCellRangesByName(incell)
	oCell = oCellRangesByName(0).getCellByPosition(0,0)
	oAnnotation = oCell.getAnnotation()
	getComment = oAnnotation.getString()
End Function

Call it as

=GETCOMMENT("Sheet2.C4")

[YanDo]

fnx!

I’ve added 2 strings in code like

sTmp = oAnnotation.getString()
getComment = IIf (sTmp = "" , "" , sTmp)

and I call it as

=GETCOMMENT(CELL("ADDRESS";A1))

than it’s easy copypaste/strech

[JohnSUN]

Dear friend, excuse me, what do you mean in this line IIf (sTmp = "" , "" , sTmp)? If sMtp already equal “empty string” then… what?.. Set getComment “empty string”?.. Why?!!

[DanielinBow]

I can’t get this to run. No problem in Excel but this one in Libre office doesn’t seem to work. Grateful for any tips

Function getComment(incell As String) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oCell As Variant
Dim oAnnotation As Variant
On Error Resume Next
getComment = “”
oSheets = ThisComponent.getSheets()
oCellRangesByName = oSheets.getCellRangesByName(incell)
oCell = oCellRangesByName(0).getCellByPosition(0,0)
oAnnotation = oCell.getAnnotation()
getComment = oAnnotation.getString()
End Function