Scripting filters

[Tobias Weber]

Hi,
the following AppleScript works fine but only as long as the cell
selected when calling it is in the final result :-(

tell application "Microsoft Excel"
AutoFilter Selection Field 3 Criteria1 "f"
AutoFilter Selection Field 6 Criteria1 ">0"
Sort Selection Key1 Range "R2C2" Order1 xlDescending Key2 Range
"R2C4" Order2 xlAscending Key3 Range "R2C13" Order3 xlDescending Header
xlGuess Orientation xlTopToBottom without MatchCase
end tell

 

[JE McGimpsey]

Hi,
the following AppleScript works fine but only as long as the cell
selected when calling it is in the final result :-(

tell application "Microsoft Excel"
AutoFilter Selection Field 3 Criteria1 "f"
AutoFilter Selection Field 6 Criteria1 ">0"
Sort Selection Key1 Range "R2C2" Order1 xlDescending Key2 Range
"R2C4" Order2 xlAscending Key3 Range "R2C13" Order3 xlDescending Header
xlGuess Orientation xlTopToBottom without MatchCase
end tell

Is there a question there?

Certainly if your Selection (or the current region of the Selection if
the Selection contains only one cell) doesn't include the range you want
to sort, then there will be nothing to sort.

You don't indicate what you want to happen, though... do you want an
error message?

Perhaps you want to specify a range to filter and sort. Here's one way
(I'm assuming you're using XL v.X, since your script doesn't compile for
XL04 - this is written for XL04, but should be able to be converted):

tell application "Microsoft Excel"
set SortRange to current region of range "B2"
autofilter range SortRange ¬
field 3 ¬
criteria1 "f"
autofilter range SortRange ¬
field 6 ¬
criteria1 ">0"
sort SortRange ¬
key1 range "B2" order1 sort descending ¬
key2 range "D2" order2 sort ascending ¬
key3 range "M2" order3 sort descending ¬
header header guess ¬
orientation sort columns ¬
without match case
end tell

 

[Tobias Weber]

Is there a question there?

Obviously I'm unhappy with the script not working under all conditions

Perhaps you want to specify a range to filter and sort. Here's one way

The whole table

(I'm assuming you're using XL v.X, since your script doesn't compile for
Yes

tell application "Microsoft Excel"
set SortRange to current region of range "B2"

That's the right idea, thanks. I probably should define a name for the
whole table. The problem with using (CurrentRegion of ActiveCell) is
that it changes to a single cell when the selection is filtered out! So
your script has to be fixed by making it a reference. Isn't it supposed
to be the other way round?

Anyway the following seems to work:

try
tell application "Microsoft Excel" to ShowAllData ActiveSheet --fails
unless AutoFilter active
end try

tell application "Microsoft Excel"
set SortRange to a reference to CurrentRegion of ActiveCell
AutoFilter SortRange Field (Column of Range "Tag") Criteria1 "f"
--needs defined names
AutoFilter SortRange Field (Column of Range "SomeField") Criteria1
">0"
Sort SortRange Key1 "Five_Tags" Order1 xlDescending Key2 "Unwatched"
Order2 xlAscending Key3 "AnotherField" Order3 xlDescending Header
xlGuess Orientation xlTopToBottom without MatchCase
end tell

PS I use several of these to get different views on my data. Can't
afford FileMaker.

 

[JE McGimpsey]

Anyway the following seems to work:

Not sure why you're using Active Cell at all.

Since you've got other ranges fixed (e.g., "Tag", "SomeField",
"Five_Tags", etc), wouldn't it be easier to name your data range, rather
than using ActiveCell?

If the range varies in size, you could make it dynamic by defining it
(Insert/Name/Define), for instance

Names in workbook: SortRange
Refers to:

=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$65536),
COUNTA(Sheet1!$2:$2))