OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

CALC: Clear Contents Macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
danivtec
Newbie
Newbie


Joined: 20 Jun 2012
Posts: 3

PostPosted: Wed Jun 20, 2012 4:27 am    Post subject: CALC: Clear Contents Macro Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Jun 20, 2012 8:32 am    Post subject: Reply with quote

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
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Wed Jun 20, 2012 9:32 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 20, 2012 10:01 am    Post subject: Reply with quote

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 https://forum.openoffice.org


Last edited by Villeroy on Wed Jun 20, 2012 11:00 am; edited 2 times in total
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Wed Jun 20, 2012 10:30 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 20, 2012 10:55 am    Post subject: Reply with quote

I'm wrong. objSheets.getCellRangesByName( names ) gives an array of ranges, indeed.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
danivtec
Newbie
Newbie


Joined: 20 Jun 2012
Posts: 3

PostPosted: Mon Jun 25, 2012 12:54 am    Post subject: Reply with quote

Thanks for all the suggestions
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group