ADO, Excel, and Data Types

I've written before about using Ruby with Microsoft's ADO technology to query Excel workbooks as databases.

This works well---most of the time. But you may occasionally bump into data type issues, where you find that Excel/ADO treat a specific column as a different data type than you expected.

For this reason, I recommend using a collection of ADO functions that will expressly convert a value to a specific data type. These ADO functions include:

CStr(x) - Converts x to a String value
CDec(x) - Converts x to a Decimal value
CInt(x) - Converts x to an Integer value
CDate(x) - Converts x to a Date value
CCur(x) - Converts x to a Currency value

For example, instead of assuming that the ORDER_NUMBER field would be treated as a string...

    SELECT * FROM ORDERS WHERE ORDER_NUMBER = '12345' ;

...call the CStr() function on it to ensure a string-to-string comparison:

    SELECT * FROM ORDERS WHERE CStr(ORDER_NUMBER) = '12345' ;

It's a few extra keystrokes that could save you time---and frustration---in the long run.

More information can be found here.

Automating Outlook with Ruby: Saving Mail Messages To Files

I've talked in the past about managing mail messages in your Inbox, and about saving email attachments to your hard drive. A reader recently asked about saving a mail message as a file on your hard drive.

This can be done by calling the MailItem's SaveAs() method. This method takes two arguments. The first argument is a string indicating the full path and filename to which you want to save the message. The second argument is an integer that indicates the file type.

Let's look at a brief example that saves an Inbox message as an HTML file:

require 'win32ole'

olHTML = 5

outlook = WIN32OLE.connect("Outlook.Application")

mapi = outlook.GetNameSpace("MAPI")

inbox = mapi.GetDefaultFolder(6)

msg = inbox.Items(2)

msg.SaveAs('c:\temp\message.htm', olHTML)

Further details on the SaveAs() method can be found here.

A full list of valid file type values can be found here.

That's all for now. Questions? Comments? Suggestions? Post a comment or send me an email.

Thanks for stopping by!

JRuby 1.6: Now With Win32OLE Goodness

JRuby 1.6 has just been released and, among the many new features, the JRuby installer for Windows now includes the win32ole library.

This is good news for those of us that frequently use Ruby's win32ole module to automate Excel, Word, and a variety of other Windows applications and processes. Now you have the option of running your code on the JVM.

And running on the JVM opens up a world of possibilities. You could, for example, whip up a Swing GUI via Monkeybars, and use Rawr to package it up as an EXE file for ease of distribution.

Interested? Download JRuby 1.6 at jruby.org, take the win32ole module for a drive around the block, and let me know if you hit any potholes.

Coming Soon: win32ole for JRuby

FYI: In a recent Ruby Forum thread, Charles Nutter wrote:

FWIW, a win32ole library is in development and should be in JRuby for 1.6.

This is good news for those of us who do a lot of work with the standard Ruby win32ole library.

My sincere thanks to all who are helping to make this happen.

Ruby & PowerPoint: Inserting HyperLinks

I've written previously about automating Microsoft PowerPoint with Ruby. Someone recently asked how to use Ruby code to insert hyperlinks into a PowerPoint slide. Let's take a look now at how this can be done.

Setting the Scene

Let's quickly review the code that will launch PowerPoint...

require 'win32ole'
ppt = WIN32OLE.new('PowerPoint.Application')
ppt.Visible = true

...create a new presentation...

doc = ppt.Presentations.Add()

...add a new slide...

slide = doc.Slides.Add(1, 2)

...and insert text into each of the two textboxes...

slide.Shapes(1).TextFrame.TextRange.Text = "Ruby on Windows"
slide.Shapes(2).TextFrame.TextRange.Text = "Ruby on Windows: PowerPoint"

To fully understand the above code, you may want to read this article, if you haven't already.

ActionSettings and HyperLinks

OK, now we have a PowerPoint presentation with a slide containing two textboxes, and we're ready to make the text of the second textbox a hyperlink. Let's grab a reference to the TextRange object that holds the text contained in the second textbox (a Shape object) on the slide:

text_range = slide.Shapes(2).TextFrame.TextRange 

To define the action that is to be taken when a TextRange object is clicked, we need to work with the first item in the TextRange's ActionSettings collection:

action = text_range.ActionSettings(1)

This returns the ActionSetting object that represents a MouseClick action. This ActionSetting object includes a HyperLink object...

link = action.Hyperlink

...and it is the properties of this HyperLink object that we will modify to result in a link to our website:

link.Address = "http://rubyonwindows.blogspot.com/search/label/powerpoint"
link.ScreenTip = "Click to go to website"
link.TextToDisplay = "Ruby on Windows: PowerPoint"

Our complete code looks like this:

require 'win32ole'

ppt = WIN32OLE.new('PowerPoint.Application')
ppt.Visible = true
doc = ppt.Presentations.Add()

slide = doc.Slides.Add(1, 2)
slide.Shapes(1).TextFrame.TextRange.Text = "Ruby on Windows"
slide.Shapes(2).TextFrame.TextRange.Text = "Ruby on Windows: PowerPoint"

text_range = slide.Shapes(2).TextFrame.TextRange 
action = text_range.ActionSettings(1)
link = action.Hyperlink

link.Address = "http://rubyonwindows.blogspot.com/search/label/powerpoint"
link.ScreenTip = "Click to go to website"
link.TextToDisplay = "Ruby on Windows: PowerPoint"

That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.

Thanks for stopping by!

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!

Creating an iTunes Song Inventory in Excel

I've talked in the past about automating iTunes, and about automating Excel. Let's now look at how to use Ruby to produce an iTunes report in Excel. Our finished product will be a sorted worksheet containing Artist, Year, Album, and Song Name.

As usual, we'll be working with the win32ole library, so include the following at the top of your code:

require 'win32ole'

Next, we want to launch the iTunes application using the WIN32OLE.new() method:

itunes = WIN32OLE.new('iTunes.Application')

We'll create an array to hold the iTunes Library data:

data = []

The following code iterates over the iTunes LibraryPlaylist.Tracks collection. For each track in the library that is not a podcast, our code adds a row to the data array containing Artist, Year, Album, and Year, all of which are properties of the Track object.

itunes.LibraryPlaylist.Tracks.each do |track|
  if not track.Podcast
    data << [track.Artist, track.Year, track.Album, track.Name]
  end
end

To include podcasts in your report, simply remove the Podcast conditional:

itunes.LibraryPlaylist.Tracks.each do |track|
  data << [track.Artist, track.Year, track.Album, track.Name]
end

Now that we have our data array, let's sort it...

data.sort!

...and then insert a row of field names as the first row:

data.insert(0, ['ARTIST', 'YEAR', 'ALBUM', 'NAME'])

Next, we'll launch a new instance of Excel, assigning it to the xl variable...

xl = WIN32OLE.new('Excel.Application')

...and make the application window visible:

xl.Visible = true

We create a new workbook by calling the Application object's Workbooks.Add() method...

wb = xl.Workbooks.Add()

...and we get a reference to the first worksheet in the Workbook object's Worksheets collection:

ws = wb.Worksheets(1)

Now we're ready to insert our data into a range of cells. We'll define a Range of cells that begins at cell A1. We'll call the Range.Resize() method to resize the Range to fit the number of rows (data.size) and the number of columns (data.first.size) in our data array.

rng = ws.Range('A1').Resize(data.size, data.first.size)

Then we insert our data:

rng.Value = data

Finally, we'll do a wee bit of formatting, making the first row bold...

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

...and adding AutoFilter drop-down lists to the column headers...

ws.Rows(1).AutoFilter()

There's a variety of other formatting you could apply, as discussed here.

That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.

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 & Excel: Inserting Pictures Into Cells (New and Improved!)

In a previous article, I discussed a method for inserting images into an Excel worksheet. It seems that the Worksheet.Pictures.Insert() method that I demonstrated in that article, though frequently used, is not actually officially documented in the Excel Object Model Reference. An astute reader has called my attention to this fact and, in reply, I hereby present the officially documented---and probably preferred---method for adding an image to a worksheet.

The Worksheet object's Shapes collection includes an AddPicture() method that creates a picture from an existing file and returns a Shape object that represents the new picture. The syntax is:

.AddPicture(Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height)

All seven arguments are required, but this allows you to specify the position and size of the picture in the method call.

The following code inserts an image into the range of cells from C3 to F5 in the active worksheet:

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet

range = ws.Range('C3:F5')

pic = ws.Shapes.AddPicture( { 
    'FileName' => 'C:\Pictures\Image1.jpg', 
    'LinkToFile' => false, 
    'SaveWithDocument' => true, 
    'Left' => range.Left, 
    'Top' => range.Top, 
    'Width' => range.Width, 
    'Height' => range.Height 
} )
    

You can find further details on the AddPicture() method on MSDN.

My thanks to Charles Roper for his inquiry, prompting me to dig a little deeper.

And my thanks to you for stopping by!

OCR: Converting Images to Text with MODI

Joe Schmoe from Kokomo has a scanned image of a 300-page contract. Joe wishes he could search this file for certain rates and terms, but it's an image, not a text file. OCR might be just what the doctor ordered.

Optical character recognition, usually abbreviated to OCR, is the mechanical or electronic translation of images of handwritten, typewritten or printed text (usually captured by a scanner) into machine-editable text. --Wikipedia

One such OCR solution that you may already have available to you is Microsoft Office Document Imaging (MODI), part of the Microsoft Office suite. Let's look at how you can use Ruby and the MODI API to automate the conversion of a scanned document into text.

Installing MODI

MODI might not have been installed when you installed Microsoft Office, so your first step may be to install it from the Office install disks. If installed, you will probably find an icon for "Microsoft Office Document Imaging" located on your Windows Start/Programs menus under "Microsoft Office Tools". If it's not there, go to your Add/Remove Software control panel, select your Microsoft Office installation, and select the option to add features. Then follow the necessary steps, which may vary depending on your version of Windows and Office.

Accessing the MODI API

To begin with, we'll use the win32ole module to create a new instance of the MODI.Document object:

require 'win32ole'
doc = WIN32OLE.new('MODI.Document')

Loading the Image

The next step is to call the document object's Create() method, passing it the name of the .TIF file to load:

doc.Create('C:\images\image1.tif')

NOTE: MODI only works with TIFF files. If your image is in another format (.JPG or .PNG, for example), you can use an image editor (such as Paint.NET or Photoshop) or code library (such as RMagick) to convert it to TIFF format.

Performing the OCR

The OCR() method performs the optical character recognition on the document. The mthod can be called without parameters...

doc.OCR()

...or with any of three optional parameters:

doc.OCR( { 'LangId' => 9, 
           'OCROrientImage' => true, 
           'OCRStraightenImage' => true } )

LangId: An integer representing the language of the document. English = 9, French = 12, German = 7, Italian = 16, Spanish = 10. This value defaults to the user's regional settings.

OCROrientImage: This boolean value specifies whether the OCR engine attempts to determine the orientation (portrait versus landscape) of the page. The value defaults to true.

OCRStraightenImage: This boolean value specifies whether the OCR engine attempts to "deskew" the image to correct minor misalignments. The value defaults to true.

You may find that tweaking these parameters from their default values produces better results, depending on the individual image(s) you're working with.

Getting the Text

Naturally, you'll want to get your hands on the text produced by the OCR process. Each page of the Document is represented by an Image object. The Image object contains a Layout object; and that Layout object's Text property represents the text for that image/page. So the hierarchy looks like this:

Document
    =>Images
        =>Image
            =>Layout
                =>Text

To accrue the entire text, simply iterate over the Document.Images collection and grab the Layout.Text values. For example:

File.open('my_text.txt', 'w') do |f|
    for image in doc.Images
        f.puts("\n" + image.Layout.Text + "\n")
    end
end

Text, But Not Formatting

No OCR process can guarantee 100% accuracy, but I've found that MODI does a pretty good job recognizing text. Results will vary, of course, depending on the quality of the TIFF image. Note, however, that it cannot preserve formatting of tabular data. So while the text in a series of columns may be produced with a high degree of accuracy, that text will probably be produced with one value per line. So...

apple    orange    pear

...comes out as...

apple
orange
pear

Paragraphs of text have, in my experience, been produced with the proper line feeds. Play around with it and see if it meets your needs.

That concludes our show for today. Thanks for tuning in!

Ruby & Excel: Inserting Pictures Into Cells

A reader recently asked how to insert an image from their PC into a cell in an Excel worksheet. So if you have a couple of minutes, I'll demonstrate how to insert an image, specify the exact position, and resize the image.

UPDATE: It seems that the Worksheet.Pictures.Insert() method that I demonstrated below, though frequently used, is not actually officially documented in the Excel Object Model Reference. See this newer article for the officially documented method.

Let's start by connecting to an open Excel worksheet:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet

Inserting the Image

To insert a picture into a worksheet, call the Worksheet object's Pictures.Insert() method, passing it the filename of the image to insert:

pic = ws.Pictures.Insert('C:\Pictures\Image1.jpg')

The Pictures.Insert() method inserts a new Picture object into the Pictures collection and returns a reference to the new Picture object, which we've assigned to the variable pic.

Positioning the Image

OK, now that you've got the picture inserted into the worksheet, you probably want to specify its exact position, perhaps aligned with a certain range of cells. Let's start by defining that range of cells, from cell C3 to cell F5:

range = ws.Range('C3:F5')

We want the picture aligned with the top/left corner of our range, so we'll set our Picture object's Top and Left properties to be the same as our Range object's Top and Left properties.

pic.Top = range.Top
pic.Left = range.Left

Resizing the Image

Now that we have the top/left position set, let's move on to specifying the width and height of the picture. The original image has an aspect ratio, which is the image's width divided by its height. An image that is 800x600 pixels could be said to have an aspect ratio of 4:3. When resizing an image, you may wish to maintain its original aspect ratio, to avoid making the image appear stretched in one direction or the other.

By default, a Picture object in Excel has its aspect ratio locked, so that when you change either the width or the height, the other dimension is automatically adjusted to preserve the aspect ratio.

You adjust the width and/or height of a Picture object by setting its---wait for it---Width and Height properties. With the aspect ratio locked, we can set the image to fill the width of the range...

pic.Width = range.Width

...or to fill the height of the range...

pic.Height = range.Height

...but we cannot successfully apply both settings without first unlocking the aspect ratio.

To unlock the Picture's aspect ratio, you set the Picture object's ShapeRange.LockAspectRatio property to false:

pic.ShapeRange.LockAspectRatio = false

Now that we have unlocked the aspect ratio, we can set the picture's width and height to match the range's width and height:

pic.Width = range.Width
pic.Height = range.Height

The Width and Height properties are just numerical values. So you could instead do something like this:

pic.Width = 400
pic.Height = 300

And that, my friends, is how you insert, position, and resize an image in Excel.

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

Ruby & Excel: Hiding and Unhiding Columns, Rows, and Worksheets

Occasionally, when working with Excel, you may have a need to hide certain columns or rows in a worksheet. As an example, perhaps your worksheet lists revenue for each of 12 months, but your intended recipient only wants to see the columns showing year-to-date totals. Whatever the reason, you'd like to simply hide certain columns (or rows) rather than delete them completely. And since you're here, you'd probably prefer to do that with Ruby code...

Let's set the scene by assuming that your code is working with an open workbook and you want to hide columns in the currently selected worksheet:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook
ws = xl.ActiveSheet

Hiding Columns

To hide one or more columns, obtain a reference to the column range(s) and set the Hidden property to true:

ws.Columns('A').Hidden = true
ws.Columns('D:K').Hidden = true

Unhiding (displaying) columns works the same way; except, of course, you set the Hidden property to false:

ws.Columns('A').Hidden = false
ws.Columns('D:K').Hidden = false

The following code ensures that all columns in a worksheet are visible:

ws.Columns.Hidden = false

Hiding Rows

Rows have a Hidden property, too, so you can do this to hide rows...

ws.Rows('3').Hidden = true
ws.Rows('7:11').Hidden = true

...and to unhide them:

ws.Rows('3').Hidden = false
ws.Rows('7:11').Hidden = false

Hiding Worksheets

But what if you want to hide an entire worksheet? The Worksheet object doesn't have a Hidden property. Instead, you'll use the Visible property. So to hide the 'Great Amish Scientists' worksheet in the active workbook (wb), we'd use this code:

wb.Worksheets('Great Amish Scientists').Visible = false

And to unhide it, we'd toggle the Visible property back to true:

wb.Worksheets('Great Amish Scientists').Visible = true

The following code ensures that all worksheets in a workbook are visible:

wb.Worksheets.each do |ws|
    ws.Visible = true
end

And that, folks, is pretty much all there is to it. Let me know if you have any questions or comments, and thanks for stopping by!

Handling WIN32OLE Events in Excel

Someone recently asked how to have Ruby react to events in Excel. Specifically, they were trying retrieve the contents of a row in a worksheet when it's selected.

The win32ole module provides a WIN32OLE_EVENT class that will allow you to execute a block of code when a specific event occurs.

To set the scene, let's use the WIN32OLE.connect() method to connect to an existing instance of Microsoft Excel and grab a reference to the currently active workbook:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook

Next, we'll call the WIN32OLE_EVENT.new() method to create a new OLE event object. You pass this method an OLE object---our Workbook object---and the name of the event sink. In this instance, we want to use the WorkbookEvents sink:

ev = WIN32OLE_EVENT.new(wb, 'WorkbookEvents')

Once you have your event sink defined, you call its on_event() method to hook into a particular event and run a block of code when that event fires. In our scenario, we want to take action when the SheetSelectionChange event fires.

ev.on_event('SheetSelectionChange') do
    range = xl.Selection
    puts(range.Value)
    STDOUT.flush()
end

The above block of code will execute when the user selects a range of cells, and will print out the array of values from the selected cells.

Finally, you need to start the event message loop to begin the event monitoring:

loop do
    WIN32OLE_EVENT.message_loop
end

In the real world, we need a means to exit the message loop. Let's catch the BeforeClose event, which fires (of course) just prior to the workbook being closed:

ev.on_event('BeforeClose') do
    exit_event_loop
end

Now, when the BeforeClose event fires, we'll have it call a new exit_event_loop() method, which sets a $LOOP value to false:

$LOOP = true

def exit_event_loop
    $LOOP = false
end

Finally, we'll modify our earlier message loop block, accordingly, and also toss in a brief pause:

while $LOOP
    WIN32OLE_EVENT.message_loop
    sleep(0.1)
end

Our complete code looks something like this:

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook

ev = WIN32OLE_EVENT.new(wb, 'WorkbookEvents')

ev.on_event('SheetSelectionChange') do
    range = xl.Selection
    puts(range.Value)
    STDOUT.flush()
end

ev.on_event('BeforeClose') do
    puts('Closed');STDOUT.flush
    exit_event_loop
end

$LOOP = true

def exit_event_loop
    $LOOP = false
end

while $LOOP
    WIN32OLE_EVENT.message_loop
    sleep(0.1)
end

And there you have it. Tweak to suit your individual needs.

Thanks for stopping by!

WIN32OLE Objects: Class Names and Methods

Over at Stack Overflow, a member asked a couple questions regarding working with WIN32OLE objects.

Q1: How do I determine the class name of an OLE object instance?

The code:

object.ole_obj_help.name

The explanation:

Calling the ole_obj_help method on a WIN32OLE object returns a WIN32OLE_TYPE object. The WIN32OLE_TYPE object includes a name attribute. Calling this on an Excel application object...

xl = WIN32OLE.new( 'Excel.Application' )
xl.ole_obj_help.name

...returns the string '_Application', while calling it on a Worksheet object...

 xl.ActiveSheet.ole_obj_help.name

...returns the string '_Worksheet'.

Q2: How can I tell whether an object instance supports a particular method?

The code:

object.ole_methods.collect!{ |x| x.to_s }.include?( 'MethodName' )

The explanation:

As mentioned previously here, calling the ole_methods method on a WIN32OLE object returns an array of WIN32OLE objects that represent methods that can be called on that object. You can convert each object in the array to a string, using the collect! method. Then it's a simple matter to call the include? method to see if the resulting array of strings contains a certain value.

And so we can use this to find that the Excel application object includes a Quit method...

xl.ole_methods.collect!{ |x| x.to_s }.include?( 'Quit' )
=> true

...but does not include a Close method:

xl.ole_methods.collect!{ |x| x.to_s }.include?( 'Close' )
=> false

There you have it. 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!

Automating PowerPoint with Ruby

Here at Ruby on Windows, we've looked at how to automate a variety of applications using Ruby. One popular MS Office app that I haven't yet discussed is PowerPoint, Microsoft's ubiquitous presentation software. But, like other Office apps, PowerPoint exposes a full-featured object model for automating, so there's virtually nothing that you can't do with PowerPoint via Ruby code.

As usual, we'll be leveraging the win32ole library:

require 'win32ole'

We'll start by creating an instance of the PowerPoint Application object, and assigning that object to a variable we'll call ppt :

ppt = WIN32OLE.new('PowerPoint.Application')

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

Like most MS Office applications, the PowerPoint application window is not visible by default when launched from code. To show or hide the Application window, set the Visible property...

ppt.Visible = true

The Presentations collection contains the list of presentation documents currently open. To create a new Presentation, we'll call the Presentations.Add() method...

doc = ppt.Presentations.Add()

To open an existing presentation, we'll call the Presentations.Open() method and pass it the path and name of the PPT file...

doc = ppt.Presentations.Open('c:\docs\mully1.ppt')

The Presentations.Add() and Presentations.Open() methods add the new Presentation object to the Presentations collection, and return a reference to the Presentation object. As you can see above, we're assigning that object to a variable we've called doc.

The Presentation object contains a collection of Slide objects. To add a new slide to your presentation, call the Slides.Add() method and pass it two integers as arguments: the position at which to insert the new slide, and the layout to use for the new slide...

ppLayoutText = 2
slide = doc.Slides.Add(1, ppLayoutText)

The position argument must be between 1 and the current number of slides plus 1.

The Slides.Add() method adds the new slide to the Slides collection and returns a reference to the new slide, which we've assigned to the variable slide.

To get the current count of slides, call the Slides.Count() method.

So, we could use this code to add a new blank slide to the end of the collection:

slide = doc.Slides.Add(doc.Slides.Count + 1, ppLayoutBlank)

You can find a complete list of Slide Layout constants in this Microsoft document.

Each slide object contains a Shapes collection of all objects on the slide, such as textboxes, pictures, and OLE objects.

In the words of Microsoft, a shape object's TextFrame "contains the text in the text frame and the properties and methods that control the alignment and anchoring of the text frame". The TextFrame object's TextRange property "returns a TextRange object that represents the text in the specified text frame". You can get/set the text via the TextRange object's Text property. Got all that? There'll be a quiz later.

Putting this all together, we can insert or change the text of the first textbox on our slide using a line of code like this:

slide.Shapes(1).TextFrame.TextRange.Text = "Hello, World!"

To save your newly-created presentation, call the SaveAs() method and pass it the path and filename:

doc.SaveAs('c:\docs\presentation1.ppt')

To save changes to a previously-saved presentation, call the Save() method:

doc.Save()

Call the presentation object's Close() method to close the presentation, and call the application object's Quit() method to exit the PowerPoint application:

doc.Close()
ppt.Quit()

Well, that's our show for today. There's much more to be covered on this topic. Please let me know if you have specific questions or suggestions for future articles.

Thanks for stopping by!

Working with Win32OLE Constants

Excel (and Word, Outlook, etc.) has hundreds of built-in constants that represent numeric values. When reviewing code written in Visual Basic, you may see these constants passed when calling methods or setting property values:

mychart.ChartType = xlColumnClustered

But the above line of code won't work on its own in Ruby, as xlColumnClustered won't be recognized as a constant. So, when translating this code to Ruby, how do you get it to work?

Do-It-Yourself

In my code examples here, I usually either provide the actual value...

mychart.ChartType = 51

...or explicitly assign the value to a constant or variable myself:

xlColumnClustered = 51
mychart.ChartType = xlColumnClustered

In Ruby, constants must begin with an upper-case letter, so 'xlColumnClustered' is really a variable in the last example above. To make it a constant, I should actually name it something like 'XlColumnClustered', with an upper-case X.

How did I know that the Excel constant xlColumnClustered equals 51? Well, I simply googled 'Const xlColumnClustered' and quickly found examples where the constant was being explicitly declared in VB/VBA code ("Const xlColumnClustered = 51"). Googling for 'Excel Constants' will return numerous pages that list all the the Excel constants and their corresponding values. Microsoft provides their own listing here.

Loading the Win32OLE Constants

But you don't have to do it yourself. Ruby's win32ole library allows you to load an object's built-in constants into a class or module. To do so, first create an empty class or module:

class ExcelConst
end

Then call the WIN32OLE.const_load method. Pass this method your previously-defined Excel application object and your new ExcelConst class:

WIN32OLE.const_load(excel, ExcelConst)

This loads the Excel application object's built-in constants into your ExcelConst class, but each constant will now begin with an upper-case letter, as required in Ruby. Now you can call Excel's built-in constants from your new ExcelConst class. So our original example...

mychart.ChartType = xlColumnClustered

...works with only a slight modification, inserting the name of our ExcelConst class and capitalizing the first letter of the constant:

mychart.ChartType = ExcelConst::XlColumnClustered

There you have it. This same method works for loading constants from other win32 application objects, such as Word or Outlook.

By the way, you can review the docs for the WIN32OLE library, including the const_load method, here.

I hope you found this useful. Feel free to post a comment here or email me if you have questions, comments, or suggestions for future articles (or the book).

Thanks for stopping by!

Automating Excel: Creating Charts

They say a picture is worth a thousand words, and sometimes a chart can make your point more effectively than the raw data behind it can. This is especially true when comparing relative values such as monthly revenue data or team statistics.

In our last episode, we looked at how to define titles in existing Excel charts. Sticking with the Excel Charts theme, let's now investigate how to create new charts in Excel.

A chart is a visualization of data, and for this example, the data represents Runs Scored and Runs Allowed for American League Baseball teams, as reported on the mlb.com website. Our Excel worksheet contains a row for each team, with columns for Runs Scored and Runs Allowed:



We'll use the win32ole library for automating Excel, and we'll connect to a running instance of Excel and use the already open 'mlb_stats.xls' workbook:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.Workbooks('mlb_stats.xls')

Let's define some parameter variables that we'll use later:

xlColumns = 2
xlColumnClustered = 51
xlWhite = 2
xlRed = 3
xlBlue = 5
xlGray = 15

To add a new Chart object to the workbook's Charts collection, call the Charts.Add method:

mychart = wb.Charts.Add
mychart.Name = "MLB Scoring"

The Charts.Add method returns a reference to the newly-created Chart object, which we've assigned to the variable mychart.

To delete an existing Chart object, call the Charts(chart).Delete method, where chart is the name or (1-based) index of the chart to delete:

wb.Charts("MLB Scoring").Delete
wb.Charts(1).Delete

Naturally, we can't produce a chart without data, and we use the SetSourceData method to define the source of the data that the chart will represent. This method takes 2 arguments, [1] a range of cells from a worksheet, and [2] a number that indicates whether to plot the graph by rows (1) or by columns (2):

mychart.SetSourceData wb.Worksheets("Runs Scored and Allowed").Range("A1:C15"), xlColumns

There are many different types of charts to choose from, and for our purpose, we'll use a 2-dimensional column [aka vertical bars] chart, setting the ChartType property to 51 (via our previously-defined xlColumnClustered variable):

mychart.ChartType = xlColumnClustered

Now, you could execute your code at this point and create a new chart. But let's tweak the colors a bit. The SeriesCollection object holds each series of data and its related properties. Our chart has 2 series, one for Runs Scored, and one for Runs Allowed. Let's make the Runs Scored columns blue and the Runs Allowed columns red, via the Interior.ColorIndex property:

mychart.SeriesCollection(1).Interior.ColorIndex = xlBlue
mychart.SeriesCollection(2).Interior.ColorIndex = xlRed

We could also dictate the name that is displayed for each series in the legend:

mychart.SeriesCollection(1).Name = "Runs Scored"
mychart.SeriesCollection(2).Name = "Runs Allowed  "

If you do not define a name for each series, Excel will try to pull it from your source data worksheet.

The PlotArea represents the area on which the data (columns, in our case) is plotted. The ChartArea is the area surrounding the PlotArea, on which the title, axes, and legend are placed. For demonstration purposes, let's define specific colors for these objects by setting their Interior.ColorIndex property:

mychart.ChartArea.Interior.ColorIndex = xlWhite
mychart.ChartArea.Border.ColorIndex = xlBlue
mychart.PlotArea.Interior.ColorIndex = xlGray
mychart.PlotArea.Border.ColorIndex = xlWhite

Finally, we'll add a title to the top of the chart, and format the text:

mychart.HasTitle = true
mychart.ChartTitle.Characters.Text = "American League - Runs Scored vs. Runs Allowed"
mychart.ChartTitle.Font.Name = 'Verdana'
mychart.ChartTitle.Font.Size = 16
mychart.ChartTitle.Font.Bold = true

Note that we first have to set the HasTitle property to true. Without first doing this, trying to define the various ChartTitle properties will raise an error.

Our complete code looks like this, and produces a chart that looks like this:



Not a bad start, eh? A quick glance at this chart tells you that Boston and Chicago are doing very well, run-wise, and that it could be a very long season for Kansas City and Seattle. And we see that Texas scores a tremendous amount of runs, but allows even more.

I encourage you to investigate the vast array of chart methods and properties. Towards that end, you might want to check out Jon Peltier's Chart tutorials, which I have found to be helpful.

Questions? Comments? Suggestions? Post a comment here or send me an email.

Thanks for stopping by!

Win32OLE Library for JRuby?

Charles O. Nutter of the JRuby project is seeking proposals for Google's Summer of Code (GSoC).

Among the suggested projects is this one of possible interest to many [J]Ruby on Windows users:

Win32OLE Library for JRuby - Implement the win32ole library for JRuby. This would also be an excellent contribution, since there's already libraries like Jacob to take some of the pain out of it, and it would be great to have it working on JRuby. (suggested by sgwong)

Update from Charlie:

BTW, if you know anyone that might be interested in doing this through GSoC, please spread the word quickly. The deadline for submissions is Monday, April 7, so there's not a lot of time left.

And to make it more attractive, there's already a very nice Java library that provides COM/OLE support (http://danadler.com/jacob/) so the task is more about wiring that library into JRuby than mucking about with any low-level native nasties.

I'd love to see this project undertaken, as it could be a big boost for those of us who currently do COM automation work with Ruby. I haven't done much with JRuby yet (a wee bit of Jython in the past), but I like the possibilities for having an easily-distributable app (JAR file?) that includes a GUI (e.g., Swing) and Win32OLE functionality.

What do you think? Would a Win32OLE library increase the likelihood of you using JRuby for some future projects?