Saving Microsoft Office Documents as PDFs

A recent discussion in the Ruby Forum reminded me that it is possible with Microsoft Office 2007 applications to save a document in Adobe PDF format.

In the Microsoft Word object model, you can call the Document object's SaveAs() method, passing it a filename, and the document will be saved in the default format.

document.SaveAs('c:\temp\MyDocument.doc')

But the SaveAs() method accepts an optional second parameter, an integer that specifies the file format. In the Word object model, the PDF format is represented by the value 17. So, where document represents your Document object, you can do the following:

document.SaveAs('c:\temp\MyDocument.pdf', 17)

In Microsoft Excel, the Workbook object's SaveAs() method accepts the value 57 to specify PDF format:

workbook.SaveAs('c:\temp\MyWorkbook.pdf', 57)

And in Microsoft PowerPoint, the magic number is 32:

presentation.SaveAs('c:\temp\MyPresentation.pdf', 32)

You can find enumerations for file types over at MSDN:

• Microsoft Word
  
• Microsoft Excel
  
• Microsoft PowerPoint
  

A tip of the hat to Joe Peck for mentioning this in the Ruby Forum.

Let me know if you have any questions or comments, and thanks for stopping by!

Connecting to One of Many Open Documents

If you've hung around here for a little while, then you probably already know that you can use the WIN32OLE.connect() method to connect to a running instance of applications like Microsoft Word. Just pass the method the ProgID of the Application object:

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

That works great if you have just one instance of the application running, with one or multiple documents open. But suppose you have multiple instances of the application running? How can you be sure that you connect to the instance with Document B, and not the instance with Document A?

Well, assuming that Document B has been saved and you know the full filename, then you can connect to the Document object, rather than to the Application object. And you do this by passing the WIN32OLE.connect() method the full filename:

document = WIN32OLE.connect('C:\Path To File\DocumentB.doc')

This returns an instance of the Word.Document object. You can then grab
an instance of that Document's Application object:

word = document.Application

This works with other multiple-instance applications, such as Excel. The following code connects to a specific instance of an Excel.Workbook object:

workbook = WIN32OLE.connect('C:\Path To File\WorkbookB.xls')
xl = workbook.Application

By the way, some applications, such as PowerPoint, are single-instance applications, so there will never be multiple instances of the application running, avoiding the need to use the process outlined above.

There you have it. Please let me know if you have specific questions or suggestions for future articles.

Thanks for stopping by!

Ruby & Word: Inserting Pictures Into Documents

In an earlier article, I explained how to insert an image into a range of cells in an Excel worksheet. Today we'll look at how to insert an image into a Word document.

Setting the Scene

Feel free to play along at home: Imagine that you have a Word document already open...

require 'win32ole'
word = WIN32OLE.connect('Word.Application')
doc = word.ActiveDocument

...and you want to insert an image from your PC...

image = 'C:\Pictures\Picture1.jpg'

...into a given Range object in the document:

range = doc.Sentences(2)

The InlineShapes Collection

The InlineShapes object represents a collection of pictures, OLE objects, and ActiveX controls contained within a given Document or Range object.

The AddPicture() Method

To insert an image into a document or range, we call the AddPicture() method on the InlineShapes collection. This method accepts up to four parameters:

* FileName (required) - The full path and filename of the image to insert.

* LinkToFile (optional) - If true the inserted picture will be linked to the file from which it was created. You'll usually set this to false to make the picture an independent copy of the file. The default value is false.

* SaveWithDocument (optional) - Set to true to save the linked picture with the document. The default is false. This value will be ignored unless LinkToFile is set to true.

* Range (optional) - A Range object representing the position in which to insert the picture.

The AddPicture() method returns a reference to the newly created InlineShape object that is your picture.

So, to insert an image at the start of the range that we defined earlier, we could call the method on our range object...

range = doc.Sentences(2)
pic = range.InlineShapes.AddPicture( { 'FileName' => image } )

...or we could call it on the Document object and pass the Range object as a parameter:

range = doc.Sentences(2)
pic = doc.InlineShapes.AddPicture( { 'FileName' => image, 
                                     'Range' => range } )

When providing all four parameters, the syntax looks like this:

    
pic = doc.InlineShapes.AddPicture( { 'FileName' => image, 
                                     'LinkToFile' => false, 
                                     'SaveWithDocument' => false, 
                                     'Range' => range } )

If your only parameter is the FileName, you could bypass the hash format and simply pass the string to the method, like this...

pic = range.InlineShapes.AddPicture( 'C:\Pictures\Picture1.jpg' )

To insert the image at the currently selected position, use the word.Selection.Range object:

pic = word.Selection.Range.InlineShapes.AddPicture( image )
pic = doc.InlineShapes.AddPicture( { 'FileName' => image, 
                                     'Range' => word.Selection.Range } )

And that's our show for today. Thanks for stopping by!

Ruby & Word: Inserting Tables

Microsoft Word is great for text documents. Microsoft Excel is great for tables of data. But, sometimes, you need to get your chocolate in your peanut butter. In other words, you may occasionally need to include a table in a Word document. Let's walk through how to do that.

Setting the Stage

To borrow an example from my upcoming book, let's say that you want to insert a table that contains a list of all movies starring Spencer Tracy and Katharine Hepburn, including the year each movie was released, the title, and the director. Your 2-dimensional films array might look like this:

films = []
films << ["1942", "Woman of the Year", "George Stevens"]
films << ["1942", "Keeper of the Flame", "George Cukor"]
films << ["1945", "Without Love", "Harold S. Bucquet"]
films << ["1947", "The Sea of Grass", "Elia Kazan"]
films << ["1948", "State of the Union", "Frank Capra"]
films << ["1949", "Adam's Rib", "George Cukor"]
films << ["1952", "Pat and Mike", "George Cukor"]
films << ["1957", "Desk Set", "Walter Lang"]
films << ["1967", "Guess Who's Coming to Dinner", "Stanley Kramer"]

Let's connect to a running instance of Word and use the currently selected document:

require 'win32ole'
word = WIN32OLE.connect('Word.Application')
doc = word.ActiveDocument

Moving to the End of the Document

First, let's move to the end of the document, where we'll add our new table. We do this by calling the Selection.EndKey() method, which moves the selection to the end of a word, line, or document. We'll pass this method a value of 6, which specifies that we want to move to the end of the document:

word.Selection.EndKey(6)

Adding a New Table

The Tables collection in Word's object model represents all the Table objects in a selection, range, or document. To add a new Table to a document, call the Document object's Tables.Add() method and pass it three parameters:

* The range object representing where the table is to be inserted
* The number of rows for the new table
* The number of columns for the new table

Now let's add a new table with one row and three columns (we'll add more rows later):

table = doc.Tables.Add(word.Selection.Range, 1, 3)

The Tables.Add() method returns a reference to the newly created table, which we have assigned to the cleverly named variable table.

Inserting Text into Table Cells

You can reference a single cell in a table by calling the Cell() method and passing it the row and column numbers (NOTE: the first row or column is represented by 1, not 0). Once you have your cell, you can set the text via its Range.Text property; so we add the header text as follows:

table.Cell(1, 1).Range.Text = 'Year'
table.Cell(1, 2).Range.Text = 'Film Title'
table.Cell(1, 3).Range.Text = 'Director'

Adding Rows

To add a row to your table, simply call the Table object's Rows.Add() method. Now that we've added the header text, let's iterate over our films array and add a new row to the table for each film and insert the text:

films.each_with_index do |film, r|
    table.Rows.Add()
    film.each_with_index do |field, c|
        table.Cell(r + 2, c + 1).Range.Text = field
    end
end

There you have it. Thanks for stopping by!

Ruby & Word: Counting Words and Pages

Someone recently asked how to get a count of the number of words and pages in a Microsoft Word document. This is done by calling the ComputeStatistics() method on a Range or Document object.

As an example (play along at home), let's imagine that you have a Word document open. Your first step is to use the win32ole library's connect() method to connect to the existing instance of Word:

require 'win32ole'
word = WIN32OLE.connect('Word.Application')

You pass the ComputeStatistics() method an integer representing the type of statistic that you want to calculate. In other words, "What do you want to count?" So let's take a moment to define constants for those values:

WdStatisticCharacters = 3
WdStatisticCharactersWithSpaces = 5
WdStatisticWords = 0
WdStatisticLines = 1
WdStatisticParagraphs = 4
WdStatisticPages = 2

You can call the ComputeStatistics() method on a Document object...

doc = word.ActiveDocument
word_count = doc.ComputeStatistics(WdStatisticWords)
page_count = doc.ComputeStatistics(WdStatisticPages)

...or on a Range object...

paragraph = doc.Paragraphs(27)
word_count = paragraph.Range.ComputeStatistics(WdStatisticWords)
char_count = paragraph.Range.ComputeStatistics(WdStatisticCharacters)

When called on a Document object, the method accepts an optional second parameter, IncludeFootnotesAndEndnotes, a boolean which (obviously) specifies if the calculation should include footnotes and endnotes:

word_count = doc.ComputeStatistics(WdStatisticWords, true)

The IncludeFootnotesAndEndnotes parameter defaults to false.

Official details on the ComputeStatistics() method are available from MSDN here.

That's all for now. Thanks for stopping by!

Ruby & Word: Creating and Applying Styles

Microsoft Word uses the Styles model to apply a set of pre-defined formatting to text. Styles can also serve a second purpose, to tag sections of the document as normal, title, headings and such. You can then, for example, create a Table of Contents in Word based on the text that is formatted with the Heading styles.

Naturally, you can do all this with code (otherwise, I wouldn't be wasting your time here). Over the next few minutes, we'll walk through the process of creating a new Style, setting its properties, and then applying that style to text.

The Style object represents a single built-in or user-defined Word style. The Styles collection contains all the Style objects within a document. To reference the Styles collection, simply call the Styles method on the document:

doc = word.ActiveDocument
styles = doc.Styles

To create a new Style, we call the Add() method on the Styles object and pass it a hash defining the name and the type of the new Style. The following code creates a new Paragraph style named 'Code':

code_style = doc.Styles.Add({'Name' => 'Code', 'Type' => 1})

The Type property defines what StyleType your new Style is based on. Possible values are:

WdStyleTypeParagraph = 1
wdStyleTypeCharacter = 2
WdStyleTypeTable     = 3
WdStyleTypeList      = 4

The default is WdStyleTypeParagraph.

The Add() method returns a reference to the newly-created Style object. Now that you have your new Style object, you can customize it through various properties that you can set. As a starting point, you may want to base your new style on another style by setting the BaseStyle property:

code_style.BaseStyle = 'Normal'

Font properties can be defined...

code_style.Font.Name = 'Consolas'
code_style.Font.Size = 12
code_style.Font.Bold = false

...as well as paragraph spacing and background colors:

code_style.NoSpaceBetweenParagraphsOfSameStyle = true
code_style.Shading.BackgroundPatternColor = 15132390

Note that not all properties will apply to all style types.

Now that you've created your own Style, you might want to automatically apply it to some existing text. The following code iterates over each paragraph in the document (doc variable). For each paragraph that uses the 'Preformatted Text' style, the new 'Code' style is applied instead:

doc.Paragraphs.each do |paragraph|
    if paragraph.Style.NameLocal == 'Preformatted Text'
        paragraph.Style = 'Code'
    end
end

There you have it. If you'd like to learn more here about Styles, or anything else related to automating Word with Ruby, please let me know.

Thanks for stopping by!

Automating Word with Ruby: Adding Bookmarks

Someone recently asked a question about using Ruby to add bookmarks to a Microsoft Word document. Here's a brief, but hopefully helpful, explanation...

Word's Document object includes a Bookmarks collection. To get a reference to this collection, call the Bookmarks() method on the Document object:

require 'win32ole'
word = WIN32OLE.connect('Word.Application')
doc = word.ActiveDocument
bookmarks = doc.Bookmarks()

To create a new bookmark, call the Add() method on the Bookmarks collection, passing it (1) a one-word name for the new bookmark, and (2) the range to be bookmarked. The following line of code adds a new bookmark, cleverly named 'Bookmark1', for the currently selected text:

bookmarks.Add('Bookmark1', word.Selection)

The Bookmarks collection includes (among others) a Count() method for getting the number of items in the collection, and an Exists() method for determining if a bookmark with that name already exists. So the following code prints the number of bookmarks, then adds a bookmark if it doesn't already exist, then prints the number of bookmarks again:

puts(doc.Bookmarks.Count)
if not doc.Bookmarks.Exists('Bookmark1')
    doc.Bookmarks.Add('Bookmark1', word.Selection)
end
puts(doc.Bookmarks.Count)

To get a reference to an individual Bookmark, call the Bookmarks() method and pass it the name of the bookmark:

bookmark1 = doc.Bookmarks('Bookmark1')

To delete an individual Bookmark, call its Delete() method:

doc.Bookmarks('Bookmark1').Delete

There you have it. Let me know if you have questions or comments. And, of course, there'll be more on this topic (and many others) in the book I'm currently working on.

Thanks for stopping by!

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!

Automating Word with Ruby: Formatting Text

A reader asks "Can you tell me how to format text in Word using Ruby? I'm most interested in controlling justification, indenting paragraphs, changing fonts, using italics, bold, and underlining, etc."

This is the latest in a series of articles about Automating Word with Ruby.

There's a variety of formatting that you can apply to Range objects in Word. Let's look at some of the most common...

Fonts

To change the font of a Range object, set its Font.Name value to a valid font name:

document.Words(1).Font.Name = 'Verdana'

To change the font size of a Range object, set its Font.Size value:

document.Words(1).Font.Size = 18

To set Bold, Italic, and Underline properties of a Range object, supply a boolean value:

document.Sentences(1).Font.Bold = true
document.Sentences(1).Font.Italic = true
document.Sentences(1).Font.Underline = true

Indenting

To indent a paragraph, call its Indent method. For example, to indent all paragraphs in the document:

document.Paragraphs.Indent

To un-indent a paragraph, call its Outdent method. For example, to un-indent only the first paragraph:

document.Paragraphs(1).Outdent

Alignment

To align a paragraph, set its Alignment value to one of the following values:

Left = 0
Center = 1
Right = 2
Justify = 3

To center-align the second paragraph, for example:

document.Paragraphs(2).Alignment = 1

That's all for now, and I hope you find it helpful. As always, post a comment here or send me an email to request future topics.

Thanks for stopping by!

Automating Word with Ruby: The Range Object

In a previous installment, we looked at the Word Document object. Now we're going to work with the contents of the Document object, via the Range object.

First, let's create an instance of the Word Application object and add a new Document object:

require 'win32ole'
word = WIN32OLE.new('Word.Application')
word.Visible = true
document = word.Documents.Add

The Range Object

As the name implies, a Range object represents a range within the Document, with a defined starting point and end point. You can define a Range by calling the Document object's Range method and passing it two arguments. The starting point is the character index before the start of the Range, and the end point is the character index at the end of the Range. So, to get a Range containing the first five characters of a document...

first5characters = document.Range(0, 5)

To get a Range containing characters 6 through 15...

next10characters = document.Range(5, 15)

Keep in mind that the Range method returns a Range object. To get the text for the Range object, call its Text method:

txt = document.Range(0, 5).Text

The Word document object's Characters, Words, and Sentences methods are shortcuts that return Range objects.

A range may represent the entire document...

document.Range

...a single word or sentence...

word5 = document.Words(5)
fifth_sentence = document.Sentences(5)

...or a single character...

first_character = document.Characters(1)
first_character = document.Characters.first
first_character = document.Range(0, 1)

To get (or set) the text of a Range, call its Text method...

fifth_sentence_text = document.Sentences(5).text
document.Sentences(5).text = "New text for sentence five."
all_text = document.Range.Text

To insert text into a Document, define a single-point Range and call its Text method. For example, to insert text at the start of the document...

document.Range(0, 0).Text = "New text at start of document. "

To be continued...

Automating Word with Ruby: The Document Object

In a previous episode, we discussed the Word application object and looked at some of its properties and methods. Now we'll take a look at the Document object, which (of course) represents a Word document.

Creating and Opening Documents

The application.Documents method returns a collection representing the currently open documents. To create a new document, call this collection's Add method:

word = WIN32OLE.new('Word.Application')
word.Visible = true
document = word.Documents.Add

You can optionally pass this method the name of a template to use for the new document:

document = word.Documents.Add('Normal')
document = word.Documents.Add('Normal.dot')

To open an existing Word document file, call this collection's Open method, passing it the file name:

word = WIN32OLE.new('Word.Application')
word.Visible = true
document = word.Documents.Open('c:\WordDocs\MyWordFile.doc')

Calling the Documents collection's Count method returns the number of documents in the collection, and you can iterate over this collection:

puts word.Documents.Count
for document in word.Documents
    # ...code...
end

To reference the currently active document, call the application object's ActiveDocument method:

word = WIN32OLE.connect('Word.Application')
document = word.ActiveDocument

The document object contains numerous collections, including:

document.Paragraphs
document.Sentences
document.Words

Need to know the name and/or path of the document? There are three read-only methods you may find helpful:

document.FullName   # returns the full path and filename
document.Name       # returns just the filename, without the path
document.Path       # returns the full path, without the filename

Printing, Saving, and Closing Your Document

To print a document, call its PrintOut method:

document.PrintOut

To save a document, call its Save or SaveAs methods:

document.SaveAs('c:\temp\MyDocument.doc')
document.Save

You can pass the SaveAs method an optional second argument to specify the file format. A few examples:

document.SaveAs('c:\temp\MyDocument.doc', 0)    # Word document (default)
document.SaveAs('c:\temp\MyDocument.dot', 1)    # Word template
document.SaveAs('c:\temp\MyDocument.txt', 2)    # Text
document.SaveAs('c:\temp\MyDocument.rtf', 6)    # Rich Text Format (RTF)

To close a document, call its Close method:

document.Close

That about wraps up our show for today. Coming up, we'll start working with document contents. As always, let me know if there is anything specific that you would like to see discussed here.

Thanks for stopping by!

Automating Word with Ruby: The Application Object

Automating Microsoft Word can involve hundreds of objects, each with its own properties and methods. You can use the ole_methods method, or Word's built-in Object Browser, to browse the objects, properties, and methods. For further details, see this article.

If you've read some of my articles about automating Excel with Ruby, you will see similarities between the two object models.

Let's start by looking at Microsoft Word's top-level Application object.

You'll use the win32ole library to create a new instance of the Word application object:

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

...or to connect to an existing instance of the Word application object:

word = WIN32OLE.connect('Word.Application')
document = word.ActiveDocument

I often use the connect method for ad hoc scripts to perform a series of actions on a Word document (or Excel workbook) that I already have open.

Note that a new instance of Word will be not visible by default. To show it, set the application object's Visible property to true:

word.Visible = true

You may want to first hide Word until your data input and formatting process is complete, then make it visible. This may speed things up, and prevents the user from interfering with your program (and vice versa).

You'll spend most of your time working with Document objects, but first let's look at some of the properties and methods for the Application object.

Call the Version method to determine the version of Word, which is returned as a decimal string (ie, "11.0"):

if word.Version.to_i => 11
    # do something
else
    # do something else
end

You can set the PrintPreview property to place Word in print preview mode:

word.PrintPreview = true
word.PrintPreview = false   # return to previous view mode

The System object includes a number of (read-only) properties about the system that Word is running on. Most of these will be of no use whatsoever to you, but a few may prove helpful:

word.System.OperatingSystem         # => "Windows NT"
word.System.HorizontalResolution    # => 1440
word.System.VerticalResolution      # => 900
word.System.LanguageDesignation     # => "English (U.S.)"

The FontNames, PortraitFontNames, and LandscapeFontNames methods return collections of font names:

for font in FontNames
    puts font
end

The Tasks method returns a collection of Task objects, representing tasks being run on the PC. Most of these tasks will be processes that you have no interest in. But you can, for example, use this to determine quickly if a particular application is currently running and, if so, address that application's window:

if word.Tasks.Exists('Firefox')
    word.Tasks('Firefox').Activate
    word.Tasks('Firefox').WindowState = 1   # maximize
    word.Tasks('Firefox').WindowState = 2   # minimize
    word.Tasks('Firefox').WindowState = 0   # restore to normal
end

Calling ole_methods on one of these Task objects reveals what we may do with it:

irb(main):004:0> word.Tasks('Firefox').ole_methods

=> [QueryInterface, AddRef, Release, GetTypeInfoCount, 
GetTypeInfo, GetIDsOfNames, Invoke, Application, Creator, 
Parent, Name, Left, Left, Top, Top, Width, Width, Height, 
Height, WindowState, WindowState, Visible, Visible, 
Activate, Close, Move, Resize, SendWindowMessage, 
GetTypeInfoCount, GetTypeInfo, GetIDsOfNames, Invoke]

So we see that we can, for example, hide, unhide, move, resize, and close other application windows through the Task object:

word.Tasks('Firefox').Visible = false
word.Tasks('Firefox').Visible = true
word.Tasks('Firefox').Top = 100
word.Tasks('Firefox').Left = 300
word.Tasks('Firefox').Height = 500
word.Tasks('Firefox').Width = 500
word.Tasks('Firefox').Close

You should, naturally, use the Tasks collection with care, as you could wreak havoc otherwise.

To quit Word, call the Quit method:

word.Quit       #   Do not save changes
word.Quit(0)    #   Do not save changes
word.Quit(-1)   #   Save changes
word.Quit(-2)   #   Prompt user to save changes

That's all for now. Next up, we'll look at working with the Document object. As always, let me know if there are any specific topics you would like to see discussed here.