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.


  1. 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.
  2. 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.
  3. 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

Posted on Feb 25, 2024 11:34 AM

Reply

There are no replies.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Writing AppleScripts to Find & Replace text in an Excel Spreadsheet/Word Document, according to a 2-column Excel Spreadsheet?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.