Q: Replace Word in Text Doc with Cell in Excel Doc
I have a Text Document with 80 variables throughout the text, each of which I want to easily replace with the information I have in any one, of the several hundred, Excel documents on my Mac.
I would like to open a my text document and have Automator replace A_1 with whatever text is in the Cell A1 of an also open Excel Doc, and then automatically move down to A_2, A_3, and so on.
I have scoured the 'net and tried a ton of methods including the recording tool but I’m not having much success.
I would like to use Automator for this, but if you have a Mac program you suggest instead, I would love your advice on that too.
Thanks so much (:
MacBook, OS X Yosemite (10.10.3)
Posted on Apr 22, 2015 2:45 PM
I have 2 rows in the Excel doc, A1:39 and B1:39.
Is it possible to make the same program replace B_1, B_2 with corresponding cells, as well?
Sure. That's pretty easy, and there are a couple of ways of doing it. The simplest may be to implement two runs - extract the two columns of data from Excel, and run two sets of replace commands - one for the A column and one for the B, like:
tell application "Microsoft Excel"
set colA_data to value of every cell of range "A1:A39" of worksheet 1 of document 1
set colB_data to value of every cell of range "B1:B39" of worksheet 1 of document 1
end tell
tell application "TextWrangler"
tell text of text document 1
repeat with index from 1 to count colA_data
replace ("A_" & index as text) using (item index of colA_data) options {starting at top:true, match words:true}
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
Here you can see I copy out two ranges from Excel into colA_data and colB_data. Then I run two replacements inside the loop, one for each of the columns.
This is a little fragile in that it assumes the two columns are equal length (i.e. both have 39 rows). If they don't you may need to change the loop, or change the loop to the longest column.
This is a slightly more flexible model which would allow you to select a two-dimensional range (e.g. A1:B39), but that gets more complex when working out the column split, so for this I'd prefer to keep track of the columns separately.
Posted on Apr 23, 2015 11:57 AM
