I have been dabbling in scripting for a while now but I am very new to scripting in Excel and although I have something that works, it has a serious flaw letting it down. The script I have gets the value from column A and column B of a selected row in an Excel worksheet and prepares it for importing into a text box in Adobe Indesign. The Excel part of the script basically looks like this:
tell application “Microsoft Excel”
set DataSelection to the selection
set myImportData to {}
set RowCount to the count of rows of DataSelection
repeat with x from 1 to RowCount
tell row x of DataSelection
set myKeyCode to the string value of cell 1
set myDescription to the string value of cell 2
end tell
set myImportData to myImportData & (myKeyCode & return & return & myDescription)
end repeat
end tell
It works a treat if your selection is a series of rows immediately next to one another (eg. all rows 1 thru 12), but the moment you make a multi-selection of rows in excel (eg. rows 1, 3, 4, 5, 12) the script falls over.
I have gone right back to basics and the following script will count the selected rows successfully if it is a series of rows but the moment you make a multi-selection it only returns 1:
tell application “Microsoft Excel”
set mySelection to the selection
return the count of rows of mySelection
end tell
This is driving me nuts, I must be missing something very basic in the way I address and call information from the rows in Excel.
When working with discontinous ranges in Excel, one needs to loop through the Areas collection.
tell application "Microsoft Excel"
repeat with DataSelection in (get areas of the selection)
set myImportData to {}
set RowCount to the count of rows of DataSelection
repeat with x from 1 to RowCount
tell row x of DataSelection
set myKeyCode to the string value of cell 1
set myDescription to the string value of cell 2
end tell
set myImportData to myImportData & (myKeyCode & return & return & myDescription)
end repeat
end repeat
end tell
Hey thanks for the reply, unfortunately the row count still only returns 1. But “(get areas of the selection)” makes perfect sense and has given me something to work with. Now I am just trying to find a way to extract the string values of cell 1 and 2 of each row in the areas of the selection.
Counting the “(get areas of the selection)” in order to loop through is going to be tricky as I have noticed that if there are any continuous ranges selected in a discontinuous range then it will only count them in the selection as 1 item (eg. 1, 3, 5-8, 10, 12 will count as only 5 items, not 8).
I’m not certain of the format of the result you want (a list of strings, each of which has two linefeeds in the middle?), but this seemed to work.
tell application "Microsoft Excel"
set myRange to the selection
set dataToImport to {}
repeat with oneArea in (get areas of myRange)
repeat with rowNum from 1 to count of rows in oneArea
tell entire row of row rowNum of oneArea
set returnString to value of cell 1 of column 1
set returnString to returnString & linefeed & linefeed & value of cell 1 of column 2
copy returnString to end of dataToImport
end tell
end repeat
end repeat
dataToImport
end tell
This look like something that would work for my need but I am having some problem running the about code.
It giving me an error: linefeed is not defined
Tested the code on:
OS X 10.4.11 with Excel 2004
OS X 10.6.2 with Excel 2008
I found I couldn’t get linefeed to work either, I used return instead. But if your list doesn’t need to be separated by a return/linefeed you can you pretty much use whatever you liked as long as it could be interpreted by Applescript as a text string.
Ohh man that is perfect, thanks very much!