Replace Text in Pages with Data from Excel [Example] Macro (v9.0.3)

Macro Library
1

Use Case

  • You have an Apple Pages Document/Template with placeholder text that needs to be replaced with actual data
  • Your Source Data is in an Excel File
  • Pages Does NOT properly support automation of full Find and Replace
  • So, We Make use of the Mac Standard Set Find Pasteboard to Make the Changes

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Example Input

image
image.png888×594 52.6 KB

Excel File

image
image.png838×434 68.7 KB

Example Output

image
image.png864×522 33 KB

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MACRO:   Replace Text in Pages with Data from Excel [Example]

~~~ VER: 2.0    2019-11-18 ~~~
Requires: KM 8.2.4+   macOS 10.11 (El Capitan)+
(Macro was written & tested using KM 9.0+ on macOS 10.14.5 (Mojave))

DOWNLOAD Macro File:

Replace Text in Pages with Data from Excel [Example].kmmacros
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.

ReleaseNotes

Author.@JMichaelTX

PURPOSE:

  • Replace Text in Pages with Data from Excel

HOW TO USE

  1. First, make sure you have followed instructions in the Macro Setup below.
  2. Open Pages Document to be Used
    • Make sure Text in Pages Matches Pattern to Find as Shown Below
  3. Open Excel Fie to Workbook/Woksheet to be use
    • Select the Cell in Excel to Start Copy From
    • Make sure a BLANK Cell is below the last Cell to copy
  4. Trigger this macro.

MACRO SETUP

  • Carefully review the Release Notes and the Macro Actions
    • Make sure you understand what the Macro will do.
    • You are responsible for running the Macro, not me. ??
      .
      Make These Changes to this Macro
  1. Assign a Trigger to this macro.
  2. Move this macro to a Macro Group that is only Active when you need this Macro.
  3. ENABLE this Macro, and the Macro Group it is in.
  4. Setup a Pages Document that uses a consistent Placeholder pattern that will be replaced
  5. Setup an Excel Document that has the data to Replace With
    • Each Replacement string in a separate cell in separate row all in the same column
    • Must have EMPTY Cell after the last Cell to be used/copied
      .
  • REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:
    (all shown in the magenta color)
    • SET Text To Find
      • This consists of a pattern with a number for each placeholder
      • For Example: [[PLACEHOLDER 1]]
      • Instead of the "1", use the KM Variable %Local__QN%

REQUIRES:

  1. KM 9.0+ (may work in KM 8.2+ in some cases)
  2. macOS 10.11.6 (El Capitan)+

TAGS: @Pages @Excel @FindReplace @Strings

USER SETTINGS:

  • Any Action in magenta color is designed to be changed by end-user

ACTION COLOR CODES

  • To facilitate the reading, customizing, and maintenance of this macro,
    key Actions are colored as follows:
  • GREEN -- Key Comments designed to highlight main sections of macro
  • MAGENTA -- Actions designed to be customized by user
  • YELLOW -- Primary Actions (usually the main purpose of the macro)
  • ORANGE -- Actions that permanently destroy Variables or Clipboards,
    OR IF/THEN and PAUSE Actions

USE AT YOUR OWN RISK

  • While I have given this a modest amount of testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
  • If you have any doubts or questions: