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

Recursively relocate a named range

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


Joined: 31 Jan 2011
Posts: 3

PostPosted: Mon Jan 31, 2011 10:05 pm    Post subject: Recursively relocate a named range Reply with quote

Hi,

Newbie here, converting an exotic application in Excel/VBA to Calc/StarBasic. The application is an automatic Euler simulation that is periodically sampled in terms of variables residing in a range called “Buffer”. When the sampling occurs, the contents of “Buffer” are saved in another range called “NLINE”. After the sampling, “NLINE” must be deleted from the NamedRanges and recreated in the next row down. After the simulation has ended, we are left with a stack of the variables that were in “Buffer” at each of the sampling intervals; and each column in the stack is a time series that is used for plotting.

The spool macro below is functional except that I need a technique for extracting the information required to determine the address of the new “NLINE” from the address of the current “NLINE” before I delete it. As you can see, I need to compute the row number nRow and row name sRow$ of the new “NLINE”. I know it should be easy; but I can’t seem to find the relevant documentation.


'
' Spool Macro
'
Sub Spool()
Dim oSheet, oAllData as Object
Dim oRangeB, oRangeN as Object
Dim oRanges as Object
Dim sName$, sRow$
Dim nRow as Integer
Dim oCellAddress As new com.sun.star.table.CellAddress
'
'Rem: record contents of Buffer in NLINE
'
oSheet = ThisComponent.Sheets.getByName("Series")
oRangeB = oSheet.getCellRangeByName("Buffer")
oAllData = oRangeB.getDataArray()
oRangeN = oSheet.getCellRangeByName("NLINE")
oRangeN.setDataArray(oAllData)
'
'Rem: develop parameters needed to re-create NLINE
' one row below its current location
'
sName$ = "NLINE"
oRange = ThisComponent.NamedRanges.getByName(sName$)


nRow = 8

sRow$ = "9"
s$ = "$Series.$C$"+sRow$+":$N$"+sRow$
'
'Rem: delete and re-create NLINE
'
oRanges = ThisComponent.NamedRanges
oRanges.removeByName(sName$)
'
oCellAddress.Sheet = 3 'The "Series" sheet.
oCellAddress.Column = 2 'Column C.
oCellAddress.Row = nRow 'Row . . .
oRanges.addNewByName(sName$, s$, oCellAddress, 0)
'
End Sub
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 01, 2011 5:31 am    Post subject: Reply with quote

Please ask this type of question in the API forum and add some code tags for better readability.
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/NamedRanges.html
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XNamedRanges.html

Of course you need some object inspector as well. The best one is http://extensions.services.openoffice.org/en/project/MRI
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ftack
Moderator
Moderator


Joined: 27 Jan 2003
Posts: 3102
Location: Belgium

PostPosted: Tue Feb 01, 2011 5:48 am    Post subject: Reply with quote

Moved.
_________________
--
GNU/Linux (Ubuntu)
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