Modifying Range Object in List Object (Applescript)

[Possum Stu]

I am trying to use Applescript to update a List Manager list.

On worksheet 1 I have the data collected from other spreadsheets on
columns A-E, rows 1-100. I created a list on worksheet 2 that
corresponds to that range.

Time passes and I must update the data. Now there are 120 records on
rows 1-120, but the list still points to rows 1-100.

The documentation says that the list object has a range object
property, but I'm not able to modify it, or even access it.

For example, when I try:

set range object of list object 1 of worksheet 1 of active workbook to
"a1:e120" or "$a$1:$e$120"

nothing changes.

I know I'm not even reaching the object because when I try:

set test to count rows of range object of list object 1 of worksheet 1
of active workbook
return test

I get 65536 -- total rows for the sheet.

I'm stumped. Any help? TIA.

 

[DerekSn [MS]]

Possum Stu,
Thanks for your post. Once a list object is created, I don't think you
can directly redefine its source range or used range. To expand the
list with AppleScript, VB, or through the user interface, you'll want
to Insert Rows. There's a coupel ways to do this via AppleScript:

-- This will add a new row to the bottom of the list manager object.

make new list row at list object 1 of active sheet

-- This is the general insert command for cells, and if the target
range is a list object row, it will insert a new row into the list.

insert into range range "A101:E101" shift shift down

This posting is provided "AS IS" with no warranties, and confers no
rights.

-Derek

 

[Possum Stu]

Thanks for the response. It's almost worse thinking there is an answer
out there that is eluding you, rather than having confirmation it
cannot be done the way you think.

Even when the list matches the data source one-for-one across
worksheets, it looks like Inserting rows in a list does not
automatically bring over the new data links. If my source has 10 new
rows of data, the list will only have 10 blank rows waiting for links.

The least elegant solution -- but it is a solution -- is to give the
list a cushion of a few hundred empty rows in anticipation of their
being filled by new data.