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?
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
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
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
(*
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
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
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
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.