S
St!ff M!ttens
In an Excel worksheet with data entered into all the cells from A1 to
H8, the following code behaves as one might expect:
tell application "Microsoft Excel"
first row index of (used range of sheet 1 of active workbook)
end tell
==>1
tell application "Microsoft Excel"
first row index of (get end (used range of sheet 1 of active workbook)
direction toward the bottom)
end tell
==>8
now, if I select an arbitrary cell or range, the following code returns
different result based on the location of the selection.
if selection is outside the used range:
tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of
theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas
of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>65536
if selection is contained within the used range:
tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of
theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas
of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>8
if selection starts within the used range and extends outside the used
range, the script returns 65536. If the selection is an entire row that
begins in the used range but extends beyond the right hand boundary of
the used range, the script returns 8. If the selection is an entire
column, the script hangs in an endless loop.
Now, can anyone explain this behavior or is this just a bug? Am I using
the 'get end' command incorrectly? And can anyone tell me how I can get
my script to examine the contents of the selection and determine how
many rows it spans?
Thanks.
H8, the following code behaves as one might expect:
tell application "Microsoft Excel"
first row index of (used range of sheet 1 of active workbook)
end tell
==>1
tell application "Microsoft Excel"
first row index of (get end (used range of sheet 1 of active workbook)
direction toward the bottom)
end tell
==>8
now, if I select an arbitrary cell or range, the following code returns
different result based on the location of the selection.
if selection is outside the used range:
tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of
theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas
of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>65536
if selection is contained within the used range:
tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to item 1 of (get areas of (get properties of
theSelection))
set theEndRow to first row index of (get end (get item 1 of (get areas
of (get properties of theSelectedRange))) direction toward the bottom)
end tell
==>8
if selection starts within the used range and extends outside the used
range, the script returns 65536. If the selection is an entire row that
begins in the used range but extends beyond the right hand boundary of
the used range, the script returns 8. If the selection is an entire
column, the script hangs in an endless loop.
Now, can anyone explain this behavior or is this just a bug? Am I using
the 'get end' command incorrectly? And can anyone tell me how I can get
my script to examine the contents of the selection and determine how
many rows it spans?
Thanks.