Re: OOo spredsheet - inserting current date & time?

[Ian Laurenson]

On Wed, 2006-05-31 at 23:38 +1200, Ian Laurenson wrote:
> On Wed, 2006-05-31 at 22:41 +1200, Nick Rout wrote:
> > On Wed, 31 May 2006 19:50:12 +1200
> [snip]
> > Now to see about writing a macro. A key shortcut would be the most
> > efficient!

A more robust version than last time (handles multiple cell selections).

sub subSetDate
oDoc = thisComponent
oSelection = oDoc.getCurrentSelection
nFormat = fnGetNumberFormatId(oDoc,"DD/MM/YY")
if HasUnoInterfaces(oSelection, "com.sun.star.lang.XServiceInfo") then
        if oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
                'More than one range selected
                subSetRanges(oSelection, vValue, nFormat)
        elseif oSelection.supportsService("com.sun.star.table.CellRange") then
                'Only one range but more than one cell
                subSetRange(oSelection, now, nFormat)
        elseif oSelection.supportsService("com.sun.star.sheet.SheetCell") then
                'only one cell selected
                subSetCell(oSelection, now, nFormat)    
        end if
end if
end sub


sub subSetRanges(oRanges, vValue, nFormat)
for i = 0 to oRanges.getCount -1
        subSetRange(oRanges.getByIndex(i), vValue, nFormat)
next
end sub


sub subSetRange(oRange, vValue, nFormat)
for i = 0 to oRange.getColumns.getCount - 1
        for j = 0 to oRange.getRows.getCount - 1
                subSetCell(oRange.getCellByPosition(i,j), vValue, nFormat)
        next
next
end sub


sub subSetCell(oCell, vValue, nFormat)
oCell.value=vValue
oCell.setPropertyValue("NumberFormat", nFormat)
end sub


function fnGetNumberFormatId(oDoc, sNumberFormat)
sCharLocale = oDoc.getPropertyValue("CharLocale")
nFormatId = oDoc.getNumberFormats.queryKey(sNumberFormat, sCharLocale, false)
if nFormatId = -1 then 'Not yet defined
        nFormatId = oDoc.getNumberFormats.addNew(sNumberFormat, sCharLocale)
end if
fnGetNumberFormatId = nFormatId
end function