I’ve written a script that will go through a column in Excel and find and record any repeated items in that column. Then, it will copy the information from the rows where it found the repeats. Finally, it will create a text document of all the information. I’m using it to help me find duplicate MP3s in my collection, but it started as a real need at the office ( I love this part of my job). This is my first attempt at doing this with multiple columns.
It basically works by you telling it how many rows are in the spreadsheet, which column to check, which columns to copy, then creating a list for each column, and matching information amongst all three by its offset in the source list.
The odd behvior I’ve found is this: I can copy the information in columns A and B, but not C. It copies all the cells in column C as one selection as opposed to just individual cells in the column. If I place the information in column C into column D, and change the script accordingly, it works fine.
I’m getting pretty handy with Applescript, but this one has me stumped. Any ideas? Code is below. Thanks in advance for any help!
global match_counter
set theSongColumn to "A"
set theBandColumn to "B"
set theAlbumColumn to "D"
set songCount to 12
set theSongList to {}
set theBandList to {}
set theAlbumList to {}
tell application "Microsoft Excel"
Activate
set dataDoc to GetOpenFilename Title "Please select Excel data file."
Open dataDoc
repeat with s from 1 to songCount
set theSongCell to (theSongColumn & s) as text
CopyObject Cell theSongCell
try
set theSongCellContent to Clipboard as text
set end of theSongList to theSongCellContent
on error
set s to s
end try
end repeat
repeat with b from 1 to songCount
set theBandCell to (theBandColumn & b) as text
CopyObject Cell theBandCell
try
set theBandCellContent to Clipboard as text
set end of theBandList to theBandCellContent
on error
set b to b
end try
end repeat
repeat with l from 1 to songCount
set theAlbumCell to (theAlbumColumn & l) as text
CopyObject Cell theAlbumCell
try
set theAlbumCellContent to Clipboard as text
set end of theAlbumList to theAlbumCellContent
on error
set l to l
end try
end repeat
end tell
repeat with m from 1 to (get count of items in theSongList)
count_matches(theSongList, (item m of theSongList))
if match_counter ? 2 then
set this_data to (m as string) & tab & ((item m of theSongList) as string) & tab & ((item m of theBandList) as string) & tab & ((item m of theAlbumList) as string) & return
set this_file to (((path to desktop folder) as text) & "RepeatedItems")
my write_to_file(this_data, this_file, true)
end if
end repeat
beep
on count_matches(this_list, this_item)
set the match_counter to 0
repeat with i from 1 to the count of this_list
if item i of this_list is this_item then set the match_counter to the match_counter + 1
end repeat
return the match_counter
end count_matches
on write_to_file(this_data, target_file, append_data)
try
set the target_file to the target_file as text
set the open_target_file to open for access file target_file with write permission
if append_data is false then set eof of the open_target_file to 0
write this_data to the open_target_file starting at eof
close access the open_target_file
return true
on error
try
close access file target_file
end try
return false
end try
set repeatsFound to true
end write_to_file
I rarely script Excel, but had some time and tried to debug your situation. I broke it down and began from the beginning while looking at the data. Using the Event log, when it got to copying “C1” it copied column 1. “C2” copies column 2 and so on. So it appears that you need to rewrite your script using a different strategy. I don’t know why you need to copy anyway because it seems that there should be a way to just get the data in the cell.
Much faster for getting cell values is directly instead of copying and getting the clipboard. Here’s an example I was fooling around with. Basically you make a range and use the ‘every element’ reference form. I added some extra stuff because I couldn’t figure out how your script would work. You can look at it if you want. It needs a lot of testing at the end because I’m hungry.
tell application “Microsoft Excel”
Activate
set first_cell_string to “r1c1”
set row_count to Row of (LastCell Range first_cell_string Direction xlDown)
set col_count to Column of (LastCell Range first_cell_string Direction xlToRight)
– create the range
set data_range_string to first_cell_string & “:” & “r” & row_count & “c” & col_count
set range_ref to Range data_range_string
– get the values
set row_values to (Value of every Cell of range_ref) – list of lists (row values)
– extra
– find repeats in column 1
copy row_values to temp_list – to define temp_list
set write_strings to {} – list of strings that repeat
set used_indices to {} – for greater than 2 repeats
set r1 to 0
repeat with this_row_list in row_values
set r1 to r1 + 1
if r1 is not in used_indices then
set col1_item to (item 1 of this_row_list) – compare with this item
set temp_list to rest of temp_list – skip the first item col1_item
– start the string with campare item
tell this_row_list
set this_string to (r1 as string) & tab & item 2 & tab & item 3 & return
end tell
set start_length to length of this_string
set r2 to r1 – index for matches with r1 item
repeat with this_list in temp_list
set r2 to r2 + 1 – increment match index
set this_item to (item 1 of this_list)
if this_item is col1_item then – match
tell this_list
set this_string to this_string & tab & r2 & tab & item 2 & tab & item 3 & return
end tell
set end of used_indices to r2
end if
end repeat
if length of this_string > start_length then
set end of write_strings to this_string
end if
end if
end repeat
end tell
return write_strings
Just so you know, you were a big help. Your script reminded that we had a one here at the office that collected data from an Excel spreadsheet in a format very similar to m music list. I modified that one to do what I needed. It work in less than a quarter of the time taken in my first version. I’ve actually found other uses for this script as well. Thanks for the help, and here’s the code:
set startTime to (current date)
--establishes column letter text needed for reports
property columnsLettersAll : {"A", "B", "C"}
--establishes lists to check
property SongColumn : {}
property AlbumColumn : {}
property BandColumn : {}
--establishes globals
global match_counter
global emptyCaught
global errorCaught
global currentColumn
global TheImages
global itemCount
set emptyCaught to false
set errorCaught to false
tell application "Microsoft Excel"
Activate
set dataDoc to GetOpenFilename Title "CheckMusicList: Please select Excel data file."
Open dataDoc
--establishes arrays and checks empties simultaneously
repeat with c from 1 to (get the count of columnsLettersAll)
CopyObject Column (item c of columnsLettersAll)
set dataText to Clipboard as text
set currentColumn to (item c of columnsLettersAll)
if SongColumn is {} then
my prepList(dataText, SongColumn)
my checkEmpties(SongColumn)
set itemCount to (get count of items in SongColumn)
else if AlbumColumn is {} then
my prepList(dataText, AlbumColumn)
my checkEmpties(AlbumColumn)
else if BandColumn is {} then
my prepList(dataText, BandColumn)
my checkEmpties(BandColumn)
end if
end repeat
end tell
--checks(SongColumn)1
repeat with x from 1 to (get count of items in SongColumn)
checkDupes(SongColumn, (item x of SongColumn))
if match_counter ? 2 then
set this_data to (x as string) & tab & ((item x of SongColumn) as string) & tab & ((item x of AlbumColumn) as string) & tab & ((item x of BandColumn) as string) & return
set this_file to (((path to desktop folder) as text) & "RepeatedSongs")
my write_to_file(this_data, this_file, true)
set errorCaught to true
end if
end repeat
--cell error handler
if errorCaught is true then
display dialog "Errors have been found. Check the report on the Desktop."
stop
end if
set SongColumn to {}
set AlbumColumn to {}
set BandColumn to {}
set endTime to (current date)
set runTime to (endTime - startTime)
set this_data to "Run time: " & runTime & " seconds for " & itemCount & " files." & return
set this_file to (((path to desktop folder) as text) & "RepeatedSongs")
my write_to_file(this_data, this_file, true)
display dialog "Finished!"
--------------------------------------------
--DATA CHECK SUBROUTINES
--------------------------------------------
--Coerces the lists into something all handlers can use
on prepList(currentDataText, thisColumn)
repeat with z from 1 to (count of paragraphs in currentDataText)
set end of thisColumn to (paragraph z of currentDataText)
end repeat
return thisColumn
end prepList
-- checks for empty cells (a double check for cullArray in MakeITKPagesInDesign)
on checkEmpties(theList)
repeat with a from 1 to the count of items in theList
if item a of theList is "" then
set this_data to "Empty cell: " & currentColumn & (a as string) & return
set this_file to (((path to desktop folder) as text) & "RepeatedSongs")
my write_to_file(this_data, this_file, true)
set emptyCaught to true
end if
end repeat
end checkEmpties
-- checks for duplicate items in figure callout and filename columns
on checkDupes(theList, theItem)
set the match_counter to 0
repeat with i from 1 to the count of items in theList
if item i of theList is theItem then set the match_counter to the match_counter + 1
end repeat
return the match_counter
end checkDupes
on write_to_file(this_data, target_file, append_data)
try
set the target_file to the target_file as text
set the open_target_file to ¬
open for access file target_file with write permission
if append_data is false then ¬
set eof of the open_target_file to 0
write this_data to the open_target_file starting at eof
close access the open_target_file
return true
on error
try
close access file target_file
end try
return false
end try
end write_to_file