Is there a command that quickly finds all the cells with comments (annotations) in a spreadsheet e displays their addresses? I know that the code “Range.queryContentCells( 8 ).Count” counts all the cells with comments in a range.
Do you mean something like this?
Function getCommentRng(incell As String) As Variant
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oRange As Variant
Dim oQueryResult As Variant
Dim oCell As Variant
Dim i As Long
Dim oAnnotation As Variant
On Error Resume Next
oSheets = ThisComponent.getSheets()
oCellRangesByName = oSheets.getCellRangesByName(incell)
Rem Of course, you can process not the first of the specified ranges, but all of them - this is just an example of use
oRange = oCellRangesByName(0)
oQueryResult = oRange.queryContentCells( 8 )
ReDim oAnnotation(0 To oQueryResult.getCount()+1, 0 To 1)
oAnnotation(UBound(oAnnotation,1)-1,0) = "---"
oAnnotation(UBound(oAnnotation,1)-1,1) = "---"
oAnnotation(UBound(oAnnotation,1),0) = ""
oAnnotation(UBound(oAnnotation,1),1) = ""
i = -1
For Each oCell In oQueryResult
i = i + 1
oAnnotation(i,0) = oCell.AbsoluteName
oAnnotation(i,1) = oCell.getAnnotation().getString()
Next oCell
getCommentRng = oAnnotation
End Function
Call it as `{=GETCOMMENTRNG(“A1:B8”)}’
navigating in iterators :
Can I export/print a list of the comments in a Calc spreadsheet?
Copy simple text from both values and comments (.ods file) an maintain pairing? - #6 by karolus
Thanks a lot, you helped enormously.
A sloppy variant:
Sub findDescribeSelect()
cCtrl = ThisComponent.CurrentController
cSheet = cCtrl.ActiveSheet
annoRgs = cSheet.queryContentCells(8)
out = annoRgs.RangeAddressesAsString
out = Join(Split(out, ";"), Chr(10))
MsgBox(out)
cCtrl.select(annoRgs)
End Sub
See attached example:_
disask121346AnnotationFinder.ods (12.9 KB)
1 Like
Simple, beautiful, and elegant…Thanks a million
if two consecutive cells have comments (c2 and c3 for example), it will show one result (range Sheet.c2:c3). Is there something like “CellAddressasString”?
As my example sheet shows, also the address of a single cell can be accessed via RangeAddressesAsString and the plural can be ignored. That’s because also single cells support the service (API) “com.sun.star.sheet.SheetCellRange”, and can therefore be elements of a SheetCellRanges object.
The needed addition to my code allowing to get each address of an annotated cell separately (as a line of the message) doesn’t depend on an additional property, but on the resolution of ranges consisting of more than one cell into single cells.
This can be done, of course by user code, but the API (giving access to cell or range properties) has no special support for it.
If you want taylor-made user code you may need to study user programming for LibO to some detail.