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.

Ruby & WIN32OLE: Inspecting Objects

So, you know how to create the major WIN32OLE objects that you need, such as Excel's application, workbook, and worksheet objects. But what can you really do with them? Ruby allows you to use Object.methods to get a list of methods that can be called on the Object:

"Baseball".methods

...returns the following array of String methods...

["methods", "instance_eval", "%", "rindex", "map", "<<", "split", "any?",
"dup", "sort", "strip", "size", "instance_variables", "downcase", "min", "gsub!",
"count", "include?", "succ!", "instance_of?", "extend", "downcase!", "intern",
...
"object_id", "length", "entries", "chomp", "=~", "require", "public_methods",
"upcase", "sub!", "squeeze", "__send__", "upcase!", "crypt", "delete!", "equal?",
"freeze", "detect", "zip", "[]", "lstrip!", "center", "[]=", "to_f"]

For easier browsing, you can sort this array by calling the sort method...

"Baseball".methods.sort

This is handy when you need to call a Ruby method that you don't recall the exact name of. Scan the list of methods for the object you're working with.

Call this on an object created through win32ole, and you see this...

excel = WIN32OLE.new('Excel.Application')
excel.methods
["methods", "instance_eval", "dup", "_setproperty", "instance_variables",
"instance_of?", "extend", "eql?", "ole_func_methods", "each", "hash", "id",
"singleton_methods", "setproperty", "taint", "frozen?", "instance_variable_get",
...
"ole_get_methods", "nil?", "untaint", "gem", "send", "ole_obj_help", "display",
"inspect", "clone", "=~", "object_id", "_getproperty", "require",
"public_methods", "__send__", "equal?", "freeze", "ole_put_methods", "[]", "[]="]

Looks like the fairly generic Object methods, doesn't it? What would be helpful, in regards to OLE/COM automation, would be a method that returns the OLE methods of the object. Fortunately, the win32ole library provides just that in Object.ole_methods. This method returns an array of WIN32OLE objects. To get a list of methods for the Excel application object...

excel = WIN32OLE.new('Excel.Application')
excel.ole_methods
=> [QueryInterface, AddRef, Release, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames,
Invoke, Application, Creator, Parent, ActiveCell, ActiveChart, ActiveDialog,
ActiveMenuBar, ActivePrinter, ActivePrinter, ActiveSheet, ActiveWindow,
...
CalculateFull, FindFile, CalculationVersion, ShowWindowsInTaskbar, ShowW
indowsInTaskbar, FeatureInstall, FeatureInstall, GetTypeInfoCount, GetTypeInfo,
GetIDsOfNames, Invoke]

You can't sort this array of WIN32OLE objects as-is, but you can convert each element to a string and then sort the array of strings...

excel.ole_methods.collect!{ |e| e.to_s }.sort
["ActivateMicrosoftApp", "ActiveCell", "ActiveChart", "ActiveDialog",
"ActiveMenuBar", "ActivePrinter", "ActivePrinter", "ActiveSheet", "ActiveWindow",
"ActiveWorkbook", "AddChartAutoFormat", "AddCustomList", "AddIns", "AddRef",
...
"Width", "Width", "WindowState", "WindowState", "Windows", "WindowsForPens",
"Workbooks", "WorksheetFunction", "Worksheets", "_Default", "_Evaluate",
"_FindFile", "_Run2", "_WSFunction", "_Wait"]

Note that a similar method exists, ole_get_methods, which does not return the exact same results as ole_methods. The WIN32OLE documentation does not make clear (to me) the difference between these two methods. If ole_methods does not provide what you are looking for, try ole_get_methods.

A graphical alternative to the above Ruby methods is to use an OLE Object Browser, such as the one included with Excel's Visual Basic Editor. To launch it from Excel or Word, select Tools => Macro => Visual Basic Editor. From the VB Editor, select View => Object Browser. Similar third-party and open source OLE browsers are also available.

Okay, now you've got a list of WIN32OLE methods reference you can do something with. Browse through this list of meaningful method names and you'll be on the road to gaining some valuable insight into all that you can do with a given WIN32OLE object. Now that you have a method name, go to Google and search for it (ie, excel DisplayInfoWindow) and you'll find details on the method/property and how you may use it.

Automating Excel with Ruby: Rows, Columns, Ranges, and Cells

An Excel worksheet object contains rows, columns, ranges, and cells. Let's look at some of the most common methods for working with these objects.

The Cells method of the Worksheet object returns a single cell. It is called with the row number and column number...

cell = worksheet.Cells(2, 5)

The Range method (of either the Application or Worksheet object) returns a collection of cells. It is called with a string value of First Cell and Last Cell, separate by a colon. The following code returns a collection of 20 cells in the rectangle from cell A1 to cell D5....

range = worksheet.Range("A1:D5")

The worksheet.UsedRange method returns a collection of cells, from the upper-left-most used cell to the lower-right-most used cell. This is useful, because the used range is usually much smaller then the entire range of cells in a worksheet.

A Worksheet or Range object also contains a (1-based index) collection of Rows and a (1-based index) collection of Columns, which you can iterate over...

for row in worksheet.Rows
    # ...code...
end

for column in worksheet.Columns
    # ...code...
end

for row in worksheet.Range("A1:D5").Rows
    # ...code...
end

for row in worksheet.UsedRange.Rows
    # ...code...
end

To get the number of rows or columns in a range, call the range's Count method...

(1..worksheet.UsedRange.Rows.Count).each do |row|
    #...code...
end

You can reference a single Row or Column by index. For example, to apply a bold font to the first row of the worksheet...

worksheet.Rows(1).Font.Bold = true

To set the width of column 4 (D) to 25 pixels...

worksheet.Columns(4).ColumnWidth = 25.0

A cell object has numerous child objects and methods. The Value (read/write) method returns a Cell's value as it is stored internally. A cell's Text (read-only) method returns the value as it is currently displayed. For example, if cell B4 has the value "1234.56789" formatted as Currency, the Value and Text methods return different values...

worksheet.Cells(4, 2).Value     # => "1234.56789"
worksheet.Cells(4, 2).Text      # => "$1234.57"

Calling the Value method on a Range object returns an array of arrays of values. Here's a quick way to get a 2-dimensional array of all values in a worksheet...

data = worksheet.UsedRange.Value

You cannot call the Text method on a Range object.

As previously mentioned, one of the best hands-on ways of learning the various Excel objects and methods/properties is to record a macro in Excel, then review the macro's VBA code and translate it to Ruby.

As always, feel free to post comments or send email if you have questions, comments, or suggestions for future topics.

Automating Excel with Ruby: The Worksheet Object

Yesterday, we looked at the Excel Workbook object. Today, we'll work with the Worksheets collection and Worksheet objects.

The Workbook object contains a (1-based index) collection of all open worksheets. You could think of the workbook.Worksheets() statement as a method which returns the collection of Worksheets (if called with no argument) or a single Worksheet (if called with an index or name argument).

To create a new worksheet, call the Add method of the Worksheets collection:

worksheet = workbook.Worksheets.Add

You can reference a single worksheet by index:

worksheet = workbook.Worksheets(1)

...or by name:

worksheet = workbook.Worksheets('Sheet1')

... or you can reference the currently active (selected) Worksheet:

worksheet = workbook.ActiveSheet

The Worksheets collection is not like a standard Ruby array. It's has a 1-based index, has no Size property, and does not recognize the '-1' index value. But it has a Count property in lieu of the Size property, so to reference the last worksheet in the collection, you could do this:

worksheet = workbook.Worksheets(workbook.Worksheets.Count)

You can, however, iterate over the Worksheets collections:

for worksheet in workbook.Worksheets
    # ...code...
end

Note that the Worksheets collections may contain hidden worksheets. To hide or unhide a Worksheet, set its Visible property:

worksheet.Visible = false
workbook.Worksheets(1).Visible = true

You can get or set the name of the worksheet:

worksheet.Name = 'Amish Computer Scientists'

Note that the worksheet name cannot exceed 31 characters, and cannot include these characters: \ / ? : * [ ]

To move a worksheet, call its Move method, passing the Worksheet object that you want to move this worksheet in front of. For example, to move 'Sheet1' to the slot before 'Sheet3':

workbook.Worksheets('Sheet1').Move(workbook.Worksheets('Sheet3'))

...or to move the third worksheet to the first slot:

workbook.Worksheets(3).Move(workbook.Worksheets(1))

To copy a worksheet, use the Copy command as you would the Move command:

workbook.Worksheets(3).Copy(workbook.Worksheets(1))

To print a worksheet, call the Worksheet object's PrintOut method:

worksheet.PrintOut

To delete a worksheet, call its Delete method:

worksheet.Delete

This will normally result in an Alert box being displayed, asking you to confirm the deletion. To disable such alerts, set the Excel Application object's DisplayAlerts property:

excel.DisplayAlerts = false

That's all for now. Soon we'll look at rows, columns, and cells. As always, feel free to leave a comment or send me email if there are specific items you would like to see discussed here.