How to Script Excel's "Remove Duplicates" Function

Hello,

I’m trying to remove duplicates from an Excel file, but am not able to script the dialog box which pops up. I’ve read the Excel Applescript guide, which didn’t help (perhaps I missed something), I used UIBrowser to inspect the page, I even tried GUI scripting the dialog box, but it simply doesn’t seem to be able to select/click the “OK” button.

Here’s what I have so far:

tell application “Microsoft Excel”
activate
open “fill in the blank filename”
tell active sheet
select used range
remove duplicates of used range
select button “OK” of window “Remove Duplicates”
end tell
end tell

Does anyone know what the correct syntax is? Or if it’s even possible?

Thank you!

Hi,

this is code I use to remove duplicates, hopefully you can adapt it for your needs.

tell application "Microsoft Excel"
	set myref to active sheet of active workbook
	set activeRowCount to count of rows of used range of myref
	set myrange to range ("A1:C" & activeRowCount) of myref
	remove duplicates myrange
end tell

Nik

As far as I know, triggering the “OK” button must be done thru System Events.

Try :

tell application "Microsoft Excel"
    activate
    open "fill in the blank filename"
    tell active sheet of active workbook
        set myRange to used range
        remove duplicates of myRange
    end tell
end tell

tell application "System Events" to tell process "Microsoft Excel" -- check the name of the process
	click button "OK" of window "Remove Duplicates"
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 6 février 2020 18:32:03

Hi Nik and Yvan,

Thank you for your replies. I tried both scripts, but unfortunately, I still can’t seem to click/select the “OK” button on the dialog box. The script just runs, then times out with the error message:

Microsoft Excel got an error: AppleEvent timed out." number -1712

GUI scripting the enter/return key doesn’t seem to work either, which is frustrating because everything works until that part.

Any other thoughts? If not, I’ll try to find a workaround outside of Excel.

Thank you again for taking the time to share your insight :slight_smile:

I never use M…soft products.

You may try to use:

(*
Use the free command line CliClick available at : http://www.bluem.net/en/mac/cliclick/
*)
tell application "System Events" to tell process "Microsoft Excel" -- check the name of the process
	set frontmost to true
	tell window "Remove Duplicates"
		set {{x, y}, {w, h}} to {position, size} of button "OK"
	end tell
end tell
do shell script "/usr/local/bin/cliclick c:" & (x + w div 2) & "," & (y + h div 2)

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 6 février 2020 21:08:48

Thank you, Yvan. I’ll download CliCLick and give your script a try.

Take care,
Grant

It may be interesting to add three instructions supposed to give us useful infos.

(*
Use the free command line CliClick available at : http://www.bluem.net/en/mac/cliclick/
*)
tell application "System Events" to tell process "Microsoft Excel" -- check the name of the process
	set frontmost to true
	name of windows --> ??? - to be sure of the name of the window
	tell window "Remove Duplicates"
		class of UI elements --> ??? - to be sure of which UI elements are available
		title of buttons --> ???
		set {{x, y}, {w, h}} to {position, size} of button "OK"
	end tell
end tell
do shell script "/usr/local/bin/cliclick c:" & (x + w div 2) & "," & (y + h div 2)

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 6 février 2020 22:13:16

Hi Yvan,

Thanks for the updated script with explanations–It’s very helpful.

After installing Cliclick and running your script, I receive the message:

“error “sh: /usr/local/bin/cliclick: No such file or directory” number 127”

I’m not sure why. After receiving the error, I tried creating the directory manually in Terminal based on instructions from another forum, but it didn’t seem to work.

I’m very new to all of this, so I apologize for it being a little over my head :slight_smile:

This script is for a coworker, so I’m trying to keep it simple by using only Script Editor and Excel without having to download add-ons, but I understand if that’s not possible.

Thanks again!

Hi Yvan,

I was able to manually copy the CliClick exec file to where it needs to be (i.e. usr/local/bin) and the script worked!

Thank you so much!