Page 1 of 1
[Solved] Clear contents in Calc named cell range
Posted: Fri Dec 30, 2011 2:33 pm
by pansmanse
I'm trying to clear a named cell range in a Calc macro.
Code: Select all
CellRange = Sheet1.getCellRangeByName("F15:F26") 'The Tick Boxes
Flags = 23
CellRange.clearContents(Flags)
works correctly. (In the actual code, Flags is defined using the preset com.sun.star values.) But when I define a named cell range
Code: Select all
CellRange = ThisComponent.NamedRanges.getByName("TickBoxes")
Flags = 23
CellRange.clearContents(Flags)
returns an error 'method not found: clearContents'. The cell range appears to be understood by Navigator.
What am I doing wrong here?
Re: Clear contents in Calc named cell range
Posted: Fri Dec 30, 2011 2:51 pm
by JohnSUN-Pensioner
Try this
CellRange = ThisComponent.NamedRanges.getByName("TickBoxes")
.getReferredCells()
Re: Clear contents in Calc named cell range
Posted: Fri Dec 30, 2011 3:03 pm
by Villeroy
NamedRange should be called NamedExpression. It is not a range. It is the item you see in the named range dialog.
It has a name, a formula expression as content, a base address for relative references (in the GUI that is the active cell) and it has a set of flags (below the More button in the GUI).
http://www.openoffice.org/api/docs/comm ... Range.html
If and only if the name refers to a absolutely absolute address,
method getReferredCells returns a
com.sun.star.table.XCellRange
The following Basi code has been recorded by the MRI tool:
Code: Select all
Sub Snippet(Optional oInitialTarget As Object)
Dim oNamedRanges As Object
Dim oObj_1 As Object
Dim sContent As String
Dim oReferredCells As Object
oNamedRanges = oInitialTarget.NamedRanges
oObj_1 = oNamedRanges.getByName("MyRange")
sContent = oObj_1.getContent()
oReferredCells = oObj_1.getReferredCells()
oReferredCells.clearContents(23)
End Sub
oInitialTarget is the same as ThisComponent
String sContent is "$Sheet1.$B$3:$E$16" which is an absolutely absolute range address with 5 $-signs so it is possible to get the referred cells.
[OT] Clear contents in Calc named cell range
Posted: Sat Dec 31, 2011 3:46 am
by kingfisher
Off topic, an aside.
Villeroy wrote:The following Basi code has been recorded by the MRI tool.
I've searched for an answer to this question, perhaps someone would indulge me and tell me how to record using MRI.
Re: Clear contents in Calc named cell range
Posted: Sat Dec 31, 2011 11:41 am
by Villeroy
OK, you can not record any actions in the office GUI, but your actions in the MRI tool gets recorded. When you move the mouse pointer near the bottom of the MRI window you may notice that it turns into a vertical double arrow. Drag the bottom border up and you'll see a sub-window with the recorded snippet. In the menu you can switch between Basic, Python, Java and others.
Re: [Solved] Clear contents in Calc named cell range
Posted: Sat Dec 31, 2011 11:59 am
by pansmanse
Thanks Villeroy and John-SUN-Pensioner. That's useful and very clear. (and solved the issue)
Re: Clear contents in Calc named cell range
Posted: Sat Dec 31, 2011 12:04 pm
by JohnSUN-Pensioner
Alternatively, you can open sub-window by double-clicking on this line. You can open this subwindow by double right-click on the bottom right gray square.
New Year's surprise ...
Happy New Year!
Re: [OT] Clear contents in Calc named cell range
Posted: Sun Jan 01, 2012 1:43 am
by kingfisher
Thank you Villeroy and JohnSUN.