Clear cell contents Macro help

[Trevor_K]

Hi, am converting a sheet to Calc from Excel and require to clear range of cells prior to filling with new data. (some cells must contain no data).
Have cut and pasted from various sites but each time I get:-
"Basic runtime Error" An Eception occurred $(ARG1).

EXAMPLE1 from Andrew Pitonyak

Code: Select all

Sub ClearDefinedRange

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim Flags As Long

Doc = StarDesktop.CurrentComponent
Sheet = Doc.Sheets(0)
CellRange = Sheet.getCellRangeByName("B71:E83")

Flags = com.sun.star.sheet.CellFlags.STRING + _  <<< ERROR POINTS TO THIS LINE
      com.sun.star.sheet.CellFlags.HARDATTR

CellRange.clearContents(Flags)

End Sub

EXAMPLE 2 from Star Office site

Code: Select all

Sub ClearCellContents

Dim nFlags As Long
Dim oRange As New com.sun.star.table.CellRangeAddress

oRange = oSheet.getCellRangeByName( "B71:E83" )
 
nFlags = com.sun.star.sheet.CellFlags.STRING + _ <<< ERROR POINTS TO THIS LINE
        com.sun.star.sheet.CellFlags.STYLES
 
oRange.clearContents( nFlags ) 

End Sub

Am I missing an OOo lib or something similar. Any help appreciated.

[kingfisher]

You should install the Xray tool and the SDK. The former is on the developers' page of http://www.ooomacros.org The latter is probably available from the mirrors.

First, the information about clearContents. That contains a link to the flags page.

I find it simpler to use numerical values. To delete strings only :

Code: Select all

CellRange.clearContents( 4 )

To clear simple values (i.e. not dates or times) :

Code: Select all

CellRange.clearContents( 1 )

To clear both strings and simple values :

Code: Select all

CellRange.clearContents( 5 )

[Trevor_K]

Your info much appreciated, seems I need to much more research/reading before diving into OOo macro's.

Thanks again.

[martius]

If you want to clear eveything this is the best way:

Code: Select all

Sub clearEverything
Thiscomponent.CurrentSelection.ClearContents(1 OR 2 OR 4 OR 8 OR 16 OR 32 OR 64 OR 128 OR 256 OR 512)
End Sub

'constants group CellFlags
'com.sun.star.sheet.CellFlags.
'These constants select different types of cell contents.
'The values can be combined. They are used to insert, copy, or delete contents.

'com.sun.star.sheet.CellFlags.
'VALUE = 1 ->selects constant numeric values that are not formatted as dates or times.
'DATETIME = 2 ->selects constant numeric values that have a date or time number format.
'STRING = 4 ->selects constant strings.
'ANNOTATION = 8 ->selects cell annotations.
'FORMULA = 16 ->selects formulas.
'HARDATTR = 32 ->selects all explicit formatting, but not the formatting which is applied implicitly through style sheets.
'STYLES = 64 ->selects cell styles.
'OBJECTS = 128 ->selects drawing objects.
'EDITATTR = 256 ->selects formatting within parts of the cell contents.
'FORMATTED = 512 ->selects cells with formatting within the cells or cells with more than one paragraph within the cells.

[paulschud]

or:

Code: Select all

Sub clearEverything
For i = 0 To 9
  Thiscomponent.CurrentSelection.ClearContents(2 ^ i)
Next
End Sub