Hi all!
as per the subject. Forgive me in case I made sothing wrong in the
sending procedure: I was using Paolo Mantovani's SnippetCreator and got
stucked at the window where you're supposed to write the code :-)
So, I'm sending it with my preferred console Mail User Agent, Mutt.
Hope this will have some utility.
Regards,
Ennio.
<!--
$RCSfile: index.html,v $
last change: $Revision: 1.8 $ $Author: jsc $ $Date: 2004/06/24 13:30:40 $
(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&, >named range erase/create within a
macro&, >macro to erase/create last row cell named range&</keyword>
</keywords>
<authors>
<author id="ennio_sr" initial="true" email="[EMAIL PROTECTED]"></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
Kelevic</author>
</authors>
<question heading="Macro to determine last used row and set a named range for
its last cell">
How do I find the last row used
<p>GreChi posed that question on [email protected] and received workarounds
instead of a punctual answer.</p>
<p>I had a similar problem and looking at Andrew Pitonyak's Useful Macro
Information for OOo, or asking</p>
<p>a few more questions on [email protected] set up in the end a complete
macro to that effect.</p>
<p>My macro could well be assigned to the 'open document' event, so that
it's run each time the file is opened.
</p>
</question>
<answer>
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("com.sun.star.frame.DispatchHelper")
' ----------------------------------------------------------------------
dim args1(0) as new 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 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 start 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$
' 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 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 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 ################################################
'This is mentioned in one of the preious macro dialog boxes
sub create_new_rec
' we simply call the _0_create_last_bal
_0_create_last_bal
End sub
rem #################################################
</answer>
<changelog>
</changelog>
</snippet>
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (�|�)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
