Writing AppleScripts to Find & Replace text in an Excel Spreadsheet/Word Document, according to a 2-column Excel Spreadsheet?
I have a master Excel Spreadsheet. ColumnA lists words & phrases; ColumnB lists their abbreviations.
I'd like to write an AppleScript to Find & Replace ColumnA with ColumnB 1) in Word Documents; & 2) in another Excel Spreadsheet.
I found the AppleScript below, which works reasonably well at Finding & Replacing in Word Documents.
- It could be improved if I can figure out a way to use wildcards so I don't have to populate ColumnA w/ all tenses, singular & plural, etc.
- Also, I'd like to Find "__" (2 spaces) & Replace it w/ "_" (a single space). I'd like to loop this repeatedly until there are no more "__" instances.
- But when I alter the AppleScript below to replace "tell application 'Microsoft Word'" with "tell application 'Microsoft Excel'" & "open file theDOCX" with "open file theXLSX," I encounter this Syntax error when I try to run the script: "Expected end of line, etc. but found class name."
AppleScript to open an Excel spreadsheet and copy its used range into a list of rows
comprised of the find string and replacement strings. The script will then loop through
all rows in the AppleScript list and perform a find/replace in the Word document.
use scripting additions
set theXLSX to (choose file of type ("org.openxmlformats.spreadsheetml.sheet"))
set theDOCX to (choose file of type {"org.openxmlformats.wordprocessingml.document"})
tell application "Microsoft Excel"
activate
open file theXLSX
tell active sheet of document 1
-- create a list of lists where each row will become a two component list item
-- comprised of the find string and the replace string
set arange to value of used range
end tell
end tell
tell application "Microsoft Word"
activate
open file theDOCX
set myfind to find object of text object of active document
set properties of myfind to {match case:false, match whole word:true, match wildcards:false}
repeat with arow in arange
execute find myfind find text (item 1 of arow) replace with (item 2 of arow) replace replace all
end repeat
end tell
MacBook Pro 13″, macOS 14.3