maxbrokenculture

Q: 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

Close

Q: Applescript Find & Replace in Text Document from Excel Sheet

  • All replies
  • Helpful answers

Previous Page 2
  • by Hiroto,

    Hiroto Hiroto Jul 25, 2016 11:38 AM in response to maxbrokenculture
    Level 5 (7,286 points)
    Jul 25, 2016 11:38 AM in response to maxbrokenculture

    Hello

     

    I'm not sure but I'd guess something like the following code will get the name and value of every cell in the specified range.

     

     

    tell application "Microsoft Excel"
        tell document 1's worksheet 1's range "B1:B38"
            set {xx, yy} to every cell's {name, value}
        end tell
    end tell
    

     

     

    Or -

     

     

    tell application "Microsoft Excel"
        tell document 1's worksheet 1's range "B1:B38"
            tell every cell
                set xx to name
                set yy to value
            end tell
        end tell
    end tell
    

     

     

    * Both will set xx to list of name of cells and yy to list of value of cells in range "B1:B38", if I'm not mistaken.

     

     

    Regards,

    H

  • by VikingOSX,

    VikingOSX VikingOSX Jul 28, 2016 4:30 AM in response to maxbrokenculture
    Level 7 (20,819 points)
    Mac OS X
    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 AppleScript version "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 name with 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

    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's arrayWithArray:xx

    set excel_values to me's NSArray's arrayWithArray:yy

    set html_key to me's NSArray's arrayWithArray:hkeys

     

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

    set kvmDict to me's NSDictionary's dictionaryWithObjects:excel_values forKeys: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's objectAtIndex:ndx) as text

           set replaceStr to (kvmDict's objectForKey:findStr) as text

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

           set loc to (srcString's rangeOfString:findStr)

           set myHTML to (srcString's stringByReplacingCharactersInRange:loc withString: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 eof replaced to 0

    write (myHTML as text) to replaced

    close access replaced

    return

Previous Page 2