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

Calc: Using createEnumeratiion with getCellRangeByName

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


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Fri Nov 21, 2003 4:30 am    Post subject: Calc: Using createEnumeratiion with getCellRangeByName Reply with quote

In Calc I am trying to use an Enumeration to scan through a matrix of cells to inspect their values, similar to VBA's 'For each Cell in MyRange' construct. However, when I try to create the enumeration, I get an error along the lines of 'Run-time error: Method or Property not found'. I've been trying this:
Code:
Dim oDocument As Object,
Dim oSheet As Object
Dim oRange As Object
Dim oSearchCells As Object
Dim oCell As Object,

oDocument = ThisComponent
oSheet=oDocument.Sheets.getByName("Sheet1")

oRange = oSheet.getCellRangeByName("A5:D9")
oSearchCells = oRange.createEnumeration
While oSearchCells.hasMoreElements
   oCell = oSearchCells.nextElement
   'Get the cell's info here, do something with it.
Wend
The error message pops up in the line containing oRange.createEnumeration. Is it possible to create an enumeration on a CellRange?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Fri Nov 21, 2003 8:22 am    Post subject: Reply with quote

Are you reading something which leads you to believe that you actually can do a createEnumeration on a cell range?

I'm looking at the getCellRangeByName() here...

http://api.openoffice.org/docs/common/ref/com/sun/star/table/XCellRange.html#getCellRangeByName

from the XCellRange interface, and it returns another XCellRange interface, and says to "see also: CellRange", which is here...

http://api.openoffice.org/docs/common/ref/com/sun/star/table/CellRange.html

CellRange includes CellProperties, and implements XCellRange. CellProperties does not have any interface which leads to a createEnumeration method.

So my reading of the API, the online one, linked above, would lead me to believe that you can not do this.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Sat Nov 22, 2003 12:58 am    Post subject: Reply with quote

No, I hadn't read that I could, I was wondering whether I could. I have read lots of pages of the API documentation, but I am usually none the wiser for the experience. I find the method of presentation of information to be impenetrable. I just thought it should be possible, since the function of searching through a range of cells for information is such an elementary part of coding in spreadsheet macros.

Anyway, thanks for clearing it up. I will now have to resort to a much more cumbersome method instead of something reasonably elegant.
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Nov 22, 2003 6:59 am    Post subject: Reply with quote

geoff_f wrote:
I have read lots of pages of the API documentation, but I am usually none the wiser for the experience. I find the method of presentation of information to be impenetrable.


You and me both. Smile
I can tell you that if you are persistent enough, it all becomes clear.
Keep in mind a few simple rules. You need to dive deeply. Think of the documentation as organized in a tree starting at some service you are interested in. That service leads to services. Any service, (either the one you started with, or ones included or inherited) can have interfaces. Then interfaces can include or inherit interfaces.
1. Properties are ALWAYS found only in Services. (NEVER interfaces.)
2. Methods are ALWAYS found only in Interfaces. (NEVER services.)
3. Any Service can inherit from zero or one parent service. (All of the service rules here then apply recursively to that service.)
4. Any Service can include from zero or more services. (Again, everything about those services, even if indirectly inherited or included, applies to the service you started from.)
5. Any service can iimplement interfaces. (Then all rules about interfaces apply to the interface, and all methods uncovered from the interfaces are in the service you started from.)
6. Any interface can inherit from zero or one parent interface.
7. Any interface can include zero or more interfaces.
When you are thinking of a service, you need to explore "down the tree of links" to the services inherited and included. Then from every service you explored, explore all interfaces of those services. And then all interfaces of those interfaces. Hope that all made sense. Once you get it, you will have an "Ah ha!" experience.

geoff_f wrote:
I just thought it should be possible, since the function of searching through a range of cells for information is such an elementary part of coding in spreadsheet macros.

Anyway, thanks for clearing it up. I will now have to resort to a much more cumbersome method instead of something reasonably elegant.


If by much more cumbersome, you mean iteration, then I'm not sure if that is any worse than the enumeration. You can definitely iterate over a particular cell range. From the spreadsheet, you get a cell range of the entire sheet. You can then ask for a sub-range of cells, say from C6:J9. Now the cell range that is returned is a view of only those cells. With two nested For/Next loops, you can look at every cell in a cell range, which could be either the original full sheet cell range, or a sub range you have asked for.

There may be a better way, but I'm not a Calc expert.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Sun Nov 23, 2003 12:09 am    Post subject: Reply with quote

Danny, thanks for taking the time to give your very detailed reply. I'll stick at it and give it a go (reading the docs), maybe I'll eventually have that 'Aha' moment.

On cumbersome methods, yes iteration was (and is) my fall-back; I have managed to achieve what I want using the nested For...Next loops.

Once again, thanks for your help; it's much appreciated.
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Nov 26, 2003 6:14 am    Post subject: Reply with quote

Hi geoff_f!

Try this, works for me:
Code:
sub Testww
Dim oDocument As Object, oSheet As Object, oRange As Object, oSearchCells As Object, oCell As Object

oDesktop = createUnoService("com.sun.star.frame.Desktop")
oDocument = ThisComponent
oSheet=oDocument.Sheets.getByName("Sheet1")
oRange = oSheet.getCellRangeByPosition(0,4,3,8) 'instead of ...ByName - it could be used if you named your range before

oRanges = oDocument.createInstance("com.sun.star.sheet.SheetCellRanges")
oRanges.insertByName("", oRange)   ' - I presume first parameter could be name of range ??, second is an object
oSearchCells = oRanges.Cells.createEnumeration

While oSearchCells.hasMoreElements
   oCell = oSearchCells.nextElement
   MsgBox oCell.String
   'Get the cell's info here, do something with it.
Wend
End Sub


Hope it helps...
Uros
Back to top
View user's profile Send private message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Fri Nov 28, 2003 2:00 pm    Post subject: Reply with quote

Thanks for your help uros, I'll give it a try.
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