Monday, November 12, 2007

Find & Replace with MS Word

While browsing through some DZone Ruby Snippets, I came across this nice little example of using Ruby to automate Find & Replace in Microsoft Word, compliments of Tim Morgan.

You could use such a snippet as part of a mail-merge type solution. To use it, create a form letter in a Word document, with bracketed placeholders where the actual values are to be inserted. Example:


Dear [name],

On [date] account [account_number] was charged a [amount] fee.

Let's take a walk through the code...

Load the win32ole library:

require 'win32ole'

Launch Microsoft Word:

word = WIN32OLE.new('Word.Application')

Open the template document:

doc = word.Documents.Open('c:\file_to_open.doc')

Iterate over a hash containing keys and values. Each key is the placeholder code and the corresponding value is the text to insert:

{
'name' => 'Tim Morgan',
'date' => Date.today.strftime('%B %d, %Y'),
'account_number' => '123456',
'amount' => '$45.76'
}.each do |key, value|

Start the search at the beginning of the document:

word.Selection.HomeKey(unit=6)

Grab a reference to the Find object:

find = word.Selection.Find

Set the Find object's text string (the hash key) to locate, surrounded by brackets:

find.Text = "[#{key}]"

For each occurrence of the string found (via the Find object's Execute method), insert the replacement text (the hash value):

while word.Selection.Find.Execute
word.Selection.TypeText(text=value)
end

Save the document with a new name:

doc.SaveAs('c:\output_file.doc')

Close the document:

doc.Close

The example above shows the hash hard-coded in the script, but you could build on this to load an array of hashes from a text or YAML file, Excel worksheet, or database table.

My thanks to Tim Morgan, who posted the original snippet on Dzone, and to you, for stopping by to read it!

8 comments:

Rainer said...

Hello David,

thanks for the example. It was very instructive for me. However, there was a tiny error with it. You need to put an end to the iterator loop that replaces the values for the keys.

Kind regards,

Rainer

Anonymous said...

I'm glad to see your back in action David!

Anonymous said...

Can you point me towards how I would substitute the text on a master page then generate new pages for very large mailings?

David Mullet said...

Thanks, Rainer & Paul!

@Gerard:

"...then generate new pages..."

The doc.SaveAs method saves the modified document to a new file. Feel free to email me directly if that doesn't answer your question.

David

GorgeousSoul said...

Great post. Very helpful.

I'd like to use either bookmarks or document properties, though.

document.bookmarks.item("bkCompany").range.text = Company.name

document.CustomDocumentProperties("dpCompany").Value = Company.name


That way if I don't have a value to substitute, I won't have a [replace this] placeholder in my finished document.

I'm having a problem with that though... if there isn't a value, the code stops and gives an error.

Any advice on whether I should (a) find a why to ignore errors for that section of code or (b) try something else?

Thanks!

David Mullet said...

Denise-

I'm not certain if I understand your problem. You might try coverting Company.name to a string...

document.bookmarks.item("bkCompany").range.text = Company.name.to_s

...if Company.name returns nil. nil is an object whose to_s method returns an empty string.

Alternatively, you could perhaps wrap the code in a begin... rescue.. end block...

begin
document.bookmarks.item("bkCompany").range.text = Company.name
rescue
end
...to ignore any error occurring between the begin and rescua statements.

Hope that helps.

David

Unknown said...

Hi David,

This is a wonderful article. It helped me with writing my first application using OLE (I know about ole_methods, but I would still need to use an application to explore OLE objects, and none of those work with my screen reader).

Thanks for writing it!

Sudeep said...

Thank you very much. This article helped me to do my task..