[Solved] Clear contents in Calc named cell range

Creating a macro - Writing a Script - Using the API

[Solved] Clear contents in Calc named cell range

Postby pansmanse » Fri Dec 30, 2011 2:33 pm

I'm trying to clear a named cell range in a Calc macro.

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
pansmanse
 
Posts: 27
Joined: Wed Nov 11, 2009 4:30 pm

Re: Clear contents in Calc named cell range

Postby JohnSUN-Pensioner » Fri Dec 30, 2011 2:51 pm

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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Clear contents in Calc named cell range

Postby Villeroy » Fri Dec 30, 2011 3:03 pm

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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[OT] Clear contents in Calc named cell range

Postby kingfisher » Sat Dec 31, 2011 3:46 am

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.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Clear contents in Calc named cell range

Postby Villeroy » Sat Dec 31, 2011 11:41 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby pansmanse » Sat Dec 31, 2011 11:59 am

Thanks Villeroy and John-SUN-Pensioner. That's useful and very clear. (and solved the issue)
Pansmanser
LO3.5.7.2/Ubuntu12.04
pansmanse
 
Posts: 27
Joined: Wed Nov 11, 2009 4:30 pm

Re: Clear contents in Calc named cell range

Postby JohnSUN-Pensioner » Sat Dec 31, 2011 12:04 pm

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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby kingfisher » Sun Jan 01, 2012 1:43 am

Thank you Villeroy and JohnSUN.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests