Filter Excel with Applescript

Scripting Forums AppleScript | Mac OS X
#1

Hi all,

I have an Excel doc with filter drop downs applied to all columns.
What I want AppleScript to do is filter column 23 (W) of the current open workbook and active worksheet dependent on a user input chosen from a dialogue list.
The three criteria that may be present in the sheet will be:

To Artwork
Amend
Approved

Once chosen to only show the filtered rows based on the users input, then end.
If there is nothing to filter of that criterion then show a dialog stating “Nothing present” and exit the script.

I’ve got this far with a script of my own to no success (minus the “Nothing Present” block)

tell application “Microsoft Excel”

set possibleEntries to {“To Artwork”, “Amend”, “Approved”}

set selectedEntry to choose from list possibleEntries with prompt “Select an entry:” default items {“To Artwork”}

if selectedEntry is not false then

set selectedEntry to item 1 of selectedEntry

set filterRange to range “W:W” – column 23

set filteredData to filter filterRange of {selectedEntry}

show filteredData

end if

end tell

This gets an error as follows:
–Can’t get filter (range “W:W” of application “Microsoft Excel”) of {“whatever list item chosen”}.

Any pointers greatly received.

Regards

#2

Sorry, need to expand on the above also - in a separate script I need the filter function in Excel to show rows containing:

To Artwork AND/OR Amend AND/OR Approved

And if none of the above are present to only then show a dialog stating “Nothing present” and exit the script.

I’m assuming this would be simpler this one doesn’t need the dialog input as the search string is static?

Thanks in advance!

#3

Any help with these two scripts anyone?

#4

In excel, the filter command is read-only, so no matter what you do with it, you won’t filter any rows. Its intent is to report on the existing situation. The command to filter rows is autofilter range. Kind of a strange way of doing things but I have the impression that microsoft wasn’t really certain about what they wanted to do when they set this up.

As to your last post, it wouldn’t necessarily be simpler (because dialogues can be pretty simple) but it would likely be best to have one script that can encompass both scenarios. You can add a multiple selections allowed option to the choose list command.

#5

Thanks for this info. What I’m trying to do is after invoking the script you originally helped me with (clears all filters initially, copy a few cells to a safe area, etc) is return the filter state back to its original view. I did manage to get the script to invoke the Custom View menu using System Events but then you have to scroll and select the pre done filter view you need so doesn’t really speed things up.
Maybe I’ll try it without the clear all filter step?

#6

Nope, removing “clear all filters” doesn’t work - sheet needs to show all for the original script to run correctly. Oh well, thanks for your input.

#7

To bring a range into view, use the ‘go to…’ command. In Excel’s menus, it’s as I’ve written it but in applescript, it’s just goto.

Here are a few ways to use it. Try this in a new workbook (make sure it has a ‘Sheet1’).

What it does is first name a range. Named ranges are like variables but in Excel’s GUI. You can use them in formulas or in scripts. Typically you access them from the drop down to the left of the formula bar. By default, it doesn’t really look like a drop down but there is a double-arrow icon beside it. Normally, it displays the address of the active cell but if you click on the double arrow, it will pop up a list of the workbooks’s named ranges (assuming you have any in the workbook).

Then it uses the goto command along with the named range ‘corner’ to go there. The scroll option puts the area in the upper left corner of the window.

Next, after a brief delay so you can see the action, it goes back to the upper left corner of the sheet using a standard range object.

Then it returns to the named range ‘corner’ but this time without the scroll option, so the selected range will likely appear in the bottom right corner of the window. It finishes by activating the second cell of the selected range.

tell application "Microsoft Excel"
	activate
	tell workbook 1
		tell sheet "Sheet1"
			
			set c99 to range "Sheet1!Y99:Z100"
			set name of c99 to "corner"
			goto reference "corner" with scroll
			delay 2
			
			set bleep to range "Sheet1!A1:F10"
			goto reference bleep
			delay 2
			
			goto range "Sheet1!corner" -- this time without scroll
			activate object cell 2 of range "Sheet1!corner"
		end tell
	end tell
end tell

So in your case, you can use the goto command to visit different zones of the sheet as needed. I would give the ‘safe zone’ range a name (although it is not obligatory) as it is the easiest way to work with ranges on a large sheet. As a bonus, you can also move to a different sheet using it and use it in Excel (outside of any script), for example with the Edit > Go To… command.

I forget where you decided to place the safe zone in your sheet but if for example it was the range “A1000:F1000” then here are three ways to use goto to bring that range into view:

Update: Added a fourth approach… activate object

set sz to range "Sheet1!A1000:F1000"
set name of range "Sheet1!A1000:F1000" to "safezone"

goto range "Sheet1!A1000:F1000" with scroll
goto reference sz with scroll
goto reference "safezone" with scroll
activate object range "A1000:F1000"
#8

As to filtering the data, here is a script that will do so based on what is returned from a choose list command.

First, to defilter the data on a sheet use this command:

show all data

I didn’t have any relevant data so I’ve used a small table with the tab-separated data below in sheet 1 of a workbook beginning at cell A1.

What it does after going to sheet 1 is get the current region of cell A1, in this case, A1:F11 and assign it to a variable and show all data (in case it is already filtered).

After that, it throws up a choose list dialogue. You can use the shift and command keys to select multiple items. Note that this returns a list of the chosen items.

It then filters the range.

  • field 5 is column E
  • criteria1 is the list of items to filter on
  • operator controls how to handle the criteria
  • filter by value uses strings with the format “=Pittsburgh Steelers” and also enables the filter to handle a list of entries. This way, it can work with a single chosen item or multiple items.

You can filter on a single partial item by putting an asterisk on each side of the word “=*Steelers*” but it doesn’t seem to work with multiple items.

You could add a goto command after that to move the view to a different spot in the worksheet.

tell application "Microsoft Excel"
	activate
	tell workbook 1
		activate object worksheet "Sheet1"
		tell sheet 1
			set r1 to (get address of current region of range "A1")
			show all data
			
			set possibleEntries to {"Denver Broncos", "Pittsburgh Steelers", "Dallas Cowboys"}
			set selectedEntry to choose from list possibleEntries with prompt "Select an entry:" default items {"Pittsburgh Steelers"} with multiple selections allowed
			
			if selectedEntry is not false then
				autofilter range range r1 field 5 criteria1 selectedEntry operator filter by value
			end if
			
		end tell
	end tell
end tell

Test data:

Year	Lg	Pos	Player	Tm	Player-additional
1975	NFL	WR	Lynn Swann	Pittsburgh Steelers	SwanLy00
1974	NFL	FB	Franco Harris	Pittsburgh Steelers	HarrFr00
1973	NFL	FB	Larry Csonka	Miami Dolphins	CsonLa00
1972	NFL	FS	Jake Scott	Miami Dolphins	ScotJa01
1971	NFL	QB	Roger Staubach	Dallas Cowboys	StauRo00
1970	NFL	LLB	Chuck Howley	Dallas Cowboys	HowlCh00
1969	AFL	QB	Len Dawson	Kansas City Chiefs	DawsLe00
1968	AFL	QB	Joe Namath	New York Jets	NamaJo00
1967	NFL	QB	Bart Starr	Green Bay Packers	StarBa00
1966	NFL	QB	Bart Starr	Green Bay Packers	StarBa00
#9

Brilliant solution as ever! - I haven’t had chance to test out your ‘goto’ range view as yet but your filtering data solution works perfectly.

I’ve actually utilised this using a variable stored in an earlier script for the data input on column C, so no need to use the dialog and the script filters back to the original row showing that one only.

–earlier stuff here
tell application “Microsoft Excel”

activate

set cCol to range “C2:C3000” – range to search in

set cAsset to find cCol what cAC – the string to search for, i.e. asset code

tell workbook 1

activate object worksheet “Master Sheet”

tell sheet 1

set r1 to (get address of current region of range “A1”)

show all data

set selectedEntry to {cAC}

if selectedEntry is not false then

autofilter range range r1 field 3 criteria1 selectedEntry operator filter by value

end if

activate object range “A1”

end tell

end tell

end tell

#10

Looks good. Two things to consider:

First:

activate object worksheet “Master Sheet”
tell sheet 1

If “Master Sheet” isn’t the first sheet, then you may get some confused results. It might be worth using the same reference for both commands, e.g. tell sheet "Master Sheet.

Second:

Move the two lines (set cCol… and set cAsset…) inside the tell sheet 1 block. Again, there are opportunities for confusion about which sheet Excel may try to work with.

#11

Thanks for your catch @mockman - I’ll take a look at this next week and revise my script.
Thanks for the assist.