Applescript Find & Replace in Text Document from Excel Sheet

Question

Applescript Find & Replace in Text Document from Excel Sheet

I'm new to

Applescript

and am trying to create a script that can run through a text document (.html) and find & replace various phrases with a corresponding cell in an Excel document.

I will put placeholders in the text to be replaced as

B_1 (first value to replace), B_2 (second), B_3

etc...

They should be replaced in order using the cells

B1-B9

in the Excel document. The phrases in the Excel document will be changed each time, hence why it has to be dynamic to capture them rather than a standard find & replace script with static values.

I've been following this thread which all makes sense:https://discussions.apple.com/thread/7008048?start=0&tstart=0

However, I keep running into compile errors. Here is my current code:

tell application "Microsoft Excel" set colB_data to value of every cell of range "B1:B9" of worksheet 1 of document 1 end tell tell application "Sublime Text" tell text of "Users/maxquinn/Desktop/index.html" repeat with index from 1 to count colB_data replace ("B_" & index as text) using (item index of colB_data) options {starting at top:true, match words:true} end repeat end tell end tell

The first half works fine, but the second half gives me the error

"Expected end of line but found identifier."

and highlights the

'using'

in the

'replace' command (line 8)

.

Does anyone know why this might be, and are there any other glaring errors in the script?

Thanks!

Max

iMac

Posted on Jul 22, 2016 6:25 AM

Reply

Answer 1

Jul 28, 2016 4:30 AM in response to maxbrokenculture

Continue to use Hiroto's solution, as it is working for you.

I wanted to revise/finish what I started earlier. The updated script now expects name and replacement data from the Excel spreadsheet, as it will use these to construct a proper key/value pair dictionary. The script extracts the "B_nn" fields that it finds in the input HTML file — into a third list. It is this list that is used to match its items to keys in the dictionary, and sets the replacement string from the matched value. If there are redundancies in the HTML, these are handled accurately.

For use on OS X Yosemite (10.10.*) and later.

Code:

use scripting additions

use framework "Foundation"

use AppleScriptversion "2.4" -- Yosemite or later

set the_desktop to ((path to desktop) as text) as alias

set infile to (choose file of type {"public.html"})'s POSIX path

set outfile to (choose file namewith prompt "Output HTML File:" default name ¬

"out.html" default location the_desktop)'s POSIX path

(*

-- if this works, use it to set the Excel keys (xx) and values (yy)

tell application "Microsoft Excel"

tell active sheet's range "B1:B38"

set {xx, yy} to every cell's {name, value}

end tell

*)

-- test data can be removed with working Excel keys and values solution

set xx to {"B_1", "B_2", "B_3", "B_4", "B_5", "B_6", "B_7", "B_8", "B_9", "B_10", "B_11", "B_12", "B_13", "B_14", "B_15", "B_16", "B_17", "B_18", "B_19", "B_20", "B_21", "B_22", "B_23", "B_24", "B_25", "B_26", "B_27", "B_28", "B_29", "B_30"}

set yy to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4"}

-- get actual "B_nn" fields in the HTML to drive find/replace process

set hkeys to words of (do shell script "sed -En 's/^.+\\>(B_[0-9]+)\\<.+/\\1/p' " & infile)

set myHTML to read (POSIX file infile as alias)

-- make the AppleScript lists into Objective-C Arrays

-- me's is a shorthand for the customary current application's

set excel_keys to me's NSArray'sarrayWithArray:xx

set excel_values to me's NSArray'sarrayWithArray:yy

set html_key to me's NSArray'sarrayWithArray:hkeys

-- make an Objective-C dictionary from Excel keys and values range data

set kvmDict to me's NSDictionary'sdictionaryWithObjects:excel_valuesforKeys:excel_keys

-- Use the HTML "B_nn" fields as dictionary lookup keys for matching replacement values

-- Handles field redundancies in the HTML document

repeat with ndx from 0 to ((html_key's |count|()) - 1)

set findStr to (html_key'sobjectAtIndex:ndx) as text

set replaceStr to (kvmDict'sobjectForKey:findStr) as text

set srcString to (me's NSString'sstringWithString:myHTML)

set loc to (srcString'srangeOfString:findStr)

set myHTML to (srcString'sstringByReplacingCharactersInRange:locwithString:replaceStr) as text

-- log (findStr & tab & replaceStr) as text

end repeat

-- write out replacement HTML

set replaced to open for access (outfile as POSIX file) with write permission

set eofreplacedto 0

write (myHTML as text) toreplaced

close accessreplaced

return

Reply