Ask Your Question

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

asked 2018-10-08 14:56:07 +0200

artman gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-08 17:21:51 +0200

JohnSUN gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-08 14:56:07 +0200

Seen: 73 times

Last updated: Oct 08 '18