Read value in Excel, find and replace in pages in text box.

Scripting Forums AppleScript | Mac OS X
#1

My first attempt at applescript. I want to read a series of values in an open Excel sheet and replace placeholders in an open pages document. Placeholders can be in text body or in text boxes. Working off a few examples.

The following code only works for body text, what do I need to alter to work for text boxes as well? Greatly appreciate any pointers.

tell application "Microsoft Excel"
	activate
	set name1 to string value of cell ("F6")
end tell

tell application "Pages"
	activate
	display dialog "This script will replace all occurences of “placeholder” in the open Pages document." with icon 1 buttons {"Cancel", "Begin"} default button 2
	
	my replaceWordWithStringInBodyText("placeholder", name1)
	
	set thisTitle to "Pages: Find & Change"
	set thisNotification to ""
	set thisSubtitle to "completed."
	my displayThisNotification(thisTitle, thisNotification, thisSubtitle)
	set thisNotification to "Replacement completed"
	set thisSubtitle to ""
	my displayThisNotification(thisTitle, thisNotification, thisSubtitle)
end tell

on displayThisNotification(thisTitle, thisNotification, thisSubtitle)
	tell current application
		display notification thisNotification with title thisTitle subtitle thisSubtitle
	end tell
end displayThisNotification

on replaceWordWithStringInBodyText(searchWord, replacementString)
	tell application "Pages"
		activate
		tell the front document
			tell body text
				-- start at the end and go to the beginning
				repeat with i from the (count of paragraphs) to 1 by -1
					tell paragraph i
						repeat
							try
								if exists searchWord then
									set (last word where it is searchWord) to replacementString
								else
									exit repeat
								end if
							on error errorMessage
								exit repeat
							end try
						end repeat
					end tell
				end repeat
			end tell
		end tell
		return true
	end tell
end replaceWordWithStringInBodyText
#2

I would try something like this:

tell application "Microsoft Excel"
	activate
	set name1 to string value of cell ("F6")
end tell

tell application "Pages"
	activate
	display dialog "This script will replace all occurences of “placeholder” in the open Pages document." with icon 1 buttons {"Cancel", "Begin"} default button 2
	
	my replaceWordWithStringInBodyText("placeholder", name1)
	my ReplaceWordWithStringInTextItems("placeholder", name1)
	
	set thisTitle to "Pages: Find & Change"
	set thisNotification to ""
	set thisSubtitle to "completed."
	my displayThisNotification(thisTitle, thisNotification, thisSubtitle)
	set thisNotification to "Replacement completed"
	set thisSubtitle to ""
	my displayThisNotification(thisTitle, thisNotification, thisSubtitle)
end tell

on ReplaceWordWithStringInTextItems(searchWord, replacementString)
	tell application "Pages"
		activate
		tell the front document
			set allTextItems to every text item
			repeat with thisTextItem in allTextItems
				
				tell object text of thisTextItem
					-- start at the end and go to the beginning
					repeat with i from the (count of paragraphs) to 1 by -1
						tell paragraph i
							repeat
								try
									if exists searchWord then
										set (last word where it is searchWord) to replacementString
									else
										exit repeat
									end if
								on error errorMessage
									exit repeat
								end try
							end repeat
						end tell
					end repeat
				end tell
			end repeat
		end tell
		return true
	end tell
end ReplaceWordWithStringInTextItems

on displayThisNotification(thisTitle, thisNotification, thisSubtitle)
	tell current application
		display notification thisNotification with title thisTitle subtitle thisSubtitle
	end tell
end displayThisNotification

on replaceWordWithStringInBodyText(searchWord, replacementString)
	tell application "Pages"
		activate
		tell the front document
			tell body text
				-- start at the end and go to the beginning
				repeat with i from the (count of paragraphs) to 1 by -1
					tell paragraph i
						repeat
							try
								if exists searchWord then
									set (last word where it is searchWord) to replacementString
								else
									exit repeat
								end if
							on error errorMessage
								exit repeat
							end try
						end repeat
					end tell
				end repeat
			end tell
		end tell
		return true
	end tell
end replaceWordWithStringInBodyText