Can anyone explain this, please!

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.
 
P

Paul Berkowitz

How about if you respond to my earlier replies first, then? I'm not quite
sure if I should be spending the time tracking this stuff down and replying
to you if you're not interested in the replies.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
 
P

Paul Berkowitz

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?

As I mentioned previously, there do seem to be some "issues" with selection.
Also, as I mention previously, there's no need to go through all that
kerfuffle with areas and properties, not that it makes much difference here.

It was good detective work to analyze when and where the bugs with selection
occur. It turns out that not only 'get end' but also 'special cells cell
type last cell' gets not the real end or the real last cell of the specified
range, but the last cell with non-empty content. I'm not certain if that's a
bug or a feature - I'll ask. If it's a feature then the dictionary has to be
more explicit about it.

However 'get address' gets the full area, including blank cells. So use this
script:


tell application "Microsoft Excel"
set theAddress to get address (get selection)
end tell
set AppleScript's text item delimiters to {":"}
set theFirstRow to last character of (text item 1 of theAddress)
set AppleScript's text item delimiters to {"$"}
set theLastRow to text item -1 of theAddress
set AppleScript's text item delimiters to {""}
return "Rows " & theFirstRow & " through " & theLastRow


Notes:
1) You must put all the lower lines containing 'character' outside the Excel
tell block: we want AppleScript's 'character' object, bit Excel's. Or else
use 'text -1 thru -1' instead of 'last character. That would work OK in an
Excel tell block.

2) You can coerce theFirstRow and theLastRow to integer:

set theFirstRow to last character of (text item 1 of theAddress) as integer

and same for theLastRow, if you need to do anything with the numbers.
They're being returned here as text.


3) If you hadn't yet seen my replies to your earlier questions, switch to
using the Microsoft News Server - msnews.microsoft.com . You'll see your own
and others' messages within about 1 minute of posting them. No password is
necessary. (Microsoft News Server comes built in to Entourage, but you could
also set it up in your G2 app, I'm sure.)

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
 
P

Paul Berkowitz

However 'get address' gets the full area, including blank cells. So use this
script:


tell application "Microsoft Excel"
set theAddress to get address (get selection)
end tell
set AppleScript's text item delimiters to {":"}
set theFirstRow to last character of (text item 1 of theAddress)
set AppleScript's text item delimiters to {"$"}
set theLastRow to text item -1 of theAddress
set AppleScript's text item delimiters to {""}
return "Rows " & theFirstRow & " through " & theLastRow

Sorry - that will give the wrong first row if the first row is 10 or higher.
My mistake. This script will get it right in all cases (including completely
blank selected area), and converts the text to numbers:


tell application "Microsoft Excel"
set theAddress to get address (get selection)
end tell
set AppleScript's text item delimiters to {":"}
set theFirstRow to (text item 1 of theAddress)
set AppleScript's text item delimiters to {"$"}
set theFirstRow to text item -1 of theFirstRow as integer
set theLastRow to text item -1 of theAddress as integer
set AppleScript's text item delimiters to {""}
return "Rows " & theFirstRow & " through " & theLastRow



Now it doesn't matter if you leave all of it in the Excel tell block.



--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
 
P

Paul Berkowitz

It turns out that not only 'get end' but also 'special cells cell
type last cell' gets not the real end or the real last cell of the specified
range, but the last cell with non-empty content. I'm not certain if that's a
bug or a feature - I'll ask. If it's a feature then the dictionary has to be
more explicit about it.

It's exactly the same in VBA so I imagine it must be an "inherited feature".

With a selection that extends from inside to outside the used range:


Sub Whatever()
Dim theRow As Long

theRow = Selection.End(xlDown).Row
Debug.Print theRow

End Sub



puts the index of the last row which has non-empty content, rather than the
last blank row, into the Immediate window.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
 
S

St!ff M!ttens

Sorry, Paul. I wasn't seeing any responses to my posts for some reason,
so I gave up and went to the Apple discussions forum. But, thanks for
the advice. I did finally figure out a workaround by using 'value' of
the selected range. here's what I wound up doing:

tell application "Microsoft Excel"
set theSelectedRange to (get selection) --returns a range object which
represents all the selected cells
set theSelectedData to value of theSelectedRange
--returns the actual data from inside all of the cells within the
selected range
if (get class of theSelectedData) is not list then
--only one cell has been selected, 'value' returns a string
else
--multiple cells are selected, 'value' returns a list of lists
set theRowCount to count of theSelectedData
--there you go. The number of items in theSelectedData equals the
number of rows the selection spans so now theRowCount is set to an
integer representing the number of rows
end if
end tell

Your way looks much more efficient. Thanks again for the advice
 
W

Wallace, William

Thanks also for the tip about Entourage' newsgroup reader. I was looking at
the newsgroup through Google's Groups site and was not seeing any of my
posts or any responses to them.
 
P

Paul Berkowitz

Sorry, Paul. I wasn't seeing any responses to my posts for some reason,
so I gave up and went to the Apple discussions forum. But, thanks for
the advice. I did finally figure out a workaround by using 'value' of
the selected range.

When you use the Google groups or any other browser access to the
newsgroups, or your ISP's news server in a news client, it can take several
hours - 12 or more - until you see your own message and then replies to that
message propagated. If you use the Microsoft News Reader in Entourage or any
news client (newsreader program), you'll see both within about a minute of
their being posted if you refresh the list, as Will confirmed. In Entourage
the news group list will refresh automatically by leaving and returning to
the newsgroup, or press command-L while viewing the list.

Getting the 'value' of the selected range does indeed return a list of lists
whose number of items (the internal lists) is indeed the number of rows. In
the case where there might be an enormous number of rows and/or columns my
more direct method will probably be quicker.

I think it's pretty clear from the VBA example I posted that commands such
as 'get end' and 'specials cells' are designed to get the last cell of the
used range (the last non-empty cell) even when the range they are operating
on extends past the used range. The Reference, if not the dictionary, and
the VB Help, should say so. I think you found a real bug in the
contradiction between what 'get selection' and 'get end' return in the case
a no selection; the first returns the active cell while the second returns
the last cell of the worksheet. I'll report all this to MacBU.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top