Hi everyone!
I thought I would be able to figure this out, but the learning curve is just too steep. Perhaps someone of you can help me get started with this.
What I want to do is the following:
- Open an existing Excel file
- For each non empty row in the Excel file I want to read the columns 1-6 and store the data in a list.
- Then I want to open an existing Word file (which is a template file) which contains certain keywords like NAME, SURNAME, DATEOFBIRTH, etc.
- Then I want to replace the keywords in the Word file by the words I read from the Excel file.
- Finally I want to store the Word file as “NAME SURNAME - something.docx”, where NAME and SURNAME are again taken from the list I read earlier.
I want this to repeat for each non empty row in the Excel file and the final result should be a a collection of word files (one for each row).
In case it is easer I would also accept using Numbers and Pages to complete this task. (I think converting the files I have works reasonably well).
I have been at this for a while, but so far I have just managed to open the two files. I think I also have figured out how to select the first word in the Word file and setup a search and replace operation.
Any help I can get is highly appreciated!
In case someone has a similar problem in the future I post my own solution for this problem. I managed to solve this (cutting and pasting a lot of scripts from different sources).
set cellList to {"A", "B", "C", "D"}
(*
Gender (M or F) in column A
First name in column B
Family name in column C
Date of birth in column D
*)
set thePath to (POSIX path of ((path to home folder as string) & "Dropbox" & ":" & "Applescript"))
set excelFileName to "/Students.xlsx"
set wordFileName to "/SWUCertificationEnglish16StipendiumTEMPLATE.docx"
-- Get number of non-empty rows
tell application "Microsoft Excel"
set excelfile to open (POSIX path of (thePath & excelFileName))
-- set rowCount to count of rows
set rowCount to first row index of (get end (last cell of column 1) direction toward the top)
-- display dialog rowCount
end tell
-- Repeat for each student
repeat with student from 2 to rowCount
set wordList to {} -- Empty list
-- Copy the data from Excel to wordList
tell application "Microsoft Excel"
repeat with j from 1 to 4
set wordList to wordList & value of cell ((item j of cellList) & student)
end repeat
end tell
-- Format date as string
set datestring to dateFormat((date string of (fourth item of wordList)))
-- display dialog (date string of (fourth item of wordList))
set item 4 of wordList to datestring
-- Open required file
tell application "Microsoft Word"
open (POSIX path of (thePath & wordFileName))
set workingfile to active document
end tell
-- Do the required replacements
set replaceWord to (item 2 of wordList)
findAndReplace("NAME", replaceWord, workingfile)
set replaceWord to (item 3 of wordList)
findAndReplace("FAMILYNAME", replaceWord, workingfile)
set replaceWord to (item 4 of wordList)
findAndReplace("DATEOFBIRTH", replaceWord, workingfile)
-- If male
if (item 1 of wordList) is equal to "M" then
findAndReplace("Mr/Ms", "Mr", workingfile)
findAndReplace("He/She", "He", workingfile)
findAndReplace("he/she", "he", workingfile)
findAndReplace("His/Her", "His", workingfile)
findAndReplace("his/her", "his", workingfile)
end if
-- If female
if (item 1 of wordList) is equal to "F" then
findAndReplace("Mr/Ms", "Ms", workingfile)
findAndReplace("He/She", "She", workingfile)
findAndReplace("he/she", "she", workingfile)
findAndReplace("His/Her", "Her", workingfile)
findAndReplace("his/her", "her", workingfile)
end if
tell application "Microsoft Word"
set studentName to (item 2 of wordList)
set studentFamilyName to (item 3 of wordList)
set fileName to "English Certificate - SWU - 2016 - " & studentName & " " & studentFamilyName & ".docx"
save as active document file name (POSIX path of (thePath & "/" & fileName))
close active document
end tell
-- Add additional substitutions required
end repeat
tell application "Microsoft Excel"
close active workbook
end tell
on findAndReplace(findText, replaceText, workingfile)
tell application "Microsoft Word"
select word 1 of workingfile
-- All word objects have a find object property which refers to a find class
set findRange to find object of selection
clear formatting findRange
tell findRange
set forward to true
set match case to true
set match whole word to true
set wrap to find continue
set format to true
execute find find text findText replace with replaceText replace replace all
end tell
end tell
end findAndReplace
on dateFormat(old_date) -- Old_date is text, not a date.
set {year:y, month:m, day:d} to date old_date
tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & "-" & text 5 thru 6 & "-" & text 7 thru 8
end dateFormat