AppleScript in Excel - selecting range

Question

AppleScript in Excel - selecting range

I am having some trouble with AppleScripting in Microsoft Excel. I have Excel 2004 and 2008. The behaviour is the same in both.

The background is this - I am working on a script to open text files containing two columns of data and a varying number (about 50 to 170) of rows of data, select the data in the third row down to the last row, and then make a chart from that data. The first two rows contain header information which I would like to leave in place. Opening the files is working OK with no problems. When it comes to selecting the rows I wish to use, I am getting an incorrect result from what appears to be the correct command. The following part of the script (which assumes an open worksheet with data) is where I am getting frustrated:

tell application "Microsoft Excel"

activate

select (ranges (row 3 of used range of the active sheet) thru (last row of used range of the active sheet))

end tell

The ranges after the select command is what AppleScript puts in if I use range. I ended up with that line after much experimenting with terms such as from, to, :, including, containing, etc.

What actually happens for me is that Excel selects the used cells of row 3 AND the last row with data rather than row 3 THROUGH the last row with data, ie only the two rows specified and not the intermediate rows. The command (both in AppleScript and in plain English) reads as if the two rows specified are the first and the last rows of a contiguous selection, not the only two rows to be selected.

I have tried changing the fourth line to:

select (ranges ("A3") thru (last cell of used range of active sheet))

but, similarly to the "row" version, it selects only the two cells specified and not the intermediate cells.

Am I missing something obvious (or not so obvious), or is this one of the odd quirks in Microsoft Office's AppleScript implementation?

Because the last cell containing data will vary from file to file (anywhere from B50 to B170), I can't simply use something like:

select range "A3:B170"

as that will potentially select a whole lot of empty cells as well as the ones containing data.

Any suggestions, fixes, workarounds, etc. will be keenly welcomed.

PowerMac, Mac OS X (10.4.11), G5, DP 2.5 GHz

Posted on Aug 19, 2011 12:02 AM

Reply

Answer 1

Aug 19, 2011 6:34 AM in response to Community User

During some experimenting with various ways of expressing the range, when looking in the event log (at the bottom of the Script Editor window), I noticed that the references to the cells being selected were in the format of $A$3,$B$50, rather than $A$3:$B$50. It looks like the comma indicates non-contiguous selections while the semicolon indicates a continuous block of selection from the first cell specified to the second. Pasting each into my script confirmed this. But I haven't worked out how to effect the necessary behaviour.

What I have figured out is a workaround which seems to do the job I want without any unwanted side-effects. In the original files being imported the first two rows contain header information (descriptions, dates, etc.) which needs to be retained, with the actual data (which the chart would then use) starting on the third row. Because the top left cell of the data will always start in the same location in all the text files I have decided to format the text files so that there is an empty row after the first two rows (first data cell is now A4 instead of A3). Then I can use the following lines to select the data cells without even having to work out how many there are:

tell application "Microsoft Excel"

activate

select range "A4"

select current region of active cell

end tell

To quote from the Excel 2004 AppleScript Reference PDF: "The current region property returns a range of cells bounded by blank rows and columns". In other words, by selecting the first data cell and then the current region, the selection is expanded until it encounters an empty row or column. By separating the data from the header rows, there is a blank row above the data, so only the data is selected.

Mission accomplished, in terms of achieving a workable solution. However, I am still very curious about the reason for the happenings as described in my first post. If anybody knows why, I am quite keen to be educated.

Reply

Answer 2

twtwtw

Level 5

4,936 points

Aug 19, 2011 8:07 AM in response to Community User

the microsoft office scripting dictionaries are just horribly designed. They violate pretty much every convention for scripting dictionaries that Apple specifies. Mocrosoft... 😟

you have to create ranges microsoft style - e.g. range "A3:B120" - so you have to wangle things to get a text string. in your case something like so:

tell application "Microsoft Excel"

tell active sheet

tell used range

set rc to count of rows

end tell

select range ("A3:B" & rc)

end tell

Reply

Answer 3

Aug 19, 2011 3:35 PM in response to twtwtw

That's exactly what I was trying to do, and your script example works. Tell used range hadn't occurred to me.

I have read somewhere that Microsoft Office's AppleScript dictionaries were designed to resemble their previous scripting/macro language, namely Visual Basic for Applications (VBA), as much as possible. For someone who had been using VBA, the transition to using AppleScript was supposed to have been made easier because the grammar and syntax were structured very similarly. For everyone else, it has made scripting Microsoft Office applications unnecessarily confusing and obfuscated (not to mention the various bugs). Apparently, support for VBA has returned in Microsoft Office 2011 for Mac.

Thank you for enlightening me. I was pretty sure that there was a way of doing it, but could not figure out the intricacies.

Reply