Sunday, April 29, 2007

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...

Thursday, April 26, 2007

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):

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.

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:

Tuesday, April 17, 2007

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:

Sunday, April 15, 2007

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:

Saturday, April 14, 2007

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.

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 somethingelse # do something elseend

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

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:

Saturday, April 7, 2007

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:

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...

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.

Friday, April 6, 2007

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...

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.

Sunday, April 1, 2007

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: