| View previous topic :: View next topic |
| Author |
Message |
danivtec
Newbie
Joined: 20 Jun 2012
Posts: 3
|
 Posted: Wed Jun 20, 2012 4:27 am Post subject: CALC: Clear Contents Macro |
 |
|
Hi
I am trying to write a macro to clear the contents of a range of cells. I have got a macro to clear a first range of cells but I am unable to add further ranges to it.
My code
| Code: | sub clear
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$11:$B$16"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())
end sub |
I want to add cells H12:16, K16, H18:19, B22:43
Any help would be appreciated so I can add these so they work?
Moderation probe1: moved to MACROS AND API section, where all macro related questions belong to |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Wed Jun 20, 2012 8:32 am Post subject: |
|
|
The macro recorder generates very cryptic code.
Clearing the contents of the ranges you mentioned on Sheet1 could be done using this...
with thiscomponent.sheets(0)
.getcellrangebyname("B11:F16").clearcontents(31)
.getcellrangebyname("H12:H16").clearcontents(31)
.getcellrangebyname("K16").clearcontents(31)
.getcellrangebyname("H18:H19").clearcontents(31)
.getcellrangebyname("B22:B43").clearcontents(31)
end with
The clearcontents method takes a parameter that determines which type of content is cleared.
31 is the sum of 1 (Value), 2 (Date Time), 4 (String), 8 (Annotation - now called Comment) and 16 (Formula).
See http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html for more information on this parameter.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Wed Jun 20, 2012 9:32 am Post subject: |
|
|
Hallo
| Code: |
sub clearRanges
names = "Sheet1.B11:F16;Sheet1.H12:H16;Sheet1.K16;Sheet1.H18:H19;Sheet1.B22:B43"
ranges() = thisComponent.Sheets().getCellRangesByName( names )
for i = 0 to ubound(ranges())
ranges(i).clearContents(511)
next i
end sub
|
Karo |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Jun 20, 2012 10:01 am Post subject: |
|
|
| Code: | sub clearRanges
with com.sun.star.sheet.CellFlags
iFlag = .VALUE + .FORMULA + .STRING + .DATETIME + .ANNOTATION
end with
names = "Sheet1.B11:F16;Sheet1.H12:H16;Sheet1.K16;Sheet1.H18:H19;Sheet1.B22:B43"
ranges() = thisComponent.Sheets().getCellRangesByName( names )
REM ranges is an array of ranges, indeed:
for each rg in ranges
rg.clearContents(iFlag)
next
End Sub
|
I would do this without any macro using either a template or a set of scenarios with one persistent blank scenario ("copy back" = No).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
Last edited by Villeroy on Wed Jun 20, 2012 11:00 am; edited 2 times in total |
|
| Back to top |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Wed Jun 20, 2012 10:30 am Post subject: |
|
|
Hallo
| Quote: | ranges = thisComponent.Sheets().getCellRangesByName( names )
REM ranges is a collection which has the interface to clearContents
ranges.clearContents(iFlag) |
@Villeroy:
So I have done first, but with Error:
"Basic_Laufzeitfehler
Objektvariable nicht belegt"
in the highlighted line.
?....
Karo |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Jun 20, 2012 10:55 am Post subject: |
|
|
I'm wrong. objSheets.getCellRangesByName( names ) gives an array of ranges, indeed.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
danivtec
Newbie
Joined: 20 Jun 2012
Posts: 3
|
| Posted: Mon Jun 25, 2012 12:54 am Post subject: |
|
|
| Thanks for all the suggestions |
|
| Back to top |
|
|
|
