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"
select (ranges (row 3 of used range of the active sheet) thru (last row of used range of the active sheet))
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.