Read from Excel and replace words in Word file (template file)

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:

  1. Open an existing Excel file
  2. For each non empty row in the Excel file I want to read the columns 1-6 and store the data in a list.
  3. Then I want to open an existing Word file (which is a template file) which contains certain keywords like NAME, SURNAME, DATEOFBIRTH, etc.
  4. Then I want to replace the keywords in the Word file by the words I read from the Excel file.
  5. 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