如何使用Applescript在Excel中循环选择单元格

Question

在VBA中，我可以执行类似的操作，在Excel中循环选择单元格：

for each c in Selection
' do things
next

我试图在AppleScript中做同样的事情，但我似乎没有取得任何进展。我确实得到了当前的单元格，但即使我做

set c to count of selection

结果是c被设置为0。

Excel应用程序说明书似乎没有帮助，谷歌搜索也没有帮助。

感谢

Answer 1

事实证明，您必须使用"count large"来获取选择中的单元格数。一旦你到达那里，它就很简单了——就像这样：

tell application "Microsoft Excel"
repeat with j from 1 to count large of selection
-- do stuff with the cell
set value of cell j of selection to "cell_" & j
end repeat
end tell

为了到达那里，我不得不做

tell application "Microsoft Excel"
set c to properties of selection
end tell
return c

然后浏览房产列表，直到我找到一个有前景的。这是一个很好的方式来获得财产列表。也许在AppleScript编辑器上有一个更快的方法，但我是一个命令行人员。

Answer 2

您可以尝试以下操作：

tell application "Microsoft Excel"
set range1 to range "A1:A5"
set value of range1 to {{1.0}, {2.0}, {3.0}, {4.0}, {5.0}}
set range2 to range "B1:B5"
set value of range2 to {3}
repeat with i from 1 to 5
set formula of row i of column 3 to "=A" & i & "+B" & i
end repeat
end tell

你可以在这里阅读更多

您也可以尝试：

set cellCount to count of (cells of selection)

Answer 3

这显然是一个老问题，但我认为这是一个好问题，其中一条评论提出了复杂的选择问题。因此，以下是使用MacExcel(在我的案例中是2011年)处理此问题的几种方法。没有错误处理，但底部是一个脚本，它将预先填充工作表以进行演示。根据您的选择有多大，它可能会崩溃。

首先，你可以做一件非常基本的事情：对选定的单元格进行计数。这应该适用于简单和复杂的选择。

tell application "Microsoft Excel" to count of cells of selection

单区域选择

其次，如果您有一个简单的选择，那么对单元格进行操作仍然不会太冗长。对于这里的测试，它适用于2x2范围。

tell application "Microsoft Excel"
select range "D2:E3"
set c to selection
value of areas of c
--> {{7.0, 21.0}, {8.0, 24.0}} -- note hierarchy

set voop to {}
set ab to count of cells in c
repeat with x from 1 to ab
set end of voop to value of cell x of c as integer
end repeat

voop
--> {7, 21, 8, 24} -- note absence of hierarchy
end tell

多区域选择

第三，这里有一个方法可以处理复杂或简单的选择。同样，底部的脚本将填充适当的示例数据。

areas of selection返回所选范围的列表。它总是一个列表，所以即使你只有一个范围(甚至一个单元格)，你也会得到一个列表。考虑到这一点，这将在选定范围的列表中循环，然后在每个范围中的每个单元格中循环。这个脚本比它需要的要大，因为我已经包含了一些你可以做的示例，以及做出选择和返回结果。

tell application "Microsoft Excel"
activate
-- make complex selection
set aran to range "A:B 3:4"
set bran to range "D2:E3"
set cran to range "G5:H7"
select (union range1 aran range2 bran range3 cran)

with timeout of 10 seconds
set c to areas of selection
--> {range "[Workbook1]Sheet1!$A$3:$B$4" of application "Microsoft Excel", range "[Workbook1]Sheet1!$D$2:$E$3" of application "Microsoft Excel", range "[Workbook1]Sheet1!$G$5:$H$7" of application "Microsoft Excel"}

set voop to {} -- value
set coop to {} -- cell address

repeat with euRg in c -- each range area
set ceuRg to cells of euRg -- hierarchical cell list -- critical step!
repeat with xy in ceuRg -- each cell in range area
-- do random stuff with each cell
copy contents of xy to end of coop
copy value of xy as integer to end of voop
set value of xy to ((value of xy) / 2)
end repeat
end repeat

-- do more random stuff
set AppleScript's text item delimiters to space
display dialog voop as text -- display original values
--> 8 66 6 44 7 21 8 24 1 2 3 4 5 6
coop -- flattened cell list
--> {cell "$A$3" of application "Microsoft Excel", cell "$B$3" of application "Microsoft Excel", cell "$A$4" of application "Microsoft Excel", cell "$B$4" of application "Microsoft Excel", cell "$D$2" of application "Microsoft Excel", cell "$E$2" of application "Microsoft Excel", cell "$D$3" of application "Microsoft Excel", cell "$E$3" of application "Microsoft Excel", cell "$G$5" of application "Microsoft Excel", cell "$H$5" of application "Microsoft Excel", cell "$G$6" of application "Microsoft Excel", cell "$H$6" of application "Microsoft Excel", cell "$G$7" of application "Microsoft Excel", cell "$H$7" of application "Microsoft Excel"}

end timeout
end tell

预填充选择

tell application "Microsoft Excel"
activate
set value of range "A:B 3:4" to {{8, 66}, {6, 44}}
set value of range "D2:E3" to {{7, 21}, {8, 24}}
set value of range "G5:H7" to {{1, 2}, {3, 4}, {5, 6}}
end tell