Friday, October 23, 2020

#1 2020-02-05 07:23:13 pm

GEC1227
Member
Registered: 2020-01-22
Posts: 15

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!

Offline

 

#2 2020-02-06 10:37:30 am

blend3
Member
From:: UK
Registered: 2006-03-28
Posts: 471

Re: How to Script Excel's "Remove Duplicates" Function

Hi,

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

Applescript:

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

Last edited by blend3 (2020-02-06 10:37:48 am)


I can resist everything in life except temptation.
(Oscar Wilde)

Offline

 

#3 2020-02-06 11:32:22 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4570

Re: How to Script Excel's "Remove Duplicates" Function

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

Try :

Applescript:

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

Last edited by Yvan Koenig (2020-02-06 11:34:58 am)

Offline

 

#4 2020-02-06 01:15:59 pm

GEC1227
Member
Registered: 2020-01-22
Posts: 15

Re: How to Script Excel's "Remove Duplicates" Function

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 smile

Offline

 

#5 2020-02-06 02:09:28 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4570

Re: How to Script Excel's "Remove Duplicates" Function

I never use M…soft products.

You may try to use:

Applescript:

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

Offline

 

#6 2020-02-06 02:24:09 pm

GEC1227
Member
Registered: 2020-01-22
Posts: 15

Re: How to Script Excel's "Remove Duplicates" Function

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

Take care,
Grant

Offline

 

#7 2020-02-06 03:16:05 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4570

Re: How to Script Excel's "Remove Duplicates" Function

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

Applescript:

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

Last edited by Yvan Koenig (2020-02-06 03:16:30 pm)

Offline

 

#8 2020-02-06 05:56:54 pm

GEC1227
Member
Registered: 2020-01-22
Posts: 15

Re: How to Script Excel's "Remove Duplicates" Function

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 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!

Offline

 

#9 2020-02-06 08:26:20 pm

GEC1227
Member
Registered: 2020-01-22
Posts: 15

Re: How to Script Excel's "Remove Duplicates" Function

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!

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)