Excel v.X: Odd behavior in selecting cells.

Howdy–

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

Hi,

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.

gl,

Hi,

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

gl,

Kel,

Thanks for the help. I’ll play with your script and let you know what happens.

P

Hi, Kel–

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