Listing Cell Notes
Archive for the ‘OpenOffice Basic’ Category
« Previous Entries
Macros: A Lotto Number Generator
Saturday, October 22nd, 2005
I have plans to implement a Keno game in the OOo basic language - but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.
Download it here
Sub Lotto
Dim i
Dim j
Dim val
Dim match
oSheet = ThisComponent.Sheets(1)
For i = 1 To 6
oCell = oSheet.getCellByPosition(1,i)
Do
match = False
val = int(rnd()*49) + 1
For j = 1 To i
If val = oSheet.getCellByPosition(1,j).getValue() Then
match = True
End If
Next j
Loop Until (match = False)
oCell.setValue(val)
Next i
End Sub
Posted in OpenOffice Basic | No Comments »
Listing Cell Notes
Wednesday, 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 »