« Basic functions: CHOOSE | Main | Using Names as constants »
September 14, 2005
Listing Cell Notes
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 by Dave at September 14, 2005 10:09 PM