Hi all!
I'm sending again my first snippet as the first one was ill-formatted.
Regards,
Ennio.
<?xml version="1.0"?>
<!--
$RCSfile: $
last change: $Revision: $ $Author: $ $Date: $
(c)2003 by the copyright holders listed with the author-tags.
If no explicit copyright holder is mentioned with a certain author,
the author him-/herself is the copyright holder. All rights reserved.
Public Documentation License Notice:
The contents of this Documentation are subject to the
Public Documentation License Version 1.0 (the "License");
you may only use this Documentation if you comply with
the terms of this License. A copy of the License is
available at http://www.openoffice.org/licenses/PDL.html
The Original Documentation can be found in the CVS archives
of openoffice.org at the place specified by RCSfile: in this header.
The Initial Writer(s) of the Original Documentation are listed
with the author-tags below.
The Contributor(s) are listed with the author-tags below
without the marker for being an initial author.
All Rights Reserved.
-->
<snippet language="OOBasic" application="Calc">
<keywords>
<keyword>macro</keyword>
<keyword>named range</keyword>
<keyword>delete/create named range</keyword>
<keyword>last used row</keyword>
<keyword>cell containing last balance</keyword>
<keyword>erase named range</keyword>
<keyword>create named range</keyword>
</keywords>
<authors>
<author id="ennio_sr" initial="true" email="[EMAIL PROTECTED]">Ennio-Sr</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Andrew Douglas Pitonyak</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Marc Santhoff</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Sasa Kelesevic</author>
</authors>
<question heading="erase-create_range_name">Reassign named range to a cell in last used row
<p>How to determine last row used and set a named range for a cell in that row</p>
<p>GregChi <[EMAIL PROTECTED]>; asked a similar question on [email protected] </p>
<p>and received workarounds more than a direct answer. I had a similar problem and after</p>
<p>reading Andrew Pitonyak's "Useful Macro Information for OOo" and asking a few questions</p>
<p>on [email protected], wrote a macro which does the job. It considers col. F as the one </p>
<p>containing a formula to determine current account balance (i.e. previous balance , plus credit,</p>
<p>minus debit). Col. headings (A to F) are: Date, Value_date, Description, Debit, Credit, Balance).</p>
<p></p>
<p>Once you have copy pasted the code in your Calc file, you can well assign the macro</p>
<p>(_0_create_last_bal) to the Event/Opening of document so that it is run when the file</p>
<p> is opened. A second macro (create_last_bal) mentioned in the dialog box, does the same</p>
<p>as it just calls the previous one.</p>
<listing>sub _0_create_last_bal
' ----------------------
' On error exit
On Error goto Label
' define variables
dim document as object
dim dispatcher as object
' ----------------------------------------------------------------------
' get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("[EMAIL PROTECTED] com.sun.star.frame.DispatchHelper}")
' ----------------------------------------------------------------------
dim args1(0) as new [EMAIL PROTECTED] com.sun.star.beans.PropertyValue}
args1(0).Name = "ToPoint"
args1(0).Value = "$F$6"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
' ----------------------------------------------------------------------
dim args2(1) as new [EMAIL PROTECTED] com.sun.star.beans.PropertyValue}
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())
' ----------------------------------------------------------------------
Dim oDocument As Object, oSheet As Object, oCell As Object
oDocument=ThisComponent
ocell=ThisComponent.CurrentSelection
vlc = oCell.getValue() ' get value of selected cell (last cell)
if vlc = 0 then
lcz = "y" ' last cell is zero true
End Sub
rem ########### This could be a separate indipendent macro ###########
Dim oRange ' The created range
Dim oRanges ' All named ranges
Dim sName$ ' Name of the named range to create
Dim oActiveCell ' The current active cell
oActiveCell = ThisComponent.CurrentSelection
' we determine the row number of the selected cell:
' numbering starts from 0, so we need add 1:
if lcz="y" then
Print "I' m ready to accept new records: for each new one copy formula " +_
"in last balance cell (or run macro "create_new_rec")."
exit sub
else
r$ = oActiveCell.celladdress.row+1
end if
xcl$ = "Sheet1.$F$"+r$ ' ref to col F may be changed
' we set our named range name:
sName$ = "last_bal"
oRanges = ThisComponent.NamedRanges
' Here we verify if there is already a range with that name and,
' in the affirmative, erase it so that the new one can be created:
If oRanges.hasByName(sName$) Then
oRanges.getByName(sName$)
oRanges.removeByName(sName$)
End If
Dim oCellAddress As new [EMAIL PROTECTED] com.sun.star.table.CellAddress}
oCellAddress.Sheet = 0 ' The first sheet
oRanges.addNewByName(sName$,xcl$,oCellAddress,0)
' End Sub of possible indipendent macro
rem ###################################
' We arrange for copying the formula from last used row to the next one
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args5(1) as new [EMAIL PROTECTED] com.sun.star.beans.PropertyValue}
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
Print "I' m ready to accept new records: for each new one copy formula" +_
"in last balance cell (or run macro 'create_new_rec')."
Label:
''''' print "errore"
Exit Sub
end sub
rem ###################################################################
Sub create_new_rec
' we call the previous macro:
_0_create_last_bal
End Sub </listing>
</question>
<answer>
</answer>
<versions>
<version number="1.1.4" status="tested"/>
<version number="1.9.82 beta" status="tested"/>
</versions>
<operating-systems>
<operating-system name="Linux"/>
</operating-systems>
<changelog>
</changelog>
</snippet>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
