[Solved] Clear contents in Calc named cell range

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
pansmanse
Posts: 29
Joined: Wed Nov 11, 2009 4:30 pm

[Solved] Clear contents in Calc named cell range

Post 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?
Last edited by TheGurkha on Sun Jan 01, 2012 3:21 pm, edited 2 times in total.
Reason: Tagged Solved, TheGurkha.
Pansmanser
LO3.5.7.2/Ubuntu12.04
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Clear contents in Calc named cell range

Post by JohnSUN-Pensioner »

Try this
CellRange = ThisComponent.NamedRanges.getByName("TickBoxes").getReferredCells()
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Clear contents in Calc named cell range

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

[OT] Clear contents in Calc named cell range

Post 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.
Apache OpenOffice 4.1.9 on Linux
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Clear contents in Calc named cell range

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
pansmanse
Posts: 29
Joined: Wed Nov 11, 2009 4:30 pm

Re: [Solved] Clear contents in Calc named cell range

Post by pansmanse »

Thanks Villeroy and John-SUN-Pensioner. That's useful and very clear. (and solved the issue)
Pansmanser
LO3.5.7.2/Ubuntu12.04
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Clear contents in Calc named cell range

Post 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 ... :o
Happy New Year! :D
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: [OT] Clear contents in Calc named cell range

Post by kingfisher »

Thank you Villeroy and JohnSUN.
Apache OpenOffice 4.1.9 on Linux
Post Reply