Automating Microsoft Project with Ruby

A reader recently asked for a discussion of automating Microsoft Project with Ruby. Let's dive right into some examples...

Create an instance of the MSProject Application class...

require 'win32ole'
app = WIN32OLE.new("MSProject.Application")

Note that Project (like Outlook and PowerPoint) is a single-instance application. This means that if an instance of Project is already running, that instance will be returned, even though you called the WIN32OLE.new method. If Project is not currently running, a new instance will be launched.

To show or hide the Application window, set the Visible property...

app.Visible = true

The application object contains a Projects collection of all open projects. To create a new project, call its Add method...

project = app.Projects.Add

Oddly, the Projects collection doesn't have an Open method. To open an existing project, call the application object's FileOpen method...

app.FileOpen('c:\temp\project1.mpp')

...then reference the active project...

project = app.ActiveProject

Use the Title method to set or get the Project's title...

project.Title = 'My Project Title'

You can iterate over the Tasks collection, and call its Add method to create a new Task...

tasks = project.Tasks
(1..100).each do |i|
    task = tasks.Add
    task.Type = 1
    task.Name = "Task #{i.to_s}"
    task.Notes = "Your comments here..."
    task.Text30 = "My Specific Text"
end

A Task object has a variety of properties/methods (some shown above), and includes a Resources collection, over which you can iterate...

project.Tasks.each do |task|
    puts task.Name
    puts task.Resources.Count
    task.Resources.each do |resource|
        puts resource.Name
    end
end

Save the active project by calling the Application object's FileSaveAs or FileSave methods...

app.FileSaveAs('c:\temp\project3.mpp')
app.FileSave

To exit, call the Application's Quit method...

app.Quit

As mentioned previously, there are various means for exploring the Project object model, and I encourage you to pursue these.

As always, post a comment or send me email if you have questions, comments, or a request for future topics.

Thanks for stopping by!

Ruby & Excel: The InputBox Hack

A ruby-talk (comp.lang.ruby) reader asked how to get user input on Windows without resorting to console input.

While you can use Windows API calls to create a Yes/No message box, there doesn't appear to be a simple API means for creating a dialog for accepting user string input.

As others on the thread have mentioned, you can use one of the GUI toolkits (ie, wxRuby) and, if you have other GUI needs, this is the best option. If, however, the only GUI need you have is for a single input dialog, you could use the InputBox function from Excel, via the win32ole library (assuming Excel is installed):

require 'win32ole'

def get_input(prompt='', title='')
    excel = WIN32OLE.new('Excel.Application')
    response = excel.InputBox(prompt, title)
    excel.Quit
    excel = nil
    return response
end

response = get_input('My Prompt', 'My Title') 

Granted, this is somewhat of a hack if you're loading Excel into memory merely to display an input dialog. On the other hand, it may be simpler than adding an entire GUI library to your project, especially if you'll eventually be wrapping up all those dependencies into a distributed package, a la rubyscript2exe.

Ruby & Excel: Inserting and Deleting Rows and Columns

A reader has asked how to insert a row into a worksheet.

To insert a row, call the Insert method on the Row object where you wish to insert a new row. So this...

worksheet.Rows(2).Insert

...will move row 2 down one row and insert a new row above it.

You can insert multiple rows by passing the Insert method a colon-separated string, so...

worksheet.Rows('2:5').Insert

...inserts 4 new rows and moves rows 2 to 5 down. Note that this is a string and must therefore be quoted.

Similarly, to insert a new column, call the Insert method on the Column object where you wish to insert a new column. So either of these...

worksheet.Columns(2).Insert
worksheet.Columns('B').Insert

...will move column B/2 over one column (to the right) and insert a new column B.

This...

worksheet.Columns('2:5').Insert
worksheet.Columns('B:E').Insert

...inserts 4 new columns and moves columns 2 to 5 to the right. Note that this is a string and must therefore be quoted.

Deleting Rows and Columns works the same way, using the Delete method...

worksheet.Rows(2).Delete
worksheet.Rows('2:5').Delete
worksheet.Columns(2).Delete
worksheet.Columns('B').Delete
worksheet.Columns('C:E').Delete

Keep in mind that after inserting or deleting rows, your row (or column) indexes change -- when you delete Rows(2), Rows(3) becomes Rows(2), etc.

That's all for now. Got a question or suggestion? Post a comment or send me email.

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 Excel with Ruby: Defining a Range and Inserting Data

As I previously mentioned, calling the Value method on an Excel Range object returns an array of arrays of values. Here's a quick way to get a 2-dimensional array of all values from a worksheet...

data = worksheet.UsedRange.Value

But, as I failed to mention in that article, you can also insert a 2-dimensional array into a Range of cells:

worksheet.Range("A2:G7").Value = data

If you have a lot of data to insert, this will be much faster than inputting the data cell-by-cell. But it is important that the range of cells be the same size (number of rows, number of columns) as your 2d array.

Your Array: Number of Rows

Assuming you have data, a 2-dimensional array, you can determine the number of rows by calling data.size:

rows = data.size

Your Array: Number of Columns

You can determine the number of columns by calling size on one of the data array's rows:

cols = data[0].size

Defining Your Worksheet Range

But to use the Range method, you need to define the column letters (ie, "A2:G7"). You probably already know that you want your starting point to be cell A1 or A2. But how do you programmatically determine the final column letter for an array that is 104 columns wide?

Each cell (or row, or column) object has an Address method that returns its location in letter-number format, so...

worksheet.Cells(5, 3).Address

...returns "$C$5", and we can use that string in our upcoming Range definition. So if you have a range that is 30 columns wide by 100 rows in length, which we want to insert into a Range, starting at cell A1:

first = worksheet.Cells(1,1).Address        # => "$A$1"
last = worksheet.Cells(100,30).Address      # => "$AD$100"
worksheet.Range("#{first}:#{last}").Value = data

If your worksheet has a header row and you will be inserting your data after that, bump up the row value by 1:

first = worksheet.Cells(2,1).Address        # => "$A$2"
last = worksheet.Cells(101,30).Address      # => "$AD$101"
worksheet.Range("#{first}:#{last}").Value = data

I hope that some of you find this useful. My thanks goes to Charlie for suggesting the topic.

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.