Ennio-Sr wrote:
Hi all,
I'm wondering whether it is possible to delete/create named ranges
by a macro.
My attempts were unsuccessfull in either cases: when I record all the
relative steps to perform that action no warning appears; however, the
final recorded macro doesn't do what expected.
Can you point me to specific documentation?
Regards,
Ennio.
Is this enough to get you started?
In a macro, a named range is accessed, created, and deleted using the
NamedRanges property of a Calc document. Use the methods hasByName(name)
and getByName(name) to verify and retrieve a named range. The method
getElementNames() returns an array containing the names of all named
ranges. The NamedRanges object supports the method addNewByname, which
accepts four arguments; the name, content, position, and type. The macro
in Listing 1 creates a named range, if it does not exist, that
references a range of cells.
/*Listing 1.* AddNamedRange is in this chapter’s source code files as SC08./
*Sub AddNamedRange()*
*Dim oRange ' The created range.*
*Dim oRanges ' All named ranges.*
*Dim sName$ ' Name of the named range to create.*
*sName$ = "MyNRange"*
*oRanges = ThisComponent.NamedRanges*
*If NOT oRanges.hasByName(sName$) Then*
*REM I could obtain the cell address by obtaining the cell*
*REM and then extracting the address from the cell.*
*Dim oCellAddress As new com.sun.star.table.CellAddress*
*oCellAddress.Sheet = 0 'The first sheet.*
*oCellAddress.Column = 1 'Column B.*
*oCellAddress.Row = 2 'Row 3.*
*REM The first argument is the range name.*
*REM The second argument is formula or expression to use. The second*
*REM argument is usually a string that defines a range.*
*REM the third argument specifies the base address for relative *
*REM cell references.*
*REM The fourth argument is a set of flags that define how the range*
*REM is used. Most ranges use the value 0, however.*
*REM com.sun.star.sheet.NamedRangeFlag.FILTER_CRITERIA = 1*
*REM com.sun.star.sheet.NamedRangeFlag.PRINT_AREA = 2*
*REM com.sun.star.sheet.NamedRangeFlag.COLUMN_HEADER = 4*
*REM com.sun.star.sheet.NamedRangeFlag.ROW_HEADER = 8*
*oRanges.addNewByName(sName$, "$Sheet1.$B$3:$D$6", oCellAddress, 0)*
*End If*
*oRange = ThisComponent.NamedRanges.getByName(sName$)*
*REM Print the string contained in cell $Sheet1.$B$3*
*Print oRange.getReferredCells().getCellByPosition(0,0).getString()*
*End Sub*
The method addNewByname() accepts four arguments; the name, content,
position, and type. The fourth argument to the method addNewByName() is
a combination of flags that specify how the named range will be used
(see Table 2)—the most common value is 0.
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.sxw
My Macro Book: http://www.hentzenwerke.com/catalog/oome.htm
Free Info: http://www.pitonyak.org/oo.php
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
