Can I export/print a list of the comments in a Calc spreadsheet?

I have been given a large Calc spreadsheet (5,000+ rows) and I am flagging errors via the comment function. Is there a way of exporting the comments in some way so I can give the end users a list of the errors I’ve found, so the end user doesn’t have to scroll through the document, hoping to find all my comments? I’m using LibreOffice 6.0.6, but the end user will likely use Excel. So I guess a secondary question is, do Calc comments translate nicely into Excel?

1 Like

I hope this helps in solving your problem:

Sub collectComments
Dim oSheets As Variant, oSheet As Variant, oAnnotations As Variant, oAnnotation As Variant
Dim oParent As Variant, sAuthor As String, sDate As String, sAnnotationText As String
Dim i As Long, j As Long, k As Long, aRes As Variant 
	oSheets = ThisComponent.getSheets()
	aRes = Array(Array("Address","Content","Author","Date","Comment"))
	k = 0
	For i = 0 To oSheets.getCount()-1
		oSheet = oSheets.getByIndex(i)
		oAnnotations = oSheet.getAnnotations()
		For j = 0 To oAnnotations.getCount()-1
			oAnnotation = oAnnotations.getByIndex(j)
			oParent = oAnnotation.getParent()
			sAuthor = oAnnotation.getAuthor()
			sDate = oAnnotation.getDate()
			sAnnotationText = oAnnotation.getString()
			k = k + 1
			ReDim Preserve aRes(0 To k)
			aRes(k) = Array(oParent.AbsoluteName, oParent.getString(), sAuthor, sDate, sAnnotationText)
		Next j
	Next i
	If UBound(aRes) > LBound(aRes) Then 
		CreateNewDocument("scalc").getSheets().getByIndex(0).getCellRangeByPosition(0, 0, UBound(aRes(0)), UBound(aRes)).setDataArray(aRes)
End Sub
1 Like


Is there a such macro but for Writer?

Kind regards,

No, @AndrzejWawa, there is no such macro for Writer, not needed. Just turn on the Comment display mode and get the result