Wednesday, December 8, 2021

#1 2021-11-21 11:11:07 am

Corum
Member
Registered: 2021-11-19
Posts: 1

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

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.

Applescript:

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

Offline

 

#2 2021-11-21 07:39:37 pm

estockly
Member
Registered: 2009-01-03
Posts: 5

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

I would try something like this:

Applescript:

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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)