dominicedmonds
General User
Joined: 19 Dec 2006
Posts: 7
|
 Posted: Thu Sep 25, 2008 2:38 pm Post subject: A more elegant way to lookup data from a table needed? |
 |
|
I have written a macro in OOo Basic that concatenates strings from a column of data in a table in one Calc worksheet (email addresses in detailsArray) and places the concatenated result into another column of data in another table (rolegroupArray) on another Calc worksheet.
I am uncertain as to the efficiency and syntactical correctness and would appreciate some guidance on the way I am using, for example, DataArray, getbyname, getcellbyposition. I think that I want something like VLOOKUP() and INDEX() to help me here. Am I getting to the data in a sensible fashion or are there easier and much more concise ways to achieve the same result? The following code works but it feels a little clunky and inefficient ... what do you think?
| Code: | REM ***** BASIC *****
option base 0
sub init_emails
' routine to initialise the email address of each rolegroup
Dim rolegroupArray As Object, rolegroupitem As Object
Dim detailsArray as Object, detailitem As Object
Dim rolegroupCells As Object
Dim i as Integer, j As Integer
Dim detailrolegroup as String, detailemails as String, rolegroupname as String
rolegroupArray = ThisComponent.NamedRanges.getbyname("RoleGroup").ReferredCells
detailsArray = ThisComponent.NamedRanges.getbyname("Details").ReferredCells
j = 0 ' counter index into rolegroup array
' clear out the old email addresses
do
j = j + 1 ' NB: the first row of the array contains column names
rolegroupArray.getCellByPosition(2, j).String = ""
loop until ( j = rolegroupArray.Rows.Count - 1 )
i = 0 ' counter index into details array
do
i = i + 1 ' NB: the first row of the array contains column names
detailitem = detailsArray.DataArray(i)
detailrolegroup = detailitem(4) ' rolegroup for detailitem
detailemails = detailitem(32) ' combined emails for detailitem
if detailemails <> "" then
j = 0 ' counter index into rolegroup array
do
j = j + 1 ' NB: the first row of the array contains column names
rolegroupitem = rolegroupArray.DataArray(j)
rolegroupname = rolegroupitem(1) ' rolegroup for rolegroupitem
if rolegroupname = detailrolegroup then
rolegroupCells = rolegroupArray.getCellByPosition(2, j)
sep = ""
s = rolegroupCells.String
if s <> "" then ' separate each email address with a comma
sep = ", "
end if
if Instr(s, detailemails) = 0 then ' check that the email is not duplicated
rolegroupCells.String = s & sep & detailemails
end if
exit do
end if
loop until ( j = rolegroupArray.Rows.Count - 1 ) ' until we have examined eacg record in the rolegroup array
' shouldn't ever get here: it means that there is an undefined role group
end if
loop until ( i = detailsArray.Rows.Count - 1) ' until we have processed all records in the details array
end sub
|
Many thanks for your patience and/or interest.
_________________
Forwards! In every direction! |
|
