Listing Cell Notes

September 14th, 2005

Here is a simple macro that creates a new sheet in the Calc document with a listing of all the notes (comments) found.

Notes can be added to any cell with Insert - Note - see below.

The Basic code for gathering all of these notes and adding them to a new sheet is given below. Items in the listing I’d like to draw your attention to..

· Creating new sheets with the insertNewByName method.

· The PrintableAddressOfCell and ColumnNumberToString routines convert row and column cell offsets to human readable notation - and were written by Andrew Pitonyak.

· The notes (annotations) for each sheet are traversed using a For loop in the exact same way as the sheets of the document.

Sub AddCommentSheet

Dim oSheets, oSheet
Dim oRange, oCell
Dim oAnnotations, oNote
Dim i As Integer
Dim j As Integer

oSheets = ThisComponent.Sheets

oSheets.insertNewByName (”Comments”, oSheets.getCOunt())

oSheet = oSheets.getByName(”Comments”)

oRange = oSheet.getCellRangeByName(”B1:C1″)
oRange.merge(True)

oCell = oSheet.getCellByPosition(1,0)
oCell.setString(”Comment listing”)
oCell.CellBackColor = 16764057
oCell.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER

oCell = oSheet.getCellByPosition(1,1)
oCell.setString(”Created on …” & Now())

oCell = oSheet.getCellByPosition(1,3)
oCell.setString(”Sheet”)
oCell.CellBackColor = 16764057
oCell = oSheet.getCellByPosition(2,3)
oCell.setString(”Cell”)
oCell.CellBackColor = 16764057
oCell = oSheet.getCellByPosition(3,3)
oCell.setString(”Note”)
oCell.CellBackColor = 16764057

CurRow = 4
For j = 0 To oSheets.getCount()-1
oSheet2 = oSheets.getByIndex(j)
oAnnotations = oSheet2.getAnnotations()

For i = 0 To oAnnotations.getCount()-1
oNote = oAnnotations.getByIndex(i)
oCell = oSheet.getCellByPosition(1,CurRow+i)
oCell.setString(oSheet2.Name)
oCell = oSheet.getCellByPosition(2,CurRow+i)
oCell.setString(PrintableAddressOfCell(oNote.getParent())
oCell = oSheet.getCellByPosition(3,CurRow+i)
oCell.setString(oNote.getString())
Next
CurRow = CurRow + i
Next

End Sub

Function PrintableAddressOfCell(oCell) As String
If IsNull(oCell) OR IsEmpty(oCell) Then
PrintableAddressOfCell = “Unknown”
Else
PrintableAddressOfCell = ColumnNumberToString(oCell.CellAddress.Column) &_
Cstr(oCell.CellAddress.Row+1)
End If
End Function

Function ColumnNumberToString(ByVal nColumn As Long) As String
Dim s As String
Do While nColumn >= 0
s = Chr$(65 + (nColumn MOD 26)) & s
nColumn = nColumn \ 26 - 1
Loop
ColumnNumberToString = s
End Function

The newly created comment sheet is shown below.

Posted in OpenOffice Basic | No Comments»

Дата добавления: 2015-08-03