Multiple filter in excel by applescript

Scripting Forums AppleScript | Mac OS X
#1

Hi All,

I wanna multiple filter in excel by applescript

excel format like below, filter gender is “F”, Happy is “N”, Name is “Jack”,“Mary”,“Lynn”, “Sue”. I can filter gender is “F” and Happy is “N” and work fine. How to do the next? Thanks a lot.

Name 1day 2day 3day gender Happy
Jack 4 745 98 M Y
Jack 3 2 1 M N
Jack 5 7 8 F N
Leo 44 144 44 M N
Leo 441 1456 5 M Y
Mary 5 1236 43 F N
Lynn 66 24 123 F N
Mark 124 123 6 M N
Ella 6 56 3 F Y
Ella 6 234 4 F N
Sue 1 1 1 F N
Sue 61 4 2 F N
Dave 5 5 5 M Y
Wed 7 9 11 F N
Lily 7 77 44 F N

I wrote script like below

tell application "Microsoft Excel"
  activate
 
 
          set RG_Row_2 to range "1:1"
      set LastColumn_Index to text 2 of (get address of (get offset of (get end range "A1" direction toward the right) column offset 1)) as text
          set Working_Range to range ("A:" & LastColumn_Index)
 
          set SKU_Index to get first column index of (find (RG_Row_2) what "Happy")
 
  autofilter range Working_Range field SKU_Index criteria1 "=N"
 
          set SKU_Index1 to get first column index of (find (RG_Row_2) what "gender")
 
  autofilter range Working_Range field SKU_Index1 criteria1 "=F"
 
 
end tell

-----Next is to filter Name is “Jack”,“Mary”,“Lynn”, “Sue”. I try to use advanced filter but failed, anyone can help? Thanks in advance.

tell application "Microsoft Excel"
  activate
 
 
          set RG_Row_2 to range "1:1"
          set LastColumn_Index to text 2 of (get address of (get offset of (get end range "A1" direction toward the right) column offset 1)) as text
          set Working_Range to range ("A:" & LastColumn_Index)
 
          set SKU_Index to get first column index of (find (RG_Row_2) what "Happy")
 
  autofilter range Working_Range field SKU_Index criteria1 "=N"
 
          set SKU_Index1 to get first column index of (find (RG_Row_2) what "gender")
 
  autofilter range Working_Range field SKU_Index1 criteria1 "=F"
 
 
          set value of range "G1:G5" to {{"Name"}, {"=\"=Jack\""}, {"=\"=Mary\""}, {"=\"=Lynn\""}, {"=\"=Sue\""}}
  advanced filter Working_Range action filter in place criteria range "G1:G5"
 
 
end tell

AppleScript: 2.5
Browser: Safari 536.26.17
Operating System: Mac OS X (10.8)

#2

I haven’t looked at the code in detail, but your AdvancedFilter would remove the AutoFiltering.
You’d need a 5 row 3 column Criteria Range
Name Gender Happy
Jack F N
Mary F N
Lynn F N
Sue F N

That will get you all the Jacks, Marys, Lynns or Sues , each of which is both female and not happy.

The criteria range requries headers.

#3

Thanks mikerickson,

I wrote below and work perfect. By the way, I filter from A18 due to I know the all date just 16 lines. If the date is more than hundreds lines which I do not know. How can I set the value of range? Thanks.

tell application "Microsoft Excel"
	activate
	
	-- get the range for the data in the main list
	set Working_Range to current region of cell "A1"
	
	-- create the criteria list programmatically
	set value of range "A18:C22" to {{"Name", "Happy", "gender"}, {"=\"=Jack\"", "N", "F"}, {"=\"=Mary\"", "", ""}, {"=\"=Lynn\"", "", ""}, {"=\"=Sue\"", "", ""}}
	-- get the range for the data in the criteria list
	set Criteria_Range to current region of cell "A18"
	
	-- filter
	advanced filter Working_Range action filter in place criteria range Criteria_Range
	
end tell
#4

BTW, why I cannot use <>M instead of F or <>Y instead of N in above code? Thanks.

Below script cannot work. Thanks.

tell application "Microsoft Excel"
	activate
	
	-- get the range for the data in the main list
	set Working_Range to current region of cell "A1"
	
	-- create the criteria list programmatically
	set value of range "A18:C22" to {{"Name", "gender", "Happy"}, {"=\"=Jack\"", "<>M", "<>Y"}, {"=\"=Leo\"", "", ""}, {"=\"=Ella\"", "", ""}, {"=\"=Sue\"", "", ""}}
	-- get the range for the data in the criteria list
	set Criteria_Range to current region of cell "A18"
	
	-- filter
	advanced filter Working_Range action filter in place criteria range Criteria_Range
	
end tell