Apologis for not necessarily providing the best description of the problem but it is had to do in a few words so here is the more detailed description…the first time I run the script it hangs in that it i) opens Excel but nothing happens (i.e. no further commands are executed), the second (and subsequent) times I run Excel the script runs flawlessly.
I thought the problem was the delay / latency in opening Excel before the subsequent commands could be excited so added a but of “delay” commands but that did no solve the problem.
I would appreciate someone showing me how to to fix the problem. If you want to run / text the script (which is self contained) please make sure you comment out the Moom tell block which is used solely to centre Excel on my monitor.
Thanks in advance,
Joel
-- Create a dialog box to inform the user that the script is running
-- This is the code to be used when placing the dialog box outside of a tell block
set time1 to current date
set progress completed steps to 10 -- Note this read "progress total steps" which resulted in the step number in the bottom corner of the dialog box
repeat with i from 1 to 10
set progress description to "Processing " & (i - 1) & " of " & 10
(*
do shell script "sleep 0.5" -- Commented out as no need to count step number in bottom corner of dialog box
set progress completed steps to i -- Commented out as no need to count step number in bottom corner of dialog box
*)
end repeat
-- display dialog ((current date) - time1) as text -- Commented out as no need for a dialog box that list number of seconds to complete the script
tell application "Contacts"
-- Set variables
set firstNames to {} -- List of all first names from all Contacts
set lastNames to {} -- List of all last names from all Contacts
set orgNames to {} -- List of all organization names from all Contacts
set phoneNumbers to {} -- List of all work telephone numbers from all Contacts
set myList to {} -- List of all first names, last names, organization names, work telephone numbers and "first name last name" combinations from all Contacts
set myListNames to {} -- List of all "first name last name" combinations from all Contacts
set myListNamesUnique to {} -- List of myListNames WITH DUPLICATE ENTRIES REMOVED
set myListUnique to {} -- List of myList WITH DUPLICATE ENTRIES REMOVED
set myListNamesDuplicate to {} -- List of myListNames DUPLICATES
set myListDuplicate to {} -- List of myList DUPLICATES ENTRIES
set myListPhantom to {} -- List of myList PHANTOM ENTRIES
-- Set / extract the Contact first names, last names, organization names and work telephone numbers
set {firstNames, lastNames, orgNames, phoneNumbers} to {first name, last name, organization, value of every phone whose label is "work"} of every person
repeat with i from 1 to count lastNames
if item i of firstNames is equal to missing value then set item i of firstNames to " "
if item i of lastNames is equal to missing value then set item i of lastNames to " "
if item i of orgNames is equal to missing value then set item i of orgNames to " "
if item i of phoneNumbers is equal to missing value then set item i of phoneNumbers to " "
end repeat
(* log lastNames -- Commented out as inserted for testing purposes
log firstNames
log orgNames
log phoneNames*)
repeat with i from 1 to (count lastNames)
set end of myList to {i, item i of firstNames, item i of lastNames, item i of orgNames, item i of phoneNumbers, item i of firstNames & " " & item i of lastNames}
set end of myListNames to (item i of firstNames & " " & item i of lastNames)
end repeat
-- Set / create the list of duplicate entries and unique entries noting that the test is based on matching "first name last name" as an "extended test" [i.e a test that also includes other contact properties] would not produce the correct results [i.e. two contact records that identical in every sense except the first record has the organization as ACME while the second record has the organization as ACME Inc. would not be identified as being a duplicate].
repeat with i from 1 to (count myListNames)
if (((count of item 2 of item i of myList) < 2) and ((count of item 3 of item i of myList) < 2) and ((count of item 4 of item i of myList) < 2)) then
set end of myListPhantom to item i of myList -- Ignore / kickout the phantom contacts
else
if ((item i of myListNames is not in myListNamesUnique) or (((count of item 3 of item i of myList) < 2) and (item 4 of item i of myList > 1))) then -- The "or" portion of the test is to include retailers, restaurants, etc. where i) there is no first name or last name but ii) there is an organization name. WIthout this added test these contact records would eb considered duplicates because there "first nane last name" combination [i.e. " "] would be in myListNamesUnique
set end of myListNamesUnique to item i of myListNames
set end of myListUnique to item i of myList
else
set end of myListNamesDuplicate to item i of myListNames
set end of myListDuplicate to item i of myList
end if
end if
end repeat
(* log "myList" -- Commented our as inserted for testing purposes
log myList
set myListCount to count myList
set myListUniqueCount to count myListUnique
set myListDuplicateCount to count myListDuplicate
set myListPhantomCount to count myListPhantom
log "myListCount " & myListCount
log "myListUniqueCount " & myListUniqueCount
log "myListDuplicateCount " & myListDuplicateCount
log "myListPhantomCount " & myListPhantomCount
log "Duplicate Listing"
log myListDuplicate
log "Phantom Listing"
log myListPhantom *)
end tell
tell application "Microsoft Excel"
open
delay 5.0
-- Get current date in YYYYMMDD format
set currentDate to current date
set dateFunction to load script POSIX file "/Users/JoelC/Documents/Apple/Scripts/Utilities/20141227_script to convert a date in seconds to a date in yyyymmdd.scpt" as alias
set dateReturned to dateYYYYMMDD(currentDate) of dateFunction
set currentDateYYYYMMDD to dateReturned
-- Make and name a new workbook
make new workbook
set theBook to the active workbook
set theSheet to the active sheet of the theBook
-- Move the workbook to the centre and forefront of the screen
delay 1.0
tell application "Moom"
open
arrange windows according to snapshot named "Align Microsoft Excel"
end tell
-- Set the magniifcation / zoom percentage
delay 1.0
set zoom of the active window to 100
-- Set the column widths in the new workbook for readability
delay 1.0
set column width of the first column of theSheet to 3
set column width of the second column of theSheet to 5
set column width of the third column of theSheet to 20
set column width of the fourth column of theSheet to 70
set column width of the fifth column of theSheet to 3
-- Set and format the column heading and titles
delay 1.0
set myRangeCells to range ("B2:D2") of theSheet
set weight of (get border of myRangeCells which border edge top) to border weight thick
set borderWeightLog to get weight of (get border of myRangeCells which border edge top)
set insertedTextTop to "Contact Records Which Appear Two or More Times" as string
set insertedTextBottom to "Selected Contact Source: Work's Exchange Server"
set myRangeTop to range ("B3:B3") of theSheet
set myRangeBottom to range ("B4:B4") of theSheet
set value of myRangeTop to insertedTextTop
set value of myRangeBottom to insertedTextBottom
set myRangeCells to range ("B7:D7") of theSheet
set weight of (get border of myRangeCells which border edge top) to border weight thin
set borderWeightLog to get weight of (get border of myRangeCells which border edge top)
set myRangeCells to range ("B3:B3")
set font size of font object of myRangeCells to 16
set font style of font object of myRangeCells to "Bold"
set fontStyleLog to (get font style of font object of myRangeCells)
set myRangeCells to range ("B4:B4")
set font style of font object of myRangeCells to "Bold"
-- set fontStyleLog to (get font style of font object of myRangeCells)
-- log fontStyleLog
set InsertedTextBottom1 to "Count"
set InsertedtextBottom2 to "Name"
set InsertedTextBottom3 to "Organization"
set myRangeBottom1 to range ("B9:B9") of theSheet
set myRangeBottom2 to range ("C9:C9") of theSheet
set myRangeBottom3 to range ("D9:D9") of theSheet
set value of myRangeTop to insertedTextTop
set value of myRangeBottom1 to InsertedTextBottom1
set value of myRangeBottom2 to InsertedtextBottom2
set value of myRangeBottom3 to InsertedTextBottom3
set myRangeCells to range ("B9:D9") of theSheet
set horizontal alignment of myRangeCells to horizontal align center
set myRangeCells to range ("B11:C100000") of theSheet
set horizontal alignment of myRangeCells to horizontal align center
set myRangeCells to range ("B9:D9")
-- set font size of font object of myRangeCells to 16
set font style of font object of myRangeCells to "Bold"
set myRangeCells to range ("B9:D9") of theSheet
set weight of (get border of myRangeCells which border edge bottom) to border weight thin
set borderWeightLog to get weight of (get border of myRangeCells which border edge bottom)
-- Import myListDuplicates
delay 1.0
repeat with i from 1 to count of myListDuplicate
set value of cell ("B" & (10 + i) as string) to i
set value of cell ("C" & (10 + i) as string) to item 6 of item i of myListDuplicate
if item 4 of item i of myListDuplicate < 2 then set item 4 of item i of myListDuplicate to "Organization field is blank"
set value of cell ("D" & (10 + i) as string) to item 4 of item i of myListDuplicate
end repeat
sort range ("C11:D" & (10 + (count of myListDuplicate) as string)) of worksheet theSheet key1 (range "C11" of worksheet theSheet) key2 (range "D11" of worksheet theSheet)
-- Set the name of the spreadsheet to be saved
set userName to do shell script "whoami"
set fileNameSaved to "Macintosh HD:Users:" & userName & ":Desktop:" & currentDateYYYYMMDD & "_contact records which appear two or more times.xls"
-- Save the workbook / spreadsheet
tell theBook
save workbook as theBook filename fileNameSaved overwrite yes
end tell
-- Test, and depending on the test result, close the spreadsheet
display dialog "Do you want to close the Excel spreadsheet which lists those contact records which appear two or more times?" buttons {"Yes", "No"} default button 2 giving up after 5
set spreadsheetClose to button returned of result
if ((spreadsheetClose = "Yes") is true) then quit
end tell