Ruby on Windows

Automating Outlook with Ruby: Saving Mail Messages To Files

2011-09-02T19:27:00.005-05:00

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

2011-03-16T07:01:00.010-05:00

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.

Ruby on NetBeans: Here Comes the Cavalry

2011-02-07T08:49:00.005-05:00

As recently reported, the NetBeans IDE team has decided to drop support for Ruby (and Rails) from NetBeans 7.0.

I have received many good suggestions from readers, with RedCar and RubyMine getting a lot of love.

But wait! What's that? It sounds like trumpets off in the distance... and hoofbeats.

Indeed, Tom Enebo is leading the cavalry to save the day, as he reported in the JRuby forum recently:

I have been talking to Netbeans team about us adopting the project and this is a done deal. Putting together some details and you should see a blog post about this in the next few days. So, if you like using Netbeans dont worry, it will still be an available option in the plugins catalog (not sure about the full Ruby product as an independent download from netbeans site yet).

Good news for all those that would like to continue to use NetBeans with Ruby.

Thanks, Tom!

NetBeans Drops Support for Ruby and Rails

2011-01-27T08:27:00.004-05:00

The NetBeans team has, unfortunately, decided to remove Ruby and Rails support from the NetBeans IDE:

After thorough consideration, we have taken the difficult step to discontinue support for Ruby on Rails in the NetBeans IDE.

As of January 27, the Ruby on Rails module will be gone from development builds of NetBeans IDE 7.0.

NetBeans has served me well for larger projects over the years, and NetBeans 6.9.1 retains its existing support for Ruby and Rails. But perhaps it's time to start considering Ruby IDE alternatives, and I'm open to suggestions.

Coming Soon: win32ole for JRuby

2010-09-26T17:48:00.004-05:00

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.

New Ruby IDE Discussion Group

2010-08-28T14:03:00.003-05:00

FYI, Ed Howland has launched a new Ruby IDE discussion group:

I set this group up to focus discussions about a community developed IDE written in Ruby (but not necessarily limited to writing code in just that language. The intent is to take general discussion specific off-line from the main ruby-talk group Hopefully, this will be a high signal-noise ratio discussion.

So if you've got some constructive thoughts on what would be the ideal Ruby IDE, make yourself heard here:

http://groups.google.com/group/ruby-ide

David

Ruby & PowerPoint: Inserting HyperLinks

2010-02-10T07:59:00.011-05:00

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

2010-01-24T07:55:00.001-05:00

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:

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

2010-01-20T07:57:00.000-05:00

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

2010-01-14T08:11:00.014-05:00

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

2009-08-24T06:45:00.007-05:00

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!

About the Book

2009-08-20T06:38:00.004-05:00

A few readers have asked me for details on the book-in-progress.

The working title is "Automating Windows Applications with Ruby". As the title implies, the focus is on automating applications, usually via Win32OLE/COM.

This is not an "Introduction to Ruby" book: I make the assumption that the reader already has a working knowledge of the language and now wants to put it to good use driving applications and processes on Windows. Some of the topics covered include:

* Microsoft Excel
* Microsoft Word
* Microsoft Outlook
* Microsoft PowerPoint
* Database Access with ADO
* Microsoft Access
* Microsoft SQL Server
* Microsoft Internet Explorer
* Microsoft Office Document Imaging (OCR)
* iTunes
* Windows Media Player
* Text-to-Speech
* Microsoft Agent
* Hummingbird HostExplorer
* Attachmate Extra
* Windows Management Instrumentation

Each chapter will include step-by-step tutorials, and many chapters will also include a reference section covering commonly-used classes, properties, and methods. Some chapters are extensive, while others may be only a few pages in length.

This isn't intended to be an "Everything You'd Ever Want To Know About..." resource, but it is my intention to provide you with a book that will serve as both a quick-start guide and a go-to reference for automating a wide variety of applications.

Stay tuned...

Ruby & Excel: Inserting Pictures Into Cells (New and Improved!)

2009-08-05T07:26:00.004-05:00

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

2009-08-02T15:33:00.003-05:00

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

2009-07-12T18:01:00.005-05:00

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

2009-07-09T07:34:00.001-05:00

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!

wxRuby: Changing Text Fonts and Colours

2009-06-28T19:15:00.012-05:00

A reader recently asked me how to change the text font size and color for wxRuby controls such as the StaticText control. Let's take a look at how you can do this.

Imagine, if you will, that we have a StaticText control named my_control...

Changing Fonts

We'll begin by creating a new Font object by calling the Font.new() method:


my_font = Wx::Font.new()

Next, we'll set various attributes of our new Font object:


my_font.set_point_size(20)
my_font.set_family(Wx::FONTFAMILY_MODERN)
my_font.set_style(Wx::FONTSTYLE_ITALIC) 
my_font.set_weight(Wx::FONTWEIGHT_BOLD)

Once we have our Font object defined to our satisfaction, we can then pass it to our control's set_font() method:


my_control.set_font(my_font)

Changing Colors

Text color is not an attribute of the Font object, but is rather the foreground color of the control. So, to change the color of the text, we call the control's set_foreground_colour() method and pass it a Colour object. We'll make it easy and use one of wxRuby's built-in Colour constants:


my_control.set_foreground_colour(Wx::RED)

Make a note of the spelling: colour, not color.

Further Reading

A simple working example can be found here.

Documentation of the Font object can be found here.

Documentation of the set_foreground_colour() method can be found here.

Documentation of the Colour object can be found here.

And there you have it. Let me know if you'd like to see more on this or other subjects.

Thanks for stopping by!

Ruby & Word: Inserting Tables

2009-06-23T08:00:00.001-05:00

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

Setting the Stage

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


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

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


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

Moving to the End of the Document

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


word.Selection.EndKey(6)

Adding a New Table

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

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

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


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

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

Inserting Text into Table Cells

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


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

Adding Rows

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


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

There you have it. Thanks for stopping by!

Ruby & Word: Counting Words and Pages

2009-06-12T07:06:00.014-05:00

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

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


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

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


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

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


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

...or on a Range object...


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

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


word_count = doc.ComputeStatistics(WdStatisticWords, true)

The IncludeFootnotesAndEndnotes parameter defaults to false.

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

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

Ruby & Word: Creating and Applying Styles

2009-06-04T08:00:00.000-05:00

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

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

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


doc = word.ActiveDocument
styles = doc.Styles

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


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

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


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

The default is WdStyleTypeParagraph.

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


code_style.BaseStyle = 'Normal'

Font properties can be defined...


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

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


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

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

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


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

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

Thanks for stopping by!

The OCRA Compiler: Tips, Tricks, and Gotchas

2009-05-24T17:00:00.001-05:00

I've previously mentioned Lars Christensen's One-Click Ruby Application Builder, a "compiler" for Ruby scripts that shows a lot of potential (and current value). There's not much documentation yet, but folks are installing it, using it, and providing feedback. It works right out of the box for many purposes, but here are a few things to keep in mind as you use it...

"Failed to create directory" Error

One user reported receiving the error "Failed to create directory" when running the compiled executable. As a possible workaround, try running ocra.rb from the directory where your script is located. For example, instead of running...


ocra.rb "C:\code\rubyscripts\application.rbw"

...navigate to the "C:\code\rubyscripts" directory, then run:


ocra.rb application.rbw

Note that while this resolved the problem on my machine, it didn't help the person who originally reported the problem.

Require RubyGems

When you compile your script (which uses one or more gems) with OCRA and then run the executable, you may receive a 'no such file to load' error. Try adding the line...


require 'rubygems'

...to the top of your script, above the other require statements. I've found that a script that runs pre-compiled without this statement may not execute once compiled.

Compile Without Running

I mentioned earlier that it would be nice to have an option to avoid fully running the script, similar to RubyScript2Exe's exit if RUBYSCRIPT2EXE.is_compiling? idiom. A tip of the hat goes to reader "BackOrder", who offered the following solution:


exit if Object.const_defined?(:Ocra)

Put all your require statements at the top of your code, followed by this line.

OCRA and Mechanize: "libxml2.dll not found"

There may be a problem compiling a script that requires the mechanize gem. Running the compiled executable resulted in a "libxml2.dll was not found" error on my machine.

Note that the above observations relate to version 1.0.2 (current as of this writing) of the ocra gem.

More information is available in the OCRA forums and bug tracker.

Thanks again to Lars for creating OCRA, and to the users who have installed it, used it, and provided their feedback.

Thanks for stopping by!

Handling WIN32OLE Events in Excel

2009-05-17T11:03:00.003-05:00

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!

OCRA: One-Click Ruby Application Builder

2009-05-14T07:12:00.002-05:00

I recently mentioned the fact that RubyScript2Exe 0.5.3 doesn't play well with recent versions of RubyGems. At the end of that post I mentioned that there are alternatives emerging for creating executables from your Ruby code, including Lars Christensen's OCRA, the One-Click Ruby Application Builder.

I've had a chance to take OCRA for a short test drive and it looks promising. Like Erik Veenstra's RubyScript2Exe, OCRA lets you "compile" your ruby code into an EXE file that you can distribute to others, without requiring that the users have Ruby installed on their PCs. To quote the OCRA page:

The executable is a self-extracting, self-running executable that contains the Ruby interpreter, your source code and any additionally needed ruby libraries or DLL.

OCRA can be installed via RubyGems: open a console window and type:

gem install ocra

It's also available from the RubyForge page. Version 1.0.2 is the latest as of this writing.

The syntax to compile a script is...


ocra.rb [option] your/script.rb

...where option is one or more of the following:


--dll dllname    Include additional DLLs from the Ruby bindir.
--no-lzma        Disable LZMA compression of the executable.
--quiet          Suppress output.
--help           Display this information.
--windows        Force Windows application (rubyw.exe)
--console        Force console application (ruby.exe)
--no-autoload    Don't load/include script.rb's autoloads

Thus far, I have exercised only the --windows and --console options.

To create a non-console application, either use the --windows option or give your script the .rbw filename extension. Compiling a basic non-console script is as simple as opening a console window, navigating to the folder containing your script, and typing:


ocra.rb myscript.rbw

OCRA will then run your script to check for dependencies, gather the necessary files, and create your executable. An option to avoid fully running the script would be nice, similar to RubyScript2Exe's exit if RUBYSCRIPT2EXE.is_compiling? idiom.

OCRA uses LZMA compression, so the resulting executable is relatively small. A simple wxRuby app, for example, resulted in a 2.5 Mb executable. The same app compiled with RubyScript2Exe weighed in at over 9 Mb. Apps that do not require a GUI toolkit will be even smaller, perhaps 500k.

I haven't spent a lot of time with OCRA yet, but I think it shows great potential and I want to thank Lars Christensen for his efforts. If you have a need to distribute Ruby programs to non-technical users, you should check it out and pass your feedback along to Lars.

Thanks for stopping by!

Ruby & Excel: Extracting Cell Comments

2009-05-11T07:16:00.001-05:00

Someone recently asked how to get the comment text from a particular cell in an Excel worksheet.

You could call the cell's Comment property to obtain a reference to the Comment object, then use the Comment object's Text property to get---or set---the text of the comment.

Imagine that you have a workbook open and you want to get the comment text from cell B2 in the currently selected worksheet. The following code should do the trick:


require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet
cell = ws.Range('B2')
comment = cell.Comment
text = comment.Text

Do you---or would you like to---use comments in your worksheets? I can go into further detail about the Comment object and the Comments collection, but that's all we have time for today.

Thanks for stopping by!

RubyScript2Exe and RubyGems

2009-05-03T15:43:00.005-05:00

I've written here previously about Erik Veenstra's RubyScript2Exe "compiler". I use it frequently to create standalone EXE files on Windows.

If you use RubyScript2Exe, you should note that the current version (0.5.3) doesn't appear to play well with the new version (1.3.2) of Rubygems released a few weeks ago. Attempting to compile a script results in the error:


    undefined method `list' for Gem::Specification:Class

Some additional discussion can be found in this Ruby Forum thread.

Perhaps Erik will update RubyScript2Exe to utilize the Gem.loaded_specs method, as suggested by Eric Hodel.

In the meantime, RubyGems 1.3.1 continues to work well for me with RubyScript2Exe. Be aware, however, that Daniel Berger points out in the above thread that version 1.3.2 addresses a couple issues related to the win32-file library.

There are alternatives emerging for creating executables from your Ruby code, including ocra and crate. I haven't had the opportunity to try either of these yet, but plan to in the near future. If you have experience with these or similar tools, feel free to share your comments here. Thanks!

WIN32OLE Objects: Class Names and Methods

2009-02-28T15:00:00.001-05:00

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 Excel with Ruby: One Cell, Multiple Fonts

2009-01-28T08:18:00.005-05:00

As a loyal reader mentions in the comments to my article about Formatting Worksheets, he wanted to apply different formatting to separate words in a single cell. Specifically, he had cells containing a surname, a comma, and then first name, such as:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

His goal was to format the last name in bold, while leaving the first name in normal weight font:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

(BTW, the sample names are mine. How many days until Opening Day? Or at least until pitchers and catchers report? But, alas, I digress...)

My suggested solution was to use the Characters object to get a reference to a specific subset of characters within the cell, rather than to all of the cell's text.

Call the Characters(start, length) method on a Range object (usually a cell) to return a reference to the specific characters. Note that the Characters object's index is 1-based.

Once you have your subset of characters you can apply Font formatting much like you would with a Cell object, setting the Font object's Name, Size, Bold, ColorIndex, and other properties.

So, assuming you have a worksheet object, ws, and you want to format the values in cells A1 through A3 as described above, try the following code:


ws.Range('A1:A3').Cells.each do |cell|
    # find the index of the first comma:
    comma = cell.Value.to_s.index(',')
    # get the range of characters before the comma
    lastname = cell.Characters(1, comma)
    # get the range of characters after the comma
    firstname = cell.Characters(comma + 2, cell.Value.to_s.size)
    # apply formatting:
    lastname.Font.Bold = true
    firstname.Font.Bold = false
end

There you have it. Let me know if you have questions or comments.

Thanks for stopping by!

Amen, Brother

2009-01-21T08:32:00.008-05:00

In an article entitled "The Best Environment for Rails on Windows", Fabio Akita writes:

The same goes for Windows: some Railers would say that you need to give up on Windows completely and simply go to Ubuntu or Mac OS X. I know for a fact that there are lots of people simply unable to do just that. And “give up their jobs” – as some kindly suggest – is not an option. Some of the people that are starting Rails in closed-minded companies are exactly the seeds those companies need to start to change their minds and every time we, Railers, tell them to just move to another job, we are killing a precious opportunity to introduce Rails to those companies.

In a followup comment to a post on Rails Inside, Fabio adds:

Please keep this in mind: there are several circumstances that keep people locked to Windows. Specially if this person works for a closed minded company that won't allow him to use Ubuntu. The instant you tell this person "give up, Ruby is unable to be used under Windows", you lose the single person that could actually bootstrap Ruby adoption inside this company. People have to start somewhere and sometimes Windows is this way. So let's help them get started and one of three things will happen: 1) he will be super happy and will evangelize Ruby to his peers, leading to mass adoption on this company; 2) he will lose hope on his employer and will try to find another job; 3) he will simply give up and none of us lose nothing.

You should read Fabio's article and the comments at both links above; even if, like me, you don't use Rails as much as you use Ruby itself.

It's very easy (and very fashionable -- all the cool kids do it!) for the Mac OS and Linux crowds to take shots at the Windows developers. And I tip my hat to those that actually changed jobs just so that they could avoid working with Windows. Good for you.

But some of us actually like our jobs and our companies, despite having to work with -- Gasp! Cover the children's ears, Mother! -- Windows. Besides, as Marcos Ricardo commented on Fabio's site, "We can never throw way 90% of market share."

Automating Word with Ruby: Adding Bookmarks

2008-11-30T12:00:00.002-05:00

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!

Scripted GUI Testing with Ruby

2008-09-27T13:18:00.002-05:00

Anyone who reads this blog knows that I love Ruby, and I love Automation.

Ian Dees is wise enough to realize that automation is not the Ultimate Solution to Everything, pointing out that "some domains are better suited than others for automation". "So," he asks, "why not let the computers and people each do what they're good at?" To that end, he offers his new book "Scripted GUI Testing with Ruby", a book for testers who code and coders who test -- and maybe for others, as well.

As the title implies, test scripts are written in the Ruby language -- and its Java implementation, JRuby -- and the author assumes that readers will have some experience with Ruby. If you've written and run a few Ruby scripts, you'll be fine. Ian doesn't require you to be a black-belt Rubyist to understand what's going on here, and his humor helps keep it interesting.

Ian's guinea pig for client-side testing is LockNote, a simple text editor that saves your notes with password-protected encryption. The program is freely available for Windows, and Ian has developed his own cross-platform Java/Swing version, dubbed "JunqueNote". Using these two applications, Dees teaches us how to automate testing of GUI applications on both the Windows and Java platforms. You'll learn how to launch the app and use API calls to find windows, automate keystrokes and mouse-clicks, and more. This can be valuable as either a means to an end, or as the end goal itself; whether you're testing software, or simply looking to automate it via the user interface.

In one chapter, Dees provides a gentle introduction to the popular RSpec Behaviour Driven Development framework. In another, he shows how we can simplify our test code by separating out the common code from the platform-specific code.

Because the focus is on software testing, the author devotes a chapter to leveraging randomness to expose bugs that might otherwise be missed. Another chapter focuses on the ZenTest test matrix library. A later chapter delves into testing web-based applications using Selenium and WATIR, and how to combine these with RSpec.

As a Ruby on Windows advocate, I'm pleased to see a book that devotes more than just a half-dozen pages to Windows-specific task automation. But "Scripted GUI Testing with Ruby" spends a good deal of time discussing Java-based testing, as well.

This book is targeted at software testers, and they'll certainly be the section of the market that gets the maximum value from it. But it has potential value beyond that niche. There's something useful to be learned by both testers and non-testers, on both Java and Windows platforms.

Thanks for stopping by!

Automating PowerPoint with Ruby

2008-08-31T08:56:00.001-05:00

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

2008-07-06T10:00:00.003-05:00

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!

Ruby on Windows: The Book

2008-06-30T07:19:00.002-05:00

A reader recently commented:

"Have you written a book on Ruby + Windows? I'd love to see the material expanded and put down on paper. I suppose I can print it out myself, but it's just not the same."

I've been asked about this before. While I currently have no such "Ruby on Windows" book to offer, I can now say that I have finally started on it.

I welcome any comments, suggestions, or advice you may have -- as readers and/or writers.

Automating Excel: Creating Charts

2008-06-22T08:00:00.003-05:00

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!

Automating Excel: Chart, Axis, and Legend Titles

2008-06-15T03:00:00.002-05:00

A reader writes, "I need to use Ruby to automate Excel... How can I set the chart title, axes title, or legend name, etc in the chart by using Ruby?" I confess that my knowledge of Excel charts is limited, but let's dive in and see what we can learn...

As usual, you'll start by using the win32ole library. For our example, we'll connect to a running instance of Excel:


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

Referencing a Chart Object

Let's assume that ws is your worksheet object. You can obtain a reference to a Chart object either...

...via the Charts collection, if it is a chart sheet...


chart = ws.Charts(1).Chart

or via the ChartObjects collection, if it is an embedded chart (not a chart sheet)...


chart = ws.ChartObjects(1).Chart

...or via the ActiveChart method if the chart is the currently active object:


chart = xl.ActiveChart

Setting the Chart Title

In order to set the value of a chart's title, you must ensure that the HasTitle property is enabled:


chart.HasTitle = true

Then set the value of the ChartTitle.Text property:


chart.ChartTitle.Text = "2008 Sales"

Setting the Axes Title

Select one of the chart's axes by calling the Chart object's Axes method and passing it an integer representing either the category, value, or series axis:


XL_CATEGORY = 1
XL_VALUE = 2
XL_SERIESAXIS = 3
axis = chart.Axes(XL_CATEGORY)

Much like with the chart title above, in order to set the value of an axis' title, you must ensure that the HasTitle property is enabled:


axis.HasTitle = true

Then set the value of the ChartTitle.Text property:


axis.AxisTitle.Text = "July Sales"

Setting the Legend Names

You can reference a legend via the Chart object's SeriesCollection method, passing it the (1-based) index of the item you wish to reference:


chart.SeriesCollection(1).Name = "Jan"
chart.SeriesCollection(2).Name = "Feb"
chart.SeriesCollection(3).Name = "Mar"

And there you have it. If you found this to be useful, you may want to check out this Microsoft MSDN Chart object reference.

Got a question, comment, or suggestion? Post your comment here or send me an email.

Thanks for stopping by!

Running In A Console Window?

2008-06-03T19:23:00.000-05:00

Someone recently asked me how to determine if your code is running in a console window or not. Perhaps you have a program that provides both GUI and command line interfaces and you need to know which interface is being used. You may wish to output error messages to the console, if it exists, but to a file if the console doesn't exist.

UPDATE: Perhaps the preferred means for this is the STDIN.isatty method, which returns true if running in a console window and false otherwise...


if STDIN.isatty
    # do some command-line stuff
else
    # do some GUI stuff
end

Thanks to Brent and Dan for their comments.

Alternatively, Nobu Nakada mentioned in a recent Ruby Forum post that you cannot open the CONIN$ (console input) device file unless you are running in a console window. So the following method (adapted from Nobu) would return true if running in a console window and false otherwise...


def in_console?
    begin
        open("CONIN$") {}
        console = true
    rescue SystemCallError
        console = false
    end
    return console
end

Usage example:


if in_console?
    # do some command-line stuff
else
    # do some GUI stuff
end

There you have it. Got a question or suggestion? Post a comment or send me email.

Thanks for stopping by!

Shutting Down Windows from Ruby Code

2008-04-21T22:30:00.000-05:00

Someone recently asked how to shut down or restart Windows from their Ruby code. Windows provides an executable, "shutdown.exe", to accomplish this. Execute this command from your script with the appropriate parameters and you can log off, shutdown, or restart the OS.

Parameters accepted include, but are not limited to:


-l        Log off (cannot be used with -m option)
-s        Shutdown the computer
-r        Shutdown and restart the computer
-t xx     Set timeout for shutdown to xx seconds
-f        Forces running applications to close without warning

So, the following line of code will close all applications without a warning and instantly shut down and restart your PC:


system('shutdown.exe -r -f -t 0')

To get the full list of parameters, open a console window and enter


shutdown.exe /?

Further details can be found in this Microsoft article. And Gordon Thiesfeld offers WMI and win32ole alternatives in this Ruby Forum thread.

Thanks for stopping by!

Win32OLE Library for JRuby?

2008-04-02T18:15:00.002-05:00

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?

Windows: Ruby's Red-Headed Stepchild

2008-03-29T20:51:00.013-05:00

In case you missed it, there has been increased online discussion these past few days regarding the viability of Windows as a development platform for Ruby. This was triggered by at least two blog posts...

Luis Lavena:

"Is Windows a supported platform for Ruby? I guess not"

Peter Cooper:

"Is Windows a first class platform for Ruby, or not?"

"All the cool kids are doing it."

I've been disappointed by some of the readers' comments to these posts. As in so many discussions within the open source community, there's a significant anti-Microsoft sentiment. There are many reasons to dislike Microsoft and Windows. But those who wonder aloud if anyone still uses Windows, or that comment that "real programmers don't use Windows" are either tragically ignorant or blinded by their passion for Linux and/or MacOS. Yes, I know, we're wandering back towards the Ruby in the Enterprise debate.

“The race is not always to the swift, nor the battle to the strong, but that's the way to bet.” -Damon Runyon

Fortunately, there are voices of reason in this discussion, including Peter Cooper, who, while not a Windows user, says "a poor Windows Ruby ecosystem could harm the growth and spread of the language". Whether you like it or not, Windows is far and away the most popular desktop OS. Many desktop developers in the [insert Darth Vader/Evil Empire theme here] Corporate World are required to develop for Windows because that's where the users are. We might be able to "sneak Ruby through the system", but expecting a company to switch hundreds or thousands of users from Windows to Linux or Mac is childish.

If you love Ruby, and care about the future of The Language, then you want Windows to be a "first-class platform for Ruby". It's not about developer preferences. It's about market forces.

Ruby is more than just Rails.

Software development is more than just websites.

Ruby needs Windows more than Windows needs Ruby.

Think about it.

RubyGarden Archives: Scripting Excel

2008-01-27T11:05:00.000-05:00

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. The website has recently dropped off the face of the earth. The following "Scripting Excel" article was salvaged from the Google cache and is provided here in its entirety.

Although the Pickaxe book has some examples of scripting Excel, I still had quite a lot of digging to do for some of the scripts I needed. I got some by looking at the M$ docs (poor as they are**), and some by looking at Perl examples. So this page is for collecting examples of code that others might adapt (and add to).

See also: ScriptingOutlook, ScriptingAccess

o You can download the Office XP Excel Object Reference help file as part of an expanded help bundle here [1]. It's large [19MB], but it includes a bunch of VBA help files that I couldn't track down otherwise. The file you need for Excel items is VBAXL10.CHM. It appears to have complete docs on all the Excel objects. -- ChrisMorris

o A great resource for general Excel and programming related questions (well with VBA) are Chip Pearsons [Excel Pages]. I learned a lot from his article [Cell References In A Range] for example. -- BernhardLeicher

First of all:


require 'win32ole'

Opening spreadsheets, accessing workbooks and worksheets


excel = WIN32OLE::new('excel.Application')
workbook = excel.Workbooks.Open('c:\examples\spreadsheet.xls')
worksheet = workbook.Worksheets(1) #get hold of the first worksheet
worksheet.Select  #bring it to the front -need sometimes to run macros, 
#    not for working with a worksheet from ruby
excel['Visible'] = true #make visible, set to false to make invisible 
#    again. Don't need it to be visible for script to work

reading data from spreadsheet


worksheet.Range('a12')['Value']  #get value of single cell
data = worksheet.Range('a1:c12')['Value'] #read into 2D array

finding the first empty row (using empty column A)


line = '1'
while worksheet.Range("a#{line}")['Value']
   line.succ!
end #line now holds row number of first empty row

or to read as you go


line = '1'
data = []
while worksheet.Range("a#{line}")['Value']
   data << worksheet.Range("a#{line}:d#{line}")['Value']
   line.succ!
end

writing data into spreadsheet, example


worksheet.Range('e2')['Value'] = Time.now.strftime 
    '%d/%m/%Y' #single value
worksheet.Range('a5:c5')['Value'] = ['Test', '25', 'result']

loading all Excel constants into a class


class ExcelConst
end
WIN32OLE.const_load(excel, ExcelConst)

Now the constant xlDown is accessible as


ExcelConst::XlDown

To find out what constants to use you can use this script. You run it by passing in a string which is matched against the constant names.


require 'win32ole'

module ExcelConsts
end

excel = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(excel, ExcelConsts)
excel.quit()

puts 'Matches for: ' + ARGV[0]
ExcelConsts.constants.each {|const|
    match = const.match(/#{ARGV[0]}/)
    value = eval("ExcelConsts::#{const}")
    puts ' '*4 + const + ' => ' + value.to_s unless match.nil?
}

An example would be looking for the constant to center text. I ran


ruby search_excel_consts.rb Center

and the following results came up:


XlCenterAcrossSelection => 7
XlVAlignCenter => -4108
XlCenter => -4108
XlLabelPositionCenter => -4108
XlPhoneticAlignCenter => 2
XlHAlignCetner => -4108
XlHAlignCenterAcrossSelection => 7

calling macros


excel.Run('SortByNumber')

Setting background colour


worksheet.Range('a3:f5').Interior['ColorIndex'] = 36 #pale yellow
# Set background color back to uncoloured (rnicz)
worksheet.Range('a3:f5').Interior['ColorIndex'] = -4142 # XlColorIndexNone constant
# or use Excel constant to set background color back to uncoloured
worksheet.Range('a3:f5').Interior['ColorIndex'] = ExcelConst::XlColorIndexNone

Adding Formulae


emptyRow = 15
worksheet.Range("t#{emptyRow}")['Formula'] = "=(Q#{emptyRow}+L#{emptyRow}+I#{emptyRow}+S#{emptyRow})"

saving changes


workbook.Close(1)
# or
workbook.SaveAs 'myfile.xls'
# default path is the system defined My Documents folder

ending session


excel.Quit

If you're experimenting from within irb and are having problems with processes hanging around after you've called excel.Quit - try deleting the reference to excel and invoking the garbage collector.


excel.Quit
excel = nil
GC.start

Hopefully this is of some use. Please add anything else you have discovered.

Some further stuff that I learned so far...
It partly overlaps with what ChrisMorris already wrote, maybe we can merge it later on. -- BernhardLeicher

Start Excel, create new workbook and save it:


require 'win32ole'
excel = WIN32OLE.new("excel.application")
excel.visible = true     # in case you want to see what happens 
workbook = excel.workbooks.add
workbook.saveas('c:\examples\spreadsheet1.xls')
workbook.close

Or, suppose Excel is already started and a few Excel files are opened (=workbooks in Excel jargon): Connect to the running instance of Excel, activate one of the workbooks and write something.

This also shows that Excel collections can be iterated very handy with "each", and that collections can sometimes be indexed by number or by name:


excel = WIN32OLE.connect("excel.application")
excel.workbooks.each{|wb|puts wb.name}  # loop through workbooks and display names
excel.workbooks(1).activate             # activate by number
excel.workbooks("Mappe1").activate      # or by name     
excel.range("b5").value="soso"          # write something to cell B5

Connecting to Excel is particularly good fun when done interactively from irb: You instantly see what happens!


irb(main):001:0> require 'win32ole'
true
irb(main):002:0> excel=WIN32OLE.connect('excel.application')
#
irb(main):003:0> excel.workbooks.each{|wb|puts wb.name}
PERSONL.XLS
Mappe1
Mappe2
Mappe3
nil
irb(main):004:0> excel.workbooks(1).name
"PERSONL.XLS"
irb(main):005:0> excel.workbooks("Mappe1").activate
true
irb(main):006:0> excel.range("b5").value="soso"
nil

Excel => workbook => worksheet => range(cell)

What always bugged me when browsing through examples were the various ways of referring to a particular cell on a particular worksheet in a particular workbook. When Excel is started and "Mappe1" is the currently active workbook and "Tabelle1" is the currently active worksheet, all following statements do the same thing:


excel.workbooks("Mappe1").worksheets("Tabelle1").range("a1").value
excel.worksheets("Tabelle1").range("a1").value
excel.activeworkbook.activesheet.range("a1").value
excel.activesheet.range("a1").value
excel.range("a1").value

My confusion was probably caused by the fact that a lot of properties/methods can be called on "excel" directly and then default to the currently active workbook/worksheet. It's more a matter of taste to specify "activesheet" and "activeworkbook" or not.

And regarding the hierarchy, it seems to be as simple as: When Excel is up and running, it contains 0 (no file opened) or more workbooks (Workbook? Just Excel jargon for an Excel file!), with each workbook/file containing 1 or more worksheets.

Various methods for addressing a cell or range of cells

In the Excel object model there isn't something like a cell object, it's all covered by the "Range" object. A range can represent only one cell or a whole bunch of them (a column, a row, a rectangular block of cells, ....).

Let's assume for the following examples, that "sheet" contains a reference to an Excel worksheet, obtained e.g. by:


require 'win32ole'
excel = WIN32OLE.connect('excel.application')   # connect to running instance of Excel
sheet = excel.activesheet

sheet.range(cellname/cell[, cellname/cell])

A range can be obtained by using the worksheet's Range property. A range with only one cell:


sheet.range("a1")

Or a rectangular block of cells (A1 to C3):


sheet.range("a1", "c3")

Same with one argument:


sheet.range("a1:c3")

Whole Column A:


sheet.range("a:a")

Whole Row 3:


sheet.range("3:3")

A range itself has a range property, thus allowing to write:


sheet.range("c3").range("a1").address     # >> "$C$3"

Doesn't make much sense, one might note that the second range's address becomes relative to the first range.


sheet.cells(rowindex, columnindex)

This is all wonderful, but shouldn't there be a way of addressing cells by row and column number? The worksheet's Cells property does that: It gets you a range with one cell by specifying the row and column number. The indices are counted from 1, so Cells(1,1) gives you cell A1:


sheet.cells(3,1).address                  # >> "$C$3"

Combined with range to get range A1:C3:


sheet.range(sheet.cells(1,1), sheet.cells(3,3))

And when applied to another range, row and column index become relative to the first range:


sheet.range("b2").cells(2,2).address      # >> "$C$3"

The index can be negative:


sheet.range("c3").cells(-1,-1).address    # >> "$A$1"
range.offset(rowoffset, columnoffset)

If you have a range, this can be used to return another range that is y rows and x columns away (or offset) from this one. This time the offsets count from 0:


sheet.range("a1").offset(0,0).address      # >> "$A$1"
sheet.range("b5").offset(1,1).address      # >> "$C$6"

While offset somehow reminds of the cells function, this might make a difference: If range contains a block of cells, an offset block of cells is returned too:


sheet.range("a1:c3").offset(0,2).address   # >> "$C$1:$E$3"

Negative offsets can be specified too:


sheet.range("b2").offset(-1,-1).address    # >> "$A$1"

Getting cell values

There isn't only one method for obtaining a cell's value, but at least three of them: text, value, value2. So which should one use, what is the difference between them?

Sidenote:
An Excel cell's content is somewhat relative, what you see isn't necessarily what is actually inside the cell, because a cell's content is displayed according to a specified format. A cell might contain "0.12345", but is displayed as "0.12" or "0.12 DM" or whatever. It might be good to know, that internally a cell's content is either a text or a floating point number. That's it, nothing else.

Just for curiosity:
Dates are represented internally as floating point values too (more details at Chip Pearson's site: HTTP://www.cpearson.com/excel/datetime.htm):³59³ "Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time."
So if the content is 37936.0 and its format is "date", it's displayed as "11.11.03" or "Nov 2003".

For the following examples let's assume a content of:


A1 => 10.12345 => formatted with 2 decimal digits => 10.12
B1 => 10.12345 => formatted as currency => 10,00 DM
C1 => 11.11.03 => date => 11.11.03

range.text

Text property retrieves the value as it is displayed, as a string. It's readonly, so only for getting values. Because my country settings are "German", floats are displayed with a comma.


sheet.range("a1").text      # >> "10,12"
sheet.range("b1").text      # >> "10,12 DM"
sheet.range("c1").text      # >> "11.11.03"

range.value

This is the result when retrieved with value:


sheet.range("a1").value       # >> 10.12345
sheet.range("b1").value       # >> "10,1235"
sheet.range("c1").value       # >> "2003/11/11 00:00:00"

Retrieves the "internal" value (A1 becomes a float), whereby currency and date are still returned as strings albeit somewhat different than before.

range.value2

According to the Excel documentation value2 behaves just like value, but additionally retrieves currency and dates as doubles (the actual internal content):


sheet.range("a1").value2        # >> 10.12345
sheet.range("b1").value2        # >> 10.12345
sheet.range("c1").value2        # >> 37936.0

Yes, seems to work as advertised.

Setting values

Seems that only "value" is useful here. An integer or float arrives as expected as number in Excel:


sheet.range("a1").value = 1.2345
sheet.range("a1").value = 2

For strings Excel does the same processing that it does when something is interactively entered. It thus depends on how Excel interprets the string:


sheet.range("a1").value = "10.11.2003"     # becomes a date
sheet.range("a1").value = "1,2345"           # becomes a number (at least with German country settings)

Iterating ranges...

...with each

Ranges can be iterated with each:


sheet.range("a1:a10").each{|cell|puts cell.value}

If the range is a block of cells the iteration goes from left to right, then down one line, and so on:


sheet.range("a1:b5").each{|cell|puts cell.value}

Iterating block of cells by row and output the first cell of each row:


sheet.range("b3:c7").rows.each{|r|puts r.cells(1,1).value}

and by column:


sheet.range("b3:c7").columns.each{|c|puts c.cells(1,1).value}

Result of row.value is an array within an array:


sheet.range("b3:c7").rows.each{|r|puts r.value.inspect}  # >> [1.0, 10.0]?

...with activecell

Like moving around an Excel sheet with the cursor. Moving down one cell:


sheet.activecell.offset(1,0).activate

Walking down from the active cell until an empty cell is encountered:


sheet.activecell.offset(1,0).activate while excel.activecell.value

...with an index


range = sheet.range("b3:c7")
noofrows = range.rows.count
(1..noofrows).each{|i|puts range.cells(i,1).value}

Named Ranges

Named ranges make Excel spreadsheets more usable for the end user. To create a named range "myRange":


sheet.names.Add( { 'Name' => 'myRange', 'RefersTo' => 'A2:A216' } )

One problem! This doesn't work. Use a Range object for RefersTo?, not a String:


myRange = sheet.Range( 'A2:A216' )
sheet.names.Add( { 'Name' => 'myRange', 'RefersTo' => myRange } )

How do you use named ranges in ruby? Named ranges are kept in the worksheet as well as the workbook. You may need to check both locations.

Something like the following works for named ranges manually defined by the user:


rangeString = workbook.names( 'Sheet1!myRange' ).Value

1. Remove "=" prefix (e.g. "=Sheet1!$A$2:$A$4")


rangeString = rangeString.slice( 1, rangeString.length - 1 ) if ( rangeString =~ /^=/ ) worksheet.range( rangeString ).value = 'testing...'

Finding Data Regions...

Data don't always start in row 1, column A, the number of columns might not be fixed, and the number of rows is most often variable. There are two handy methods that can help to find that "block of data".

Let's assume, that B3:C7 contain data.

...with CurrentRegion

Given any cell inside the "data block", CurrentRegion?() finds the surrounding contiguous data region and returns its range:


sheet.range("b5").currentregion.address   # >> "$B$3:$C$7"

...by "jumping around"

There's a shortcut key in Excel "+", that allows you to jump to the end/start of regions with content. With our example and the cursor in B3, pressing + would jump the cursor to cell B7, pressing that shortcut again would move the cursor to the last line of the spreadsheet to B65536. There's an equivalent method: "End()".

Finding the last row with data:


sheet.range("b3").end(-4121).address   # >> "$B$7"

The parameter indicates the direction, the Excel constants are:


xlDown    = -4121
xlToLeft  = -4159
xlToRight = -4161
xlUp      = -4162

Saving to CSV (or other formats)

Note that Excel can show quite a lot of warnings / confirm request. To supress these:


excel.DisplayAlerts = false

Then:


workbook.SaveAs 'myfile.csv', xlCSV

where xlCSV = 6.

Here are some common file formats:


xlCSV=6
xlCSVMac=22
xlCSVMSDOS=24
xlCSVWindows=23
xlCurrentPlatformText=-4158
xlExcel9795=43
xlTextMSDOS=21
xlTextPrinter=36
xlTextWindows=20

See also: ScriptingOutlook, ScriptingAccess

- How about OpenOffice and Ruby scripting? Anything in this area? The examples all seem to rely on


require 'win32ole'

and I believe this will only work on Windows OSes.

RubyGarden Archives: Scripting Access

2008-01-20T11:53:00.000-05:00

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. That website has recently disappeared. The following "Scripting Access" article was salvaged from the Google cache and is provided here in its entirety.

Here is a quick example on getting data from a Microsoft Access database.

Recordset with a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!


require "win32ole"

conn = WIN32OLE.new("ADODB.Connection")
conn["Provider"] = "Microsoft.Jet.OLEDB.4.0"
conn.Open('c:\ruby\dev\calldata.mdb')

rs = conn.Execute("select date, dialednumber, extension, cost from
    callrecords where call = 2 and date >=#01-jan-2005#")
rs.getrows.each do |row|
    puts row
end

conn.Close

Recordset without a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!


require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")

qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\data\calldata.mdb'

rs.open(qry,constr)

rs.getrows.each do |row|
    puts row
end

Recordset without connection using an ODBC source. My program took about 28MB mem vs. 39MB for the JET version. The difference is massive when working with a lot of data Requires that you create the ODBC data source!


require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")
qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
rs.open(qry,"DSN=calldata;")

rs.getrows.each do |row|
    puts row
end

Here's a function you can drop at the top of your program that alters the definition of Object. It will work for loading access data into any object class that supports "<<" and "transpose" - such as Array. Transpose is required since the recordset comes back with the rows "rotated".


public
def readMDB(qry,dsn)
    rs = WIN32OLE.new("ADODB.recordset")
    rs.open(qry,"DSN=#{dsn};")
    rs.getrows.each do |row|
        self << row
    end
    self.transpose
end

Usage example


qry = "select date, dialednumber, extension, cost from callrecords"
dsn = "DSN=calldata;"

a = []
a.readMDB(qry, dsn)
p a

Question: Is there a way to use paramters on the query like the ones from DBI? E.g.: (note the '?')


select * from callrecords where call = ?

RubyGarden Archives: Scripting Outlook

2008-01-13T10:50:00.000-05:00

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. The website has recently dropped off the face of the earth. The following "Scripting Outlook" article was salvaged from the Google cache and is provided here in its entirety.

With some help of the the ScriptingExcel page and the Office helpfiles mentioned there a short script to list your Outlook messages. The script should be pretty easy to expand. One method that was really helpfull in figuring out what is possible is:


OLEObject.ole_methods

MarkJanssen


require 'win32ole'

myApp = WIN32OLE::new("outlook.Application")

# load Outlook OLE constants

class OutlookConst
end

WIN32OLE.const_load(myApp, OutlookConst)

p "OlMailItem = #{OutlookConst::OlMailItem}"

ns = myApp.GetNameSpace("MAPI")
#ns.Logon # uncomment for online usage
folders = ns.Folders

new_messages = 0

folders.each { 
    | folder |
     puts "+" + folder.Name

    begin
        folder.Folders.each {
            | folder |
            # puts " " + folder.Name
            if ["Inbox","Sent Items"].member? folder.Name
                folder.Items.each { 
                    | msg |
                    if msg['UnRead']
                        new_messages += 1
                    end
                    puts "  From: " + msg['SenderName']
                    puts "  Subject: " + msg['Subject']
                }
            end
        }
    rescue
        
         puts " Unable to open"
    end
}
puts "You have #{new_messages} new message(s)"

Glauber 2003-10-09:

* Here's a quick tip that would have saved me some time: When iterating over a list of Outlook items (e.g.: MailItems) in order to move or delete some of them, you must do so backwards (GetLast... GetPrevious... GetPrevious...). In other words, don't attempt to access an item that's after the one you deleted or moved.
* Here's another tip: there's no good way to find the From Internet email address of a message. Most times, creating a reply and extracting the first recipient for it will work, but sometimes not (it may give you an Exchange X.400 address instead of what you're looking for). Don't send the reply, discard it.

IronRuby, Ruby.NET, and the Differences

2008-01-03T08:25:00.000-05:00

Following on the heels of Pat Eyler's interview of M. David Peterson, Mr. Peterson has posted an article entitled Ruby.NET vs. IronRuby: What's The Difference?.

Microsoft's John Lam has posted a follow-up on his blog.

It's good to see increased public discussion of these two important projects.

Parsing Spreadsheets with the Roo Gem

2008-01-02T19:37:00.000-05:00

I've talked at length about using the win32ole library to automate Microsoft Excel. But there are alternatives for accessing data in Excel spreadsheets -- some of which don't even require Excel to be installed. One of these is the roo gem, which allows you to extract data from Excel, OpenOffice and Google spreadsheets. Roo provides read-only access to Excel and OpenOffice spreadsheets, but both read and write access to Google spreadsheets.

To install the roo gem, including its dependencies, open a console window and enter:


gem install roo -y

Require the roo library in your script:


require 'roo'

To parse an Excel worksheet, we first create an instance of the Excel workbook object by calling the Excel.new method and passing it the path and filename:


xl = Excel.new('C:\my_workbook.xls')

The next step is to define which worksheet in the workbook we will be working with. We do this by setting the Excel.default_sheet value to one of the worksheets in the Excel.sheets array:


xl.default_sheet = xl.sheets[0]

To extract the value from a particular cell, call the cell method, passing it the row number and either the column number or column letter. Examples:


val = xl.cell(3, 5)
val = xl.cell(3, 'E')

The row method returns an array of values from the specified row number, so...


values = xl.row(3)

...returns the values from the third row.

Similarly, the column method returns an array of values from a column:


values = xl.column(5)

Get the full details from the roo homepage, and the roo Rdoc page.

That's all for now. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Ruby.NET/IronRuby Interview With M. David Peterson

2007-12-28T12:06:00.000-05:00

Over on the On Ruby blog, Pat Eyler interviews M. David Peterson about the Ruby.NET project. Peterson comments on the relationships between Ruby.NET, IronRuby, Microsoft, and the larger Ruby community. Check it out.

Automating Outlook with Ruby: Saving File Attachments

2007-12-06T08:22:00.000-05:00

A while back, we looked at accessing the Outlook Inbox and managing messages. A reader recently asked how to iterate over the messages and save file attachments to their hard drive. Specifically, they wanted to save only those file attachments that exceeded 500k in size.

In Outlook, a message (MailItem) object's file attachments are accessed via the Attachments collection:


message.Attachments.each do |attachment|
    # Do something here
end

The Attachment object has few properties and methods, but one you will use is the FileName property, which returns the, uh, filename.

To save the attachment, call its SaveAsFile method, passing it a path and filename:


attachment.SaveAsFile("c:\\attachments\\#{attachment.FileName}")

The attachment object does not offer a method/property for determining the file size. If you were looking to save only files of a certain size, you could save the file, then check the saved file's size and delete it if necessary. This is not optimal, but it does the trick.

So, putting this all together, we'd have something like the following code which iterates over your Inbox messages and saves all attachments of 500,000 bytes or larger:


require 'win32ole'

outlook = WIN32OLE.new('Outlook.Application')
mapi = outlook.GetNameSpace('MAPI')
inbox = mapi.GetDefaultFolder(6)
inbox.Items.each do |message|
    message.Attachments.each do |attachment|
        filename = "c:\\attachments\\#{attachment.FileName}"
        attachment.SaveAsFile(filename)           
        File.delete(filename) if File.size(filename) < 500000
    end
end

That's our show for today. Questions? Comments? Suggestions? Post a comment here or send me an email.

Thanks for stopping by!

The Ruby.NET Compiler: A Little Love

2007-12-02T15:42:00.000-05:00

Amidst all the IronRuby hype, you could be forgiven if you didn't hear mention of the Ruby.NET Compiler. But if you use Ruby on Windows -- and you probably do if you're reading this blog -- you owe it to yourself to at least be aware of the work that Wayne Kelly and friends have been doing.

To quote the Ruby.NET page:

Ruby.NET is a compiler that translates Ruby source code into .NET intermediate code. This allows components implemented using the Ruby language to:

* Natively execute on the .NET platform
* Be linked with third party components developed using other .NET languages such as C#.
* Utilize the extensive resources of the .NET platform including visual design and debugging tools, the security framework and an extensive collection of class libraries used, for example, to create Windows forms, web and database applications.

Ruby.NET version 0.9 was released a couple weeks ago and can be installed via a binary installer from here. It requires the Microsoft .Net Framework version 2.0.

There's an optional Visual Studio integration package, which requires Visual Studio 2005 SDK version 4.0.

I've just started tinkering with this, so I can't provide any meaningful analysis or instruction on it yet. But I like the possibilities that it presents...

* Compile your code to a .Net executable
* Use the .NET framework's Forms library for your GUI
* Leverage the vast array of .NET framework libraries
* Work is currently underway to port the win32ole library to Ruby.NET

I'd like to see more documentation and examples, including Windows forms code samples. If you have Ruby.NET examples you would like to share, feel free to email them to me or post in the comments.

There's also a Ruby.NET Compiler Discussion group here.

So check it out and maybe, as Antonio Cangiano recommends, give Ruby.NET some love. I think this is a project that deserves much more attention.

Find & Replace with MS Word

2007-11-12T19:51:00.000-05:00

While browsing through some DZone Ruby Snippets, I came across this nice little example of using Ruby to automate Find & Replace in Microsoft Word, compliments of Tim Morgan.

You could use such a snippet as part of a mail-merge type solution. To use it, create a form letter in a Word document, with bracketed placeholders where the actual values are to be inserted. Example:


Dear [name],

On [date] account [account_number] was charged a [amount] fee.

Let's take a walk through the code...

Load the win32ole library:


require 'win32ole'

Launch Microsoft Word:


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

Open the template document:


doc = word.Documents.Open('c:\file_to_open.doc')

Iterate over a hash containing keys and values. Each key is the placeholder code and the corresponding value is the text to insert:


{
  'name' => 'Tim Morgan',
  'date' => Date.today.strftime('%B %d, %Y'),
  'account_number' => '123456',
  'amount' => '$45.76'
}.each do |key, value|

Start the search at the beginning of the document:


word.Selection.HomeKey(unit=6)

Grab a reference to the Find object:


find = word.Selection.Find

Set the Find object's text string (the hash key) to locate, surrounded by brackets:


find.Text = "[#{key}]"

For each occurrence of the string found (via the Find object's Execute method), insert the replacement text (the hash value):


while word.Selection.Find.Execute
  word.Selection.TypeText(text=value)
end

Save the document with a new name:


doc.SaveAs('c:\output_file.doc')

Close the document:


doc.Close

The example above shows the hash hard-coded in the script, but you could build on this to load an array of hashes from a text or YAML file, Excel worksheet, or database table.

My thanks to Tim Morgan, who posted the original snippet on Dzone, and to you, for stopping by to read it!

Getting Started with the wxRuby GUI Toolkit

2007-11-11T17:53:00.001-05:00

Many readers have asked "What is the best GUI toolkit?". There is no single answer to this question, and I won't try to lobby for my GUI of choice. Use whatever works best for you and your users.

I use the wxRuby library. It's cross-platform, provides a native look-and-feel, and is easy to install (via a gem) and distribute with your application (via RubyScript2Exe). Further features can be found in the wxRuby wiki here. wxRuby is the Ruby interface to wxWidgets, a very stable and widely-used widget toolkit.

Installing wxRuby is simple using RubyGems:


gem install wxruby

Though you can use a GUI designer, creating most forms "by hand" is probably simpler than you suspect. A useful wxRuby user interface can be created in less than 50 lines of code, as we shall see...

Let's create a simple form with a label, text box, combo box, and button. We start by requiring the wx library and including the Wx namespace:


require 'wx'
include Wx

We create a new class which inherits from the Wx::Frame class and includes an initialize method:


class MyFrame < Frame
  def initialize()
    super(nil, -1, 'My Frame Title')
  end
end

The Frame class' super constructor method takes the following arguments (all but Parent are optional):

Parent: The window parent. This may be NULL. If it is non-NULL, the frame will always be displayed on top of the parent window on Windows.

ID: The window identifier. It may take a value of -1 to indicate a default value.

Title: The caption to be displayed on the frame’s title bar.

Position: The window position. A value of (-1, -1) indicates a default position, chosen by either the windowing system or Widgets, depending on platform.

Size: The window size. A value of (-1, -1) indicates a default size, chosen by either the windowing system or Widgets, depending on platform.

Style: The window style (ie, if the minimize, maximize, and close boxes appear on the Frame).

Further details on the Frame class can be found in the wxRuby wiki here.

Next, let's add the code to the initialize method to create a panel, which will contain the other controls:


@my_panel = Panel.new(self)

The self passed to the new constructor is a reference to the Frame object, which we are passing as the parent of the Panel object.

Next, we'll create a variety of controls, passing the newly created @my_panel as the parent of each control:


@my_label = StaticText.new(@my_panel, -1, 'My Label Text', 
  DEFAULT_POSITION, DEFAULT_SIZE, ALIGN_CENTER)
@my_textbox = TextCtrl.new(@my_panel, -1, 'Default Textbox Value')
@my_combo = ComboBox.new(@my_panel, -1, 'Default Combo Text', 
  DEFAULT_POSITION, DEFAULT_SIZE, ['Item 1', 'Item 2', 'Item 3'])
@my_button = Button.new(@my_panel, -1, 'My Button Text')

I recommend assigning all your form controls to instance variables. This is the reason for the '@' preceding each control variable name.

We want to bind the button click to a "my_button_click" method which we will add to this class later. We do with the evt_button method:


evt_button(@my_button.get_id()) { |event| my_button_click(event)}

Now we'll proceed to the layout: arranging the controls in the Panel. We'll do this using a BoxSizer, which we'll have arrange the controls vertically:


@my_panel_sizer = BoxSizer.new(VERTICAL)
@my_panel.set_sizer(@my_panel_sizer)

Next we add each control to the panel's sizer by calling the sizer's add method:


@my_panel_sizer.add(@my_label, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_textbox, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_combo, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_button, 0, GROW|ALL, 2)

See the Sizer.Add documentation for an explanation of the various parameters.

Our last line of code in the initialize method makes the frame visible:


show()

Don't forget to add the my_button_click method that is called by the button click:


def my_button_click(event)
    # Your code here
end

That concludes our MyFrame class. Now we want to create a MyApp class that will call our MyFrame class:


class MyApp < App
  def on_init
    MyFrame.new
  end
end

Finally, we create a new instance of our MyApp class and call its main_loop method:


MyApp.new.main_loop()

View the complete code here.

There you have it. A complete, but simple, wxRuby form. It's not much to look at...

...but it demonstrates the basics and we'll enhance it shortly.

Soon, we'll cover related topics: dressing up our GUI a little, using the wxSugar extensions, using a Forms designer such as wxFormBuilder.

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

Thanks for stopping by!

RubyConf 2007

2007-11-01T08:07:00.000-05:00

I'll be attending my first RubyConf here in Charlotte this weekend and there's a lot of great stuff on the agenda. In addition, I learned from Bill Plummer that Microsoft is hosting an event Thursday night with John Lam talking about IronRuby. Unfortunately, I will have to miss it but I hope that John will duplicate the best parts of it in his RubyConf talk Saturday morning. Charlie Nutter follows that with his JRuby presentation. Of course, "Writing Client and Desktop Applications in Ruby" by Bruce Williams also caught my eye, as I am a desktop apps developer.

There's been a bit of a lull in my blog postings (and replies to readers) of late, as I've been swamped with work, but I plan to post more frequently going forward. I have a few topics suggested by readers, but welcome any comments, questions, or suggestions you may have. I still feel that Windows is Ruby's red-headed stepchild, with far more potential than the attention given to it. That may change (somewhat) in the future as projects such as IronRuby and the Ruby.Net compiler mature.

And if you're at RubyConf, stop by and say 'Hey!'

Windows XP Visual Style Controls with wxRuby

2007-10-21T16:35:00.000-05:00

wxRuby uses the native Microsoft Windows common controls when displaying widgets on a Windows OS. You may have noticed, however, that the form controls do not have the Windows XP look and feel. They have the flat and 2-dimensional look of earlier Windows versions...

...rather than having the rounded corners and 3-dimensional look of Windows XP...

Note, among other things, the "glowing button" effect when the mouse hovered over the "Get Films Data" button.

This is because Windows XP comes with both version 5 and version 6 of the Common Controls, but unless specifically instructed to use version 6, it will default to using version 5 controls.

So, how do you instruct Windows to use the version 6 controls for your wxRuby GUI application? By including a simple manifest XML file with your application.

Copy the following text into a new text file. Save the file as "rubyw.exe.manifest" in your ruby/bin folder, the same folder that contains rubyw.exe. You might also save a copy to the same folder as "ruby.exe.manifest".


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
    <assemblyIdentity version="1.8.6.0" processorArchitecture="X86" 
        name="Microsoft.Winweb.Ruby" type="win32"/>
        <description>Ruby interpreter</description>
        <dependency>
         <dependentAssembly>
                <assemblyIdentity 
                    type="win32" 
                    name="Microsoft.Windows.Common-Controls" 
                    version="6.0.0.0" 
                    processorArchitecture="X86" 
                    publicKeyToken="6595b64144ccf1df" 
                    language="*"
                />
        </dependentAssembly>
    </dependency>
</assembly>

When the ruby.exe or rubyw.exe interpreter is run, Windows will look for a manifest file matching the program name in the same folder. When it finds our manifest file, it will then use the version 6 controls, as defined in the <dependency> section of the manifest.

We place the file in our c:\ruby\bin directory because that is where ruby.exe and rubyw.exe are located. We name the file ruby.exe.manifest or rubyw.exe.manifest to match the name of the interpreter being run.

If you are compiling your application with RubyScript2Exe, copy the manifest file into the folder with your script, then embed the manifest file in your compiled executable using the RUBYSCRIPT2EXE.bin statement:


RUBYSCRIPT2EXE.bin = ["rubyw.exe.manifest"]

When your compiled executable is run, your manifest file will be extracted to the same temporary "/bin" folder as the rubw.exe (or ruby.exe) interpreter.

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

Thanks for stopping by!

Hide & Seek: Using NTFS Alternate Data Streams

2007-10-07T17:25:00.000-05:00

The NTFS file system utilized by Windows (NT and later) includes support for Alternate Data Streams (ADS). ADS was implemented in NTFS to provide compatibility with Apple's Macintosh Hierarchical File System (HFS), which uses resource forks to store icons and other information for a file, but such streams can be used to store any type of data that a normal file would store. You could, for example, use an ADS to store metadata about the file to which the ADS is attached. You could even store binary data inside an ADS. This might be useful for storing backup versions of a file before making changes.

To work with an alternate data stream for a file, simply append a colon and the stream name to the filename. The following code appends a stream named 'stream1' to file.txt:


open('file.txt:stream1', 'w') do |f|
    f.puts('Your Text Here')
end

If file.txt did not exist, it would have been created and would be 0 bytes in size, since no data was written to the main stream. But you can append streams to an existing file, as well. The reported size of the file would not change, though it now includes alternate data streams of embedded data.

Reading from a stream is just as simple:


stream_text = open('file.txt:stream1').read

Alternate data streams can also be used for storing binary data, including executables:


bytes = open('MyApp.exe', 'rb').read
open('file.txt:MyApp.exe', 'wb') do |f|
    f.write(bytes)
end

bytes = open('file.txt:MyApp.exe','rb').read
open('MyApp2.exe', 'wb') do |f|
    f.write(data)
end

A file can contain multiple data streams, so you could, for example, include a data stream for text and another stream for binary data.

Another possible use for ADS would be to create a backup of the file which could be restored later:


def create_backup(filename)
    open("#{filename}:backup", "wb") do |f|
        f.write(open(filename, "rb").read)
    end
end

def restore_backup(filename)
    if not File.exist?("#{filename}:backup")
        puts("Backup stream does not exist!")
        return
    end
    backup = open("#{filename}:backup", "rb").read
    open("#{filename}", "wb") do |f|
        f.write(backup)
    end
end

Note: A file's alternate data streams will be preserved on the NTFS disk, but would be stripped off of a file when copied to a non-NTFS disk, such as a flash drive or CD/DVD disk; or when a file is copied via ftp. For this reason you may not want to rely on alternate data streams for storing critical data.

There you have it. Sound interesting? Further details on Alternate Data Streams can be found here and here.

Thanks for stopping by!

Using Ruby & SQL-DMO to Automate SQL Server Tasks

2007-09-30T11:30:00.000-05:00

If you work with Microsoft SQL Server, you might like to know that you can automate many of your administrative tasks by leveraging Distributed Management Objects (SQL-DMO). SQL Server's Enterprise Manager is based on DMO, so most (if not all) of what you do through the Enterprise Manager interface can be automated through SQL-DMO -- and Ruby. Create, manage, and backup databases, tables, views, jobs, stored procedures, and more. Let's take a look at some of the available objects and Ruby code that uses them.

We start by creating an instance of the SQLServer object:


require 'win32ole'
server = WIN32OLE.new('SQLDMO.SQLServer')

To connect to your server using SQL Authentication, call the Connect method providing the server IP address or name, the login, and password:


server.Connect('127.0.0.1', 'login', 'password')

To connect using Windows Authentication, set the LoginSecure property to true, then call the Connect method with just the server IP address or name:


server.LoginSecure = true
server.Connect('127.0.0.1')

When you're done, be sure to close your connection by calling the Disconnect method:


server.Disconnect

The Databases method returns a collection of Database objects. You can access a specific Database object by passing the database name to the Databases method:


database = server.Databases('my database')

The Database object includes dozens of methods and several collections, including Tables, Views, and StoredProcedures. This code, for example, prints the names of all tables:


for table in database.Tables
    puts table.Name
end

Furthermore, many database objects expose a Script method that returns the SQL code for creating that object. This little block of code saves the SQL for creating all your Table objects:


for table in database.Tables
    File.open("#{table.Name}.sql", "w") do |f|
        f.puts table.Script
    end
end

The following block of code writes the text of each stored procedure to a text file:


database.StoredProcedures.each do |sp|
    File.open("#{sp.Name}.txt", "w") do |f|
        f.puts sp.Text
    end
end

The SQL Server Agent service, which runs and manages jobs, is accessed via the JobServer object. The following code starts and stops this service:


server.JobServer.Start
server.JobServer.Stop

The JobServer object's Jobs method returns a collection of all SQLServer Agent jobs:


for job in server.JobServer.Jobs
    puts "Name: #{job.Name}\nDescription: #{job.Description}\n\n"
end

I have a script that iterates over the Jobs collection and creates a "jobs inventory", an Excel workbook listing each job's name, description, frequency (daily, weekly, etc.) and start time.

Further details on SQL-DMO can be found in this MSDN article, and in this SQL Team article.

That's all for today, but if you use SQL Server, perhaps this has given you some ideas on how to use your Ruby skills to make SQL Server admin tasks a little easier.

UPDATE: You may also be interested in this earlier article about using Ruby and ADO for your SQL Server queries.

Feel free to post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Using Rake to Automate Windows Desktop App Builds

2007-09-26T08:35:00.000-05:00

We've recently talked about compiling your Ruby app with RubyScript2Exe, and creating an install package with Inno Setup.

I was inspired by RoW reader Luis Lebron, who recently shared with me his Rakefile for automating the running of RubyScript2Exe from within the NetBeans Ruby IDE.

My process for packaging an application for distribution involves, among other things:

Compiling the code with RubyScript2Exe
Replacing the default EXE icon with my own (using Resource Hacker)
Moving the EXE file to my Install folder
Updating the ReadMe file with the new version number
Updating the Inno Setup script with the new version number
Running the Inno Setup script to create a new Setup.exe file

This isn't a particularly time-consuming list of manual tasks. But I spend my workdays developing tools to automate my fellow employees' manual tasks. So it was inevitable that I would seek to do the same for myself.

I had no prior experience with Rake, a DSL created by Jim Weirich to automate project builds. I'm neither a C programmer nor a web developer, so I wasn't really motivated to investigate what Rake might have to offer. But after receiving Luis' email, I looked into using Rake, and then researched the options for running Resource Hacker and the Inno Setup compiler from the command line.

I still know almost nothing about Rake, but I learned enough to create a simple Rakefile to automate all of the above tasks. This could, of course, be done in Ruby without the using Rake, but NetBeans' Rake integration and templates make it handy. Now I can right-click on my project icon in NetBeans, select Run Rake Task => create_setup, and Rake takes care of the rest.

In case you're interested, here's an example of my Rakefile. It can no doubt be improved upon, but should give you an example to start from (Beware of line-wrap):


require 'rake'
require 'fileutils'
include FileUtils

# set constant values:
LIB_FOLDER = File.expand_path('../lib')
INSTALL_FOLDER = File.expand_path('../install')
ISCC = "C:/Program Files/Inno Setup 5/iscc.exe"
RESHACKER = "C:/Program Files/ResHacker/ResHacker.exe"
ISS_FILE = "#{INSTALL_FOLDER}/Setup.iss"
README_FILE = "#{INSTALL_FOLDER}/ReadMe.txt"

# extract values from main.rb file:
main_rb = open('../lib/main.rb').read
APP_TITLE = main_rb.scan(/APP_TITLE = '(.+)'/)[0][0]
EXE_NAME = main_rb.scan(/EXE_NAME = '(.+)'/)[0][0]
EXE_BASENAME = EXE_NAME.gsub('.exe', '')
APP_VERSION = main_rb.scan(/APP_VERSION = '(.+)'/)[0][0]

# rake tasks:
task :default => [:create_setup]

desc "Create setup.exe"
task :create_setup => [:move_exe, :modify_icon, :create_iss_script, 
    :edit_readme] do
    puts "Creating setup.exe"
    Dir.chdir(INSTALL_FOLDER)
    system(ISCC, ISS_FILE)
end

desc "Create ISS script"
task :create_iss_script => [:move_exe] do
    puts "Creating ISS script"
    Dir.chdir(INSTALL_FOLDER)
    data = ISS_TEXT.gsub('[APP_TITLE]', APP_TITLE)
    .gsub('[APP_VERSION]', APP_VERSION)
    .gsub('[EXE_NAME]', EXE_NAME)
    .gsub('[EXE_BASENAME]', EXE_BASENAME)
    File.open(ISS_FILE, 'w') do |f|
        f.puts(data)
    end
end

desc "Edit ReadMe.txt"
task :edit_readme do
    puts "Updating ReadMe.txt file"
    Dir.chdir(INSTALL_FOLDER)
    txt = nil
    open(README_FILE) do |f|
        txt = f.read
    end
    old_version = txt.scan(/Version (\d\d\.\d\d\.\d\d)/)[0][0]
    txt = txt.gsub(old_version, APP_VERSION)
    File.delete(README_FILE)
    open(README_FILE, 'w') do |f|
        f.puts(txt)
    end
end

desc "Modify EXE icon"
task :modify_icon => [:move_exe] do
    puts "Modifying EXE icon"
    Dir.chdir (INSTALL_FOLDER)
    arg = " -addoverwrite #{EXE_NAME}, #{EXE_NAME}, application.ico, 
          icongroup, appicon, 0"
    system(RESHACKER + arg)
end

desc "Move EXE to install folder"
task :move_exe => [:compile_code] do
    puts "Moving EXE to install folder"
    mv("#{LIB_FOLDER}/main.exe", "#{INSTALL_FOLDER}/#{EXE_NAME}")
end

desc "Compile code into EXE"
task :compile_code do
    puts "Compiling main.rb into EXE"
    system("rubyscript2exe.cmd", "#{LIB_FOLDER}/main.rb")
end

# text of Inno Setup script:
ISS_TEXT =<<-END_OF_ISS

[Setup]
AppName=[APP_TITLE]
AppVerName=[APP_TITLE] version [APP_VERSION]
AppPublisher=David L. Mullet
AppPublisherURL=http://davidmulletcom
AppContact= david.mullet@gmail.com
AppVersion=[APP_VERSION]
DefaultDirName=C:\\[APP_TITLE]
DefaultGroupName=[APP_TITLE]
UninstallDisplayIcon={app}\\[EXE_NAME]
Compression=lzma
SolidCompression=yes
OutputDir=.
OutputBaseFilename="[EXE_BASENAME]_Setup"

[Files]
Source: "[EXE_NAME]"; DestDir: "{app}"; Flags: ignoreversion
Source: "Readme.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\\[APP_TITLE]"; Filename: "{app}\\[EXE_NAME]"; 
    WorkingDir: "{app}"
Name: "{group}\\View ReadMe File"; Filename: "{app}\\ReadMe.txt"; 
    WorkingDir: "{app}"

END_OF_ISS

As always, post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Installing Your Ruby App with Inno Setup Installer

2007-09-23T08:23:00.000-05:00

In my last article, we looked at 'compiling' your Ruby app into a single portable executable file using RubyScript2Exe.

As a reader commented, "For windows it becomes so important to make things 'one click'. I guess the next step would be to also wrap this in some kind of simple installer script to store it in the right place and add the appropriate menu/desktop icons."

Which brings us to the topic of today's discussion: using Inno Setup to create an Install program (ie, setup.exe) for your Ruby applications.

Inno Setup, created, by Jordan Russell, has been around for about a decade, is very well documented, and frequently updated. It's very customizable and extendable, and allows you to create professional installation packages that will allow you to create directories, install files to multiple locations (install folder, Windows system folder, etc.), create Start menu Program groups and icons, and place shortcut icons on the user's Desktop and Quick Launch bar. And it's free of charge.

Inno Setup creates your Setup.exe file based on parameters that you define in a text-based script. Your ISS script file is divided into sections, with parameter=value pairs, much like a Windows INI file. Starting out, you'll probably use the Inno Setup user interface to create and edit your scripts, and then to compile your installation package into a setup.exe and run it.

Section names may include [Setup], [Files], [Icons], and [Code]. Inno Setup includes a comprehensive help file detailing all of the options, and there is an extensive FAQ document on the website.

A typical ISS script might look something like this:


[Setup]
AppName=My Ruby Application
AppVerName=My Ruby Application version 09.23.07
AppPublisher=David Mullet
AppPublisherURL=http://davidmullet.com
AppContact=david@davidmullet.com
AppVersion=09.23.07
DefaultDirName=C:\My Ruby Application
DefaultGroupName=My Ruby Application
UninstallDisplayIcon={app}\MyRubyApp.exe
Compression=lzma
SolidCompression=yes

[Files]
Source: "MyRubyApp.exe"; DestDir: "{app}"; Flags: ignoreversion
Source: "ReadMe.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\My Ruby Application"; Filename: "{app}\MyRubyApp.exe"; WorkingDir: "{app}"
Name: "{group}\View ReadMe File"; Filename: "{app}\ReadMe.txt"; WorkingDir: "{app}"

Inno Setup is highly customizable. You can, for example, customize the install wizard, or include a [Code] section with Pascal procedures (see the Help file for details and examples). The scripts are straight text, and the compiler can be called from the command line, so you can create and compile your ISS script all within Ruby, if you like, such as in a Rakefile. I'll include just such an example in an upcoming article.

So, you can now create a single executable file for your Ruby application and use a professional-looking installer to distribute it.

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

Thanks for stopping by!

Compiling Your Ruby App with RubyScript2Exe

2007-09-18T22:43:00.000-05:00

I've mentioned RubyScript2Exe previously. This tool allows you to 'compile' a script/application into a portable executable file (EXE) that you can easily provide to your users without requiring them to install Ruby and the required libraries. RubyScript2Exe traces and gathers all the necessary files, including the Ruby interpreter, and 'compiles' them into a single EXE file. You can easily embed images and icon files, and DLLs such as SQLite.

I put the word 'compile' in quotes above because RubyScript2Exe does not transform your code as a C compiler or .Net compiler would. Rather, it collects all the files necessary to run your application and bundles them into a single EXE file. When the user runs that EXE file, that bundle is quickly extracted to a temporary file and your Ruby code is executed.

Installing RubyScript2Exe is as easy as falling off a log, thanks to RubyGems. Just get to a command prompt and enter:


gem install rubyscript2exe

Include the following require statement at the top of your script:


require 'rubyscript2exe'

Whenever possible, include all your require statements at the top of your script. This ensures that RubyScript2Exe successfully traces and includes all the necessary files your application will need.

If you are compiling a non-console script and therefore want to use the rubyw.exe interpreter, rather than the ruby.exe interpreter, include the following module variable near the top of your script:


RUBYSCRIPT2EXE.rubyw = true

When I use the NetBeans Ruby IDE, which defaults a new project's main script name to "main.rb", I include the above code to avoid renaming the script with a ".rbw" extension or providing command-line parameters to the RubyScript2Exe compiler. More on that later.

A Ruby Forum reader and RubyScript2Exe user recently mentioned "I want to be able to wrap the icon file along with the rest of the application." You can embed additional files such as icons or DLLs in the executable like this:


RUBYSCRIPT2EXE.bin = ["my_icon.ico", "sqlite3.dll"]

When you run your compiled executable, RubyScript2Exe extracts all the files from your executable into a temporary directory. But sometimes you need to know the location of the folder the executable was originally run from. Just call the RUBYSCRIPT2EXE.exedir method:


APPLICATION_PATH = RUBYSCRIPT2EXE.exedir

Enough preparation! Let's compile our application. Go to a command prompt and enter:


rubyscript2exe my_script.rb

...or...


rubyscript2exe my_script.rbw

If your script has a filename extension of .rb, RubyScript2Exe will include the ruby.exe interpreter and a console window. If your script has a filename extension of .rbw, RubyScript2Exe will include the rubyw.exe interpreter and your app will therefore not have a console window; this is the same as if you had included RUBYSCRIPT2EXE.rubyw = true in your code.

The size of your compiled executable can vary widely depending on what files are needed to be included. A simple console app may be 1mb in size, a wxRuby 0.6 GUI app may be 3-4mb, and a wxRuby 2 GUI app may be 6-8mb in size. Part of this size is due to a known 'bug' that may cause some files (the 8mb wxRuby2.so file, for example) to be included twice. This affects the size of the EXE file but not the performance.

There you have it. But this post just scratches the surface. RubyScript2Exe's creator, Erik Veenstra, has done a great job maintaining and documenting this tool, and you should take a few minutes and read the docs here.

Questions? Comments? Suggestions?

Post a comment here or send me an email.

Thanks for stopping by!

Ruby & Excel: 911 Characters Only, Please

2007-09-14T18:21:00.001-05:00

I was writing some code this week to query an SQL Server database and write the results to an Excel workbook.

I was defining a range in an Excel worksheet and inserting data using code something like this:


worksheet.Range("A2").Resize(data.size, data[0].size).Value = data

The above code defines a range starting at cell A2 and extending the width and length of my 2-dimensional array, then inserts my array into that range of cells.

This worked great -- about 95 percent of the time. But occasionally it would bomb. My debugging uncovered the fact that it would always and only bomb if a string exceeding 911 characters was inserted into a cell.

Some quick googling revealed that this is a known Excel 2003 bug, acknowledged by Microsoft. Microsoft's suggested workaround is "don't do that"; don't try to insert more than 911 characters into a cell as part of an array. Gee, thanks.

I was using the method above because it is significantly faster than inserting data one cell at a time. This isn't a big deal for 100 lines x 10 columns of data, but when you're inserting a million cells of data, there's a huge time difference. I could successfully insert the data one cell at a time, but my users might have to take extended coffee breaks.

I could write the data to a tab-delimited, comma-delimited, or XML text file; then open it in Excel and tweak it if necessary. But I hoped to avoid that if possible.

In case you're wondering, I resolved it with a compromise. Rather than insert the data all at once or cell-by-cell, I inserted it row-by-row:


worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row

This will still raise an exception when attempting to insert more than 911 characters into a cell. But we'll handle those exceptions by inserting that row's data cell-by-cell.

The revised code looks something like this:


data.each_with_index do |row, r|
    begin
        # try to insert the entire array into the row
        worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row
    rescue
        # if exception, then insert each cell individually
        row.each_with_index do |field, c|
            worksheet.Cells(r + 2, c + 1).Value = field
        end
    end
end

It's not as fast as an all-at-once insert, but much faster than a cell-by-cell insert.

By the way, the '+1' offset for column number is because Ruby arrays have zero-based indexes, while Excel's column indexes are 1-based. And the '+2' offset for row number is for the same reason, plus 1 to skip the first row of the worksheet, which contains the field names.

So, insert data into worksheets as arrays when you can. But don't let the 911-Characters Bug bite.

NetBeans Ruby IDE

2007-09-12T18:30:00.000-05:00

If you're looking for a new Ruby editor, I suggest you check out NetBeans. Tor Norbye and the gang have been doing a great job adapting this Java IDE for use with Ruby and Rails.

I usually use SciTE, the Scintilla text editor, for writing my Ruby code. It's fast, flexible, and lightweight, consuming a fraction of the resources of a full-blown IDE. It's still my editor of choice for small scripts. But I've begun using NetBeans for larger projects and am very pleased so far.

NetBeans is now available in a Ruby-only version which, presumably, is a little slimmer than the full Java + Ruby IDE. And you can trim a little more fat by deactivating a few plug-ins. On my Windows XP systems, memory usage is in the 80-120Mb range. That's acceptable, even on my more memory-challenged machine, when you consider the potential productivity gains offered by the IDE features. And the fact that it's free is certainly a plus.

Further details can be found on the NetBeans Ruby Support page, on the NetBeans Ruby wiki, and in numerous in-depth reviews like this one by Daniel Spiewak.

New builds are posted every few hours, and the latest stable build can always be found here. I usually replace my nbrubyide folder every day or two.

So if you're looking for a full-featured Ruby IDE, take NetBeans for a test drive and see if it meets your needs. It may be a dumb name, but it's a good Ruby IDE.

RubyConf 2007 Comes to Charlotte

2007-09-09T12:52:00.000-05:00

Somehow, I had missed the fact that RubyConf 2007 will be held here in Charlotte, North Carolina (USA) November 2-4.

Fortunately, a friend made me aware of this fact in time for me to register to attend.

Lots of good stuff is on the agenda. Here's a couple talks that caught my eye as a Windows desktop developer:

John Lam on The State of IronRuby
Bruce Williams on Writing Client and Desktop Applications in Ruby

Registration is still open, but I hear that seats are going fast.

Automating Outlook with Ruby: Inbox & Messages

2007-08-28T20:50:00.000-05:00

In response to my series on Automating Outlook with Ruby, several readers have asked about accessing the Inbox and managing messages.

We start by using the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:


mapi = outlook.GetNameSpace('MAPI')

To get a reference to the Inbox folder, call the MAPI object's GetDefaultFolder method, passing it the integer 6, which represents the Inbox folder:


inbox = mapi.GetDefaultFolder(6)

To get the Personal Folders object, call the MAPI object's Folders.Item method, passing it the name of the folder.


personal_folders = mapi.Folders.Item('Personal Folders')

To reference a subfolder, call the parent folder object's Folders.Item method, passing it the name of the folder.


baseball_folder = personal_folders.Folders.Item('Baseball')

You can get a count of a folder's unread items by calling the UnreadItemCount method:


puts "#{inbox.UnreadItemCount} unread messages"

A folder object's Items method returns a collection of message objects, which you can iterate over:


inbox.Items.each do |message|
    # Your code here...
end

You can also pass the Items method a (1-based) index to retrieve a single message:


first_message = inbox.Items(1)

Once your code tries to access methods and properties of a Message object, an Outlook security dialog will prompt the user to allow access to Address Book entries. The user must click a checkbox to allow access and select a time limit between 1 and 10 minutes.

Message objects have dozens of methods/properties, including:

SenderEmailAddress
SenderName
To
Cc
Subject
Body

To see a complete list, you could call the ole_methods method, as explained here. So to view a sorted list of the Message object's methods and properties, we could do this:


methods = []
inbox.Items(1).ole_methods.each do |method|
    methods << method.to_s
end
puts methods.uniq.sort

To delete a message, call its Delete method:


message.Delete

To move a message to another folder, call its Move method, passing it the folder object:


baseball_folder = personal_folders.Folders.Item('Baseball')
message.Move(baseball_folder)

So, if we wanted to check our Inbox and move all messages that contain 'Cardinals' in the subject line to our 'Baseball' folder, we could do something like this:


inbox.Items.Count.downto(1) do |i|
    message = inbox.Items(i)
    if message.Subject =~ /cardinals/i
        message.Move(baseball_folder)
    end
end

As reader Ana points out, we should use the Count and downto methods to ensure that our inbox.Items index stays in sync, even as we move messages out of the Inbox. Otherwise, we run the risk of a message being skipped when the message above it is moved or deleted.

That concludes our program for today. Feel free to post a comment here or send me email with questions, comments, or suggestions.

Automating Outlook with Ruby: Address Books

2007-08-26T16:52:00.000-05:00

We recently discussed working with Outlook Contacts. On a related note, a reader asked how to access the Outlook Address Books. So let's dive right in...

We start by using the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:


mapi = outlook.GetNameSpace('MAPI')

The MAPI object's Session.AddressLists method, when called without parameters, returns a collection of the various Address Books available to Outlook, such as "Contacts", "Personal Address Book", and "Global Address List". The following code iterates over this collection and prints the name of each available Address Book:


mapi.Session.AddressLists.each do |list|
    puts list.Name
end

To work with a particular Address Book, call the MAPI object's Session.AddressLists method and pass it the name of the Address Book:


address_list = mapi.Session.AddressLists('Global Address List')

This returns a single AddressList object. Call this object's AddressEntries method to obtain a list of AddressEntry objects:


address_entries = address_list.AddressEntries

Upon executing the above line of code, an Outlook security dialog will prompt the user to allow access to Address Book entries. The user must click a checkbox to allow access and select a time limit between 1 and 10 minutes.

Once access to the Address Book entries has been granted, you can iterate over the AddressEntries collection and get the Name and Address for each entry, or only for entries that meet certain criteria:


address_entries.each do |address|
    if address.Name =~ /Machiavelli/
        name = address.Name
        email_address = address.Address
    end
end

To search for an Address, you could call the AddressEntries collection's Item method. This accepts an integer as a (1-based) index:


address_entry = address_entries.Item(27)

...but also accepts a text string:


address_entry = address_entries.Item("sinatra, f")

This seems to return only the first AddressEntry object that meets the text string criteria. If no AddressEntry meets the text criteria, it seems to return the closest item following. So don't assume that the object returned meets your search criteria -- check the Name and/or Address properties to be certain.

There you have it. As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Windows Developer Power Tools

2007-08-22T20:17:00.000-05:00

While taking a brief respite from tutorialism and browsing the stacks at Barnes & Noble recently, I stumbled across Windows Developer Power Tools, an O'Reilly book by James Avery and Jim Holmes that covers over 170 free tools for Windows developers.

I've only scratched the surface thus far, but have already begun using several of the tools mentioned in the book, including SQLite Administrator, SharpDevelop, and Snippet Compiler. Some tools are .NET-specific, but many more are not.

The companion web site is here.

If you work with SQLite, my personal favorite database, you'll probably find SQLite Administrator quite helpful. It provides a GUI front end that allows you to create, design, and manage SQLite database files. Check out the website for full details and screenshots.

Automating Outlook with Ruby: Contacts

2007-08-15T21:13:00.000-05:00

We've looked at sending email, working with calendar appointments, and creating and managing tasks with Outlook. Next we'll take a look at how to create new contacts and read contacts data from Outlook.

You know by now how this starts out: we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:


mapi = outlook.GetNameSpace('MAPI')

Creating a New Contact

To create a new contact, call the Outlook Application object's CreateItem method, passing it the number 2, which represents a Contact Item. This returns a new Contact object:


contact = outlook.CreateItem(2)

Then set values for various properties of the new Contact object:


contact.FullName = 'Stan Musial'
contact.CompanyName = 'St. Louis Cardinals'
contact.JobTitle = 'Hitter'
contact.BusinessTelephoneNumber = '(314)555-1234'
contact.Email1Address  = 'stan_the_man@stlcardinals.com'

What properties are available to set? Dozens. To see a list, you could call contact.ole_methods, Google for it (Outlook Contact Properties), or use an OLE object browser.

When you've completed setting property values, call the Contact object's Save method:


contact.Save

Getting Existing Contacts Data

To obtain a collection of Contacts, call the MAPI object's GetDefaultFolder method, passing it the integer 10, which represents the Contacts folder. Then call the Items method on this object:


contacts = mapi.GetDefaultFolder(10).Items

You can now iterate over this collection of Contact objects, calling the methods/properties to get the data you need:


contacts.each do |contact|
    puts contact.FullName
    puts contact.Email1Address
    puts contact.BusinessTelephoneNumber
end

For further information, check out these Microsoft TechNet articles:

Creating a New Contact in Microsoft Outlook
Exporting Contact Information

As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Automating Windows Media Player with Ruby

2007-07-29T19:00:00.000-05:00

My recent article about automating iTunes has resulted in several requests for a similar article about Windows Media Player (WMP). And so, without further ado...

We start by using the win32ole library to connect to the WMPlayer object:


require 'win32ole'
player = WIN32OLE.new('WMPlayer.OCX')

Note that this will not display the WMP user interface. That happens next.

To play a song, call the player object's OpenPlayer method, passing it the path to the song file:


player.OpenPlayer('c:\music\van halen\right now.wma')

The Media Collection

Next, we'll grab the MediaCollection object:


media_collection = player.mediaCollection

To quote Microsoft, "The MediaCollection object represents all the items stored in the Windows Media Player media collection. You will typically query the MediaCollection object to return collections of media items."

The MediaCollection object's getAll method returns a collection of all items in the library:


all_media = media_collection.getAll()

The getByAttribute method can be used to get a collection of all audio files:


audio_media = media_collection.getByAttribute("MediaType", "Audio")

To get a collection of songs by a specific artist, use the getByAuthor method:


sinatra_songs = media_collection.getByAuthor("Frank Sinatra")

You can also get items by Album:


album = media_collection.getByAlbum('Come Fly with Me')

Or by Genre:


jazz_tunes = media_collection.getByGenre('Jazz')

And, of course, you can get a collection of items by name:


songs = media_collection.getByName('Fly Me to the Moon')

Note that this will return a collection of items, even if it contains only one item. You reference a specific item by calling the collection's Item method, passing it a (zero-based) index value. So to get the first item:


first_song = songs.Item(0)

As mentioned earlier, to play a song, call the player object's OpenPlayer method, passing it the song object's sourceURL property. sourceURL is simply the path to the song file:


player.OpenPlayer(first_song.sourceURL)

To add a song to your Media Collection, call the MediaCollection object's Add method, passing it the path and filename:


song = media_collection.Add('C:\music\Just in Time.wma')

To delete a song, from the Media Collection, call the MediaCollection object's Remove method, passing it the song object and the boolean value 'true':


songs = media_collection.getByName('Fly Me to the Moon')
media_collection.Remove(songs.Item(0), true)

You may need to restart the Media Player user interface to see additions and removals.

The PlayLists Collection

PlayLists are managed via the player's PlaylistCollection object:


playlists = player.PlaylistCollection

The PlaylistCollection object has methods similar to the MediaCollection object. Its getAll method returns a collection of all PlayList objects:


all_playlists = playlists.getAll()

Its getByName method returns a collection of PlayList objects by name:


split_enz_playlist = playlists.getByName('Split Enz')

Remember, this returns a collection, even if that collection contains a single item. So to get the first (and possibly only) item, we call the Item method on the returned collection:


split_enz_playlist.Item(0)

A PlayList is a collection of Song items, just like those returned by the MediaCollection object's methods. You can call each item in the collection by index. So to print the name of each song in our playlist:


(0..my_playlist.Count - 1).each do |i|
    song = my_playlist.Item(i)
    puts song.Name
end

To create a new playlist, call the PlaylistCollection object's newPlaylist method, passing it the name of the new playlist:


playlists = player.PlaylistCollection
playlists.newPlaylist('New Playlist')

This creates a .wpl playlist file. You then have to add the new playlist file to the MediaCollection object, by calling the Add method and passing it the path and name of the newly created PlayList file:


media_collection.Add('D:\Music\My Playlists\New Playlist.wpl')

The path to the playlist file will be defined in the 'Rip music to this location' setting in Media Player's options.

To remove a playlist, call the PlaylistCollection object's Remove method, passing it the name of the PlayList object:


playlists = player.PlaylistCollection
split_enz_playlist = playlists.getByName('Split Enz').Item(0)
playlists.Remove(split_enz_playlist)

To add a song to a playlist, call the PlayList object's appendItem method, passing it the song object:


song = media_collection.getByName('Fly Me to the Moon').Item(0)
playlist = playlists.getByName('Frank & Dino').Item(0)
playlist.appendItem(song)

To remove a song from a playlist, call the PlayList object's removeItem method, passing it the song object:


playlist.removeItem(song)

Playing a playlist is much like playing an individual song. First get the individual PlayList object, then pass its sourceURL value to the player object's OpenPlayer method:


playlist = playlists.getByName('Frank & Dino').Item(0)
player.OpenPlayer(playlist.sourceURL)

And there you have it.

Further details can be found in this Microsoft TechNet article.

As always, feel free to post a comment or send email with questions, comments, or suggestions.

Thanks for stopping by!

Automating and Managing iTunes with Ruby

2007-07-22T10:36:00.000-05:00

We've spent a lot of time on this blog looking at how to get work done with Ruby and Microsoft Office (Excel, Word, Access, Outlook). Let's take a break and go play... with iTunes.

Apple's popular iTunes application for Windows includes a COM interface. This allows you to automate and manage iTunes with Ruby. You can launch the application, search for and play songs, set user interface properties, and manage your iTunes library and PlayLists using Ruby code. Let's take a look...

First, of course, we start by connecting to the iTunes application object, launching iTunes if it isn't already running:


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

Controlling the iTunes User Interface

To place iTunes into MiniPlayer mode (or return it to Full Mode), set the BrowserWindow.MiniPlayer property:


itunes.BrowserWindow.MiniPlayer = true

To toggle the play/pause button, simply call the PlayPause method:


itunes.PlayPause

To increase or decrease sound volume, adjust the SoundVolume property:


itunes.SoundVolume = itunes.SoundVolume + 50
itunes.SoundVolume = itunes.SoundVolume - 25

To go to the previous or next track, call those methods:


itunes.PreviousTrack
itunes.NextTrack

Managing Your iTunes Library

OK, that covers several standard user interface features. Now, let's look at how you can work with your content.

Let's create an instance of the Library, which is a child object of the Application object:


library = itunes.LibraryPlaylist

We'll be working with this LibraryPlaylist object a lot going forward. It provides several methods that will return collections of Track and PlayList objects. For example, calling the Tracks method returns a collection of all tracks in the library:


tracks = library.Tracks

You can then select a song by name from this collection of Track objects:


song = tracks.ItemByName('At Long Last Love')

...and then play the track by calling its Play method:


song.Play

To search the LibraryPlaylist, call its Search method, passing it a string to search for, and an integer that determines what field to search, such as Artist, Album Title, or Track Title:


artist_tracks = library.Search('Sinatra', 2)
album_tracks = library.Search('Come Fly With Me', 3)
title_tracks = library.Search('Fly Me To The Moon', 5)

The Search and Tracks methods return a collection of Track objects, which you could iterate over. We might want to add all track objects in this collection to a ruby array called songs:


songs = []
for track in tracks
    songs << track
end

This would then allow us to use Ruby's excellent sort_by method to sort the songs array:


songs = songs.sort_by{|song| [song.Artist, song.Year, song.Album, song.TrackNumber]}

The Track object includes dozens of attributes pertaining to this track. As indicated above, there's Artist, Album, Year, and TrackNumber. But there's also Time (in minutes and seconds), Duration (total seconds), BPM (beats-per-minutes), Composer, Genre, and many more.

You could iterate over the songs collection and write data for each track to a file:


songs.each do |song|
    my_file.puts [song.Artist, song.Album, song.Name, song.Time].join("\t")
end

...or add the data to an Excel worksheet or SQLite database.

Working with iTunes PlayLists

To create a new PlayList, call the Application object's CreatePlaylist method, passing it the name of the new PlayList:


playlist = itunes.CreatePlaylist('My Playlist')

To add a song to a PlayList object, first get a Track object by calling the ItemByName method on a Tracks collection:


song = library.Tracks.ItemByName('At Long Last Love')

...then call the PlayList's AddTrack method, passing it the Track object:


playlist.AddTrack(song)

In the iTunes object model, PlayLists are child objects of Source objects. So, to create an array of all PlayList objects, we iterate over each Source object and get each PlayList object:


playlists = []
itunes.Sources.each do |source|
    source.PlayLists.each do |playlist|
        playlists << playlist
    end
end

This gives us a collection of PlayList objects that we can now work with:


for playlist in playlists do
    puts playlist.Name
end

To select a single PlayList by name:


playlist = itunes.Sources.ItemByName('Library').Playlists.ItemByName('All Sinatra')

To play the first track of a PlayList:


playlist.PlayFirstTrack

To exit the iTunes application, call its Quit method:


itunes.Quit

Further Reading

Further details can be found in this Microsoft TechNet article.

For further examples in Ruby, you might want to check out the itunes-control (itch) library source code.

And for those running iTunes on Mac OS, you may find this blog post by Zack Hobson to be of interest.

I hope you have found this information helpful. As always, post a comment here or send me an email if you have questions, comments, or suggestions.

Thanks for stopping by!

Automating Outlook with Ruby: Tasks

2007-07-18T20:35:00.000-05:00

In our last episode, we used Ruby to extract appointments data from the Outlook Calendar, delete an appointment, and create a new appointment. Today we'll look at managing Outlook Tasks with Ruby.

Getting Existing Tasks

As usual, we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:


mapi = outlook.GetNameSpace('MAPI')

Outlook consists of several Folder objects, including Inbox, Tasks, and Calendar. To get a folder object, call the MAPI object's GetDefaultFolder method, passing it an integer representing the folder to return. For the Tasks folder, this number is 13:


tasks = mapi.GetDefaultFolder(13)

The Tasks folder's Items method returns a collection of all tasks, which you can iterate over. The following code prints out some of the most-often used properties for each task:


for task in tasks.Items
    puts task.Subject
    puts task.Body
    puts task.DueDate
    puts task.PercentComplete
    puts task.Status
    puts task.Importance
    puts task.LastModificationTime
end

The Status method/property returns an integer, representing one of the following values:

0 -- Not started
1 -- In progress
2 -- Complete
3 -- Waiting on someone else
4 -- Deferred

The Importance method/property returns an integer, representing one of the following values:

0 -- Low
1 -- Normal
2 -- High

Filtering Your Tasks Collection

You can filter your collection of Task items by calling the Items collection's Restrict method, passing it a filter string. The following code gets all 'Order Yankees World Series Tickets' tasks, and then deletes each one:


for task in tasks.Items.Restrict("[Subject] = 'Order Yankees World Series Tickets'")
    task.Delete
end

Other possible filters include:


tasks.Items.Restrict("[Status] = 'Not started'")
tasks.Items.Restrict("[Importance] = 'High'")

As illustrated in the above example, to delete a Task, call its Delete method.

Creating New Tasks

To create a new task, call the Outlook Application object's CreateItem method, passing it the number 3, which represents a Task Item. This returns a new Task object:


task = outlook.CreateItem(3)

Then set values for various properties of the new Task object:


task.Subject = 'Send flowers to wife'
task.Body = 'Call flower shop and send roses.'
task.ReminderSet = true
task.ReminderTime = '7/19/2007 12:00 PM'
task.DueDate = '7/20/2007 12:00 PM'
task.ReminderPlaySound = true
task.ReminderSoundFile = 'C:\Windows\Media\Ding.wav'

When you've completed setting property values, call the Task object's Save method:


task.Save

Further details can be found in this Microsoft TechNet article.

As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Automating Outlook with Ruby: Calendar Appointments

2007-07-14T21:01:00.000-05:00

In a previous article, we looked at how to automate Microsoft Outlook to create and send a new email message. This time around, we'll use Ruby to extract appointments data from the Outlook Calendar, delete an appointment, and create a new appointment.

As usual, we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:


mapi = outlook.GetNameSpace('MAPI')


calendar = mapi.GetDefaultFolder(9)

The calendar folder's Items method returns a collection of all appointments, which you can iterate over. Each appointment object includes dozens of properties. The following code prints out six of the most-often used properties:


calendar.Items.each do |appointment|
    puts appointment.Subject
    puts appointment.Location
    puts appointment.Start
    puts appointment.Duration
    puts appointment.End
    puts appointment.Body
end

To delete an appointment, call its Delete method. For example, the following code locates an appointment based on its Subject value, then deletes it, if found:


calendar.Items.each do |appointment|
    if appointment.Subject == 'Punch Bud Selig in the Nose'
        appointment.Delete
    end
end

To create a new appointment, call the Outlook Application object's CreateItem method, passing it the number 1, which represents a Calendar Item. This returns a new Appointment object:


appointment = outlook.CreateItem(1)

Then set values for various properties of the new Appointment object:


appointment.BusyStatus = 2
appointment.Start = '7/29/2007 11:00 AM'
appointment.Duration = 300
appointment.Subject = 'Baseball Hall of Fame Induction'
appointment.Body = 'Tony Gwynn and Cal Ripken Jr.'
appointment.Location = 'Cooperstown, NY'
appointment.ReminderMinutesBeforeStart = 15
appointment.ReminderSet = true

The BusyStatus value is an integer with the following possible values:

olFree = 0
olTentative = 1
olBusy = 2
olOutOfOffice = 3

The Duration value is an integer representing the appointment length in minutes.

For an all-day event, forget the Duration property and instead set the AllDayEvent property to true:


appointment.AllDayEvent = true

To make an appointment recurring, you'll want to work with the Appointment object's Recurrence child object. You get this object by calling the GetRecurrencePattern method:


recurrence = appointment.GetRecurrencePattern

Now you'll set the RecurrenceType to one of the following values:

0 -- To set an appointment that occurs each and every day.
1 -- To set an appointment that occurs on the same day each week.
2 -- To set an appointment that occurs on the same day each month.
5 -- To set an appointment that occurs on the same day each year.

So, for an appointment that occurs on this day and time (as defined in your Start property above) each week:


recurrence.RecurrenceType = 1

Next, we'll set the PatternStartDate property. This value needs to be on or before the Start value defined above:


recurrence.PatternStartDate = '8/6/2007'

Finally, we set the PatternEndDate property:


recurrence.PatternEndDate = '8/27/2007'

When you've completed setting property values, call the Appointment object's Save method:


appointment.Save

And there you have it. Further details can be found in this Microsoft TechNet article.

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

Thanks for stopping by!

Using Ruby & WMI to Get Win32 Process Information

2007-07-08T21:44:00.000-05:00

In an earlier post, we looked at using Windows Management Instrumentation (WMI) to determine if a "USB Mass Storage Device" is inserted. Today we'll use WMI to get information about the Win32 processes being run.

WMI is installed and already running on all recent versions of Windows, so we'll connect to it using the win32ole library's connect method:


require 'win32ole'
wmi = WIN32OLE.connect("winmgmts://")

Next, we'll call WMI's ExecQuery method to query the Win32_Process table:


processes = wmi.ExecQuery("select * from win32_process")

This returns a collection of all WMI Win32_Process objects.

The Win32_Process class has dozens of properties. You can find a complete list of those properties here. You could also produce a list of these properties by calling the Properties_ method (note the underscore) on one of the Win32_Process objects. This returns a collection of Properties; then call the Name method/property for each of these Property objects. For example:


for process in processes do
    for property in process.Properties_ do
        puts property.Name
    end
    break
end

To obtain data about a process, call that Process object's relevant property/method. For example, the following code prints four properties for each process:


for process in processes do
    puts "Name: #{process.Name}"
    puts "CommandLine: #{process.CommandLine}"
    puts "CreationDate: #{process.CreationDate}"
    puts "WorkingSetSize: #{process.WorkingSetSize}"
    puts
end

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

Thanks for stopping by!

Querying the Windows Registry for the Default Mail Client

2007-07-02T21:23:00.000-05:00

Following up on my post about sending email with MS Outlook, a reader asks, "I wonder how one can mine out the chosen default mail client... Is that somewhere in the Registry?"

It does indeed appear to be in the Windows Registry, and can therefore be extracted using the win32/registry library. I've just started hacking around with this library, but this bit of code seems to do the trick...


require 'win32/registry'

def get_mail_client
    Win32::Registry::HKEY_LOCAL_MACHINE.open('Software\Clients\Mail') do |reg|
        reg_typ, reg_val = reg.read('')
        return reg_val
    end
end

mail_client = get_mail_client

...though I am sure it can be improved upon.

On my machine, this returns 'Microsoft Outlook'.

Well, there you have it. As always, post comments or send email with enhancements, questions, or suggestions for future topics.

Thanks for stopping by!

Automating Outlook with Ruby: Sending Email

2007-07-01T10:12:00.000-05:00

Just as we have done with Excel and Word, we can use the win32ole library to automate various tasks in Microsoft Outlook. Let's take a look at how to create an new email message.

Of course, we'll want to require the win32ole library:


require 'win32ole'

Create an instance of the Outlook Application object:


outlook = WIN32OLE.new('Outlook.Application')

Outlook is a single-instance application, so if it is already running, calling the WIN32OLE.new method will behave the same as the WIN32OLE.connect method, returning the running instance.

To create a new item in Outlook, call the Application object's CreateItem method and pass it a numeric value that represents the type of item to create. Valid arguments include:

olMailItem = 0
olAppointmentItem = 1
olContactItem = 2
olTaskItem = 3
olJournalItem = 4
olNoteItem = 5
olPostItem = 6

So, to create a new email message (ie, MailItem), we call the CreateItem method with an argument of 0 (zero):


message = outlook.CreateItem(0)

This returns the newly-created mail item object. We'll proceed by setting values for this MailItem object's properties and calling some of its methods.

To define the subject line of the message, set its Subject value with a string:


message.Subject = 'Double-Header Today'

To define the message body, set the Body value to a string:


message.Body = 'This is the message body.'

Alternatively, for an HTML message body, set the HtmlBody value to an HTML-encoded string.

Define the recipient by setting the To property...


message.To = 'ted.williams@redsox.com'

...or call the Recipients.Add method one or more times:


message.Recipients.Add 'ted.williams@redsox.com'
message.Recipients.Add 'joe.dimaggio@yankees.com'

Got attachments to add? Call the Attachments.Add method one or more times, passing it the path and name of the attachment:


message.Attachments.Add('c:\my_folder\my_file.txt', 1)

The second argument, 1, indicates that this is an attached file, rather than a link to the original file.

You can save the unsent message in your Outbox by calling the Save method...


message.Save

This would then allow you to open and review the message before manually clicking the Send button.

Of course, you can send the message with the Send method:


message.Send

Note that when using the Send method, Outlook may display a security alert that forces the user -- after a forced pause of about five seconds -- to click 'Yes' to allow the message to be sent. I am unaware of a method for avoiding this alert dialog.

UPDATE: I have discovered, but not yet used, a free Outlook add-in from MAPILab that allows the end user to set Outlook security settings. And the related ($99) Security Manager claims to allow you to bypass the Outlook security alerts, with a single line of code. I can't recommend either solution, not having tried them, but mention them here for your information.

That about wraps our show for today. Would you like to see more about Automating Outlook with Ruby? As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Using Ruby & ADO to Work with Excel Worksheets

2007-06-29T20:49:00.000-05:00

In an earlier article, I discussed using ActiveX Data Objects (ADO) to access a Microsoft Access database. A reader commented that ADO can also be used to access data in an Excel worksheet. Here's a brief demonstration...

As usual, we'll use the win32ole library:


require 'win32ole'

Create a new ADODB.Connection object:


connection = WIN32OLE.new('ADODB.Connection')

To open a connection to your Excel workbook, we'll call the Connection object's Open method and pass it a connection string. You can use same the Microsoft Jet driver used for accessing an MS Access database, but we need to append an "Extended Property" to specify that this is an Excel woorkbook:


conn_string =  'Provider=Microsoft.Jet.OLEDB.4.0;'
conn_string << 'Data Source=c:\my_folder\my_workbook.xls;'
conn_string << 'Extended Properties=Excel 8.0;'
connection.Open(conn_string)

Now, we'll create an ADO recordset object:


recordset = WIN32OLE.new('ADODB.Recordset')

When calling the RecordSet object's Open method, pass it your SQL statement and the open connection object. When working with an Excel worksheet as your table, append '$' to the worksheet table name and wrap it in brackets:


recordset.Open("select * from [Sheet1$];", connection)

The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:


data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:


connection.close

There you have it! My thanks to reader Khaoz for the suggestion of using ADO with Excel.

Other articles about working with ADO can be found under the ado label to the right.

As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Automating Excel with Ruby: Formatting Worksheets

2007-06-24T22:04:00.000-05:00

If you're going to automate the generation of an Excel worksheet, you might as well make it look good. There are a near-infinite number of methods that can be called upon to format rows, columns, ranges, and cells. Let's take a look at some of the most common.

To format the first row of a worksheet as bold-font, set the Font.Bold value:


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

To format numbers in a range, set the NumberFormat value.

To format column D as Currency:


worksheet.Columns(4).NumberFormat = "$###,##0.00"

To format column A as a date (mm/dd/yy):


worksheet.Column("A").NumberFormat = "mm/dd/yy"

Alternatively, you can set the Style value:


worksheet.Column("A").Style = "Currency"
worksheet.Column("B").Style = "Percent"

To set the alignment on a range, set its HorizontalAlignment value:


worksheet.Rows(1).HorizontalAlignment = 2          # Left
worksheet.Columns("A:F").HorizontalAlignment = 4   # Right
worksheet.Cells(3, 5).HorizontalAlignment = -4108  # Center

To auto-fit the width of a column, or the height of a row, call its AutoFit method:


worksheet.Columns.AutoFit
worksheet.Rows(1).AutoFit

To set the width of a column, set its ColumnWidth value:


worksheet.Columns(4).ColumnWidth = 25.0

To set the height of a row, set its RowHeight value:


worksheet.Rows.RowHeight = 15.0

To apply highlighting to a range, set its Interior.ColorIndex value:


worksheet.Rows(10).Interior.ColorIndex = 6   # Yellow

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!

Making Use of Ruby's ENV Object

2007-06-20T20:53:00.000-05:00

Ruby provides the ENV object for easy access to a variety of environment variables. ENV is not actually a hash, but you access individual values like you would a hash:


ENV['Path']

If you need to, you can convert it into a Hash using the to_hash method, so to see a complete list of keys and values stored in ENV, you could do this:


ENV.to_hash.each do |key, value|
    puts("#{key}\t#{value}")
end

Many of these values you may never use, but a few are worth noting...

HOMEDRIVE returns the home drive, for example:


ENV['HOMEDRIVE']    # returns "C:"

APPDATA returns the path to the Application Data folder(ie, "C:")


ENV['APPDATA']    # returns "C:\Documents and Settings\Joe DiMaggio\Application Data"

USERPROFILE returns the user's home directory:


ENV['USERPROFILE']    # returns "C:\Documents and Settings\Joe DiMaggio"

USERNAME returns the Windows user's name or Windows login:


ENV['USERNAME']    # returns "Joe DiMaggio"

ProgramFiles returns the path to the Program Files folder:


ENV['ProgramFiles]    # returns "C:\Program Files"

windir returns the path to the Program Files folder:


ENV['windir']    # returns "C:\Windows"

Writing to Environment Variables

To quote The Pickaxe:

"A Ruby program may write to the ENV object, which on most systems changes the values of the corresponding environment variables. However, this change is local to the process that makes it and to any subsequently spawned child processes. This inheritance of environment variables is illustrated in the code that follows. A subprocess changes an environment variable and this change is seen in a process that it then starts. However, the change is not visible to the original parent. (This just goes to prove that parents never really know what their children are doing.)"

A tip of the hat to reader Revence, who shared a code snippet that utilized the ENV object, reminding me of its value.

That's all for now. As always, leave a comment here or via email if you have questions or suggestions for future topics.

Thanks for stopping by!

Automating Word with Ruby: Formatting Text

2007-06-15T20:55:00.000-05:00

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

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

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

Fonts

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


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

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


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

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


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

Indenting

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


document.Paragraphs.Indent

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


document.Paragraphs(1).Outdent

Alignment

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

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

To center-align the second paragraph, for example:


document.Paragraphs(2).Alignment = 1

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

Thanks for stopping by!

Using Ruby & WMI to Detect a USB Drive

2007-06-12T21:05:00.000-05:00

While I was away, a reader asked, "Anyone know how to monitor Mass Storage Insert events (eg. When someone inserts a USB drive) from Ruby?" So let's take a look at how to use Windows Management Instrumentation (WMI) to determine if a "USB Mass Storage Device" is inserted.

Microsoft says that WMI "is the primary management technology for Microsoft® Windows® operating systems." WMI is installed and already running on all recent versions of Windows, so we'll connect to it using the win32ole library's connect method:


require 'win32ole'
wmi = WIN32OLE.connect("winmgmts://")

Next, we'll call WMI's ExecQuery method to query the Win32_USBControllerDevice to obtain a collection of USB Controller Device objects:


devices = wmi.ExecQuery("Select * From Win32_USBControllerDevice")

Each device in the collection has a Dependent method that returns a string something like this:


\\CASABLANCA\root\cimv2:Win32_PnPEntity.DeviceID="USB\\ROOT_HUB20\\3&41601B64&0"

To extract the Device ID value, we want to remove the double-quotes and grab the portion of the string after the "=":


device_name = device.Dependent.gsub('"', '').split('=')[1]

Now we'll use that Device ID to query the Win32_PnPEntity to obtain a collection of USB Device objects:


usb_devices = wmi.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '#{device_name}'")

Finally, we will iterate over the collection and call the Description method for each, looking for a value of 'USB Mass Storage Device':


for usb_device in usb_devices do
    if usb_device.Description == 'USB Mass Storage Device'
        # DO SOMETHING HERE
    end
end

Putting it all together, we have something like the following:


require 'win32ole'

wmi = WIN32OLE.connect("winmgmts://")

devices = wmi.ExecQuery("Select * From Win32_USBControllerDevice")
for device in devices do
    device_name = device.Dependent.gsub('"', '').split('=')[1]
    usb_devices = wmi.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '#{device_name}'")
    for usb_device in usb_devices do
        puts usb_device.Description
        if usb_device.Description == 'USB Mass Storage Device'
            # DO SOMETHING HERE
        end
    end
end

The above code is functional, but can certainly be improved upon and modified to meet your specific needs.

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

Thanks for stopping by!

Using Ruby & ADO to Work with MS Access Databases

2007-06-04T21:55:00.001-05:00

A reader in the Ruby forum recently asked about using Ruby to get data from a Microsoft Access database. This can be done easily using the ActiveX Data Objects (ADO), via the win32ole library. Let's walk through it...

As usual, require the win32ole library:


require 'win32ole'

Next, create a new ADODB Connection object, which will manage the connection to the database:


connection = WIN32OLE.new('ADODB.Connection')

To open a connection to the Access database, call the Connection object's Open method, passing it the Provider and Data Source (your Access mdb file) parameters:


connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:\baseball\lahman54.mdb')

To execute an SQL statement that does not return a recordset (ie, CREATE, ALTER, INSERT), you can call the Connection object's Execute method:


connection.Execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

To perform a query that returns a recordset of data, first create a new ADODB Recordset object:


recordset = WIN32OLE.new('ADODB.Recordset')

Then call the Recordset object's Open method, passing it the SQL statement and your existing Connection object:


recordset.Open(sql, connection)

This loads the query results into the Recordset object. The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:


data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:


recordset.Close

We could create a simple class to wrap up this functionality:


class AccessDb
    attr_accessor :mdb, :connection, :data, :fields

    def initialize(mdb=nil)
        @mdb = mdb
        @connection = nil
        @data = nil
        @fields = nil
    end

    def open
        connection_string =  'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        connection_string << @mdb
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        recordset = WIN32OLE.new('ADODB.Recordset')
        recordset.Open(sql, @connection)
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            @data = recordset.GetRows.transpose
        rescue
            @data = []
        end
        recordset.Close
    end

    def execute(sql)
        @connection.Execute(sql)
    end

    def close
        @connection.Close
    end
end

This class is very similar to an SqlServer class I previously discussed.
We would use this AccessDb class as follows:


db = AccessDb.new('c:\Baseball\lahman54.mdb')
db.open

db.query("SELECT * FROM AllStar WHERE playerID = 'conceda01';")
field_names = db.fields
rows = db.data

db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

db.close

That about wraps up our show for today. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Displaying a MessageBox Using the Windows API

2007-06-02T18:41:00.000-05:00

Sometimes the only user interface you need is a message box. Maybe you just need to alert the user to the completion of a process; or maybe you need to ask a question to which the user responds Yes or No. You'd rather not resort to a console window; but neither do you want to load an entire GUI library to display a simple message box. "Dave", you say, "there must be a better way."

Indeed, there is. Use the DL library to call the MessageBoxA Windows API function.

First, require the dl library:


require 'dl'

We'll feed the function our message text, a dialog box title, and an integer that defines what buttons to display. Let's define some meaningful constants to represent the possible button values:


BUTTONS_OK = 0
BUTTONS_OKCANCEL = 1
BUTTONS_ABORTRETRYIGNORE = 2
BUTTONS_YESNO = 4

The function will return an integer representing the button that the user clicked, so let's define some meaningful constants to represent the possible return code values:


CLICKED_OK = 1
CLICKED_CANCEL = 2
CLICKED_ABORT = 3
CLICKED_RETRY = 4
CLICKED_IGNORE = 5
CLICKED_YES = 6
CLICKED_NO = 7

Here's our method to call the MessageBoxA function from the user32 DLL:


def message_box(txt='', title='', buttons=0)
    user32 = DL.dlopen('user32')
    msgbox = user32['MessageBoxA', 'ILSSI']
    r, rs = msgbox.call(0, txt, title, buttons)
    return r
end

Here's how we call it display a message box with an OK button:


message_box("Hello, World!", "Hi!", BUTTONS_OK)

Here's how we call it to display a message box with 'Yes' and 'No' buttons, and process the response:


response = message_box("Are you sure you want to proceed?", "Proceed?", BUTTONS_YESNO)
if response == CLICKED_YES
    # insert your code here
end

Finally, here it is in its entirety:


require 'dl'

# button constants
BUTTONS_OK = 0
BUTTONS_OKCANCEL = 1
BUTTONS_ABORTRETRYIGNORE = 2
BUTTONS_YESNO = 4

# return code constants
CLICKED_OK = 1
CLICKED_CANCEL = 2
CLICKED_ABORT = 3
CLICKED_RETRY = 4
CLICKED_IGNORE = 5
CLICKED_YES = 6
CLICKED_NO = 7

def message_box(txt, title=APP_TITLE, buttons=BUTTONS_OK)
    user32 = DL.dlopen('user32')
    msgbox = user32['MessageBoxA', 'ILSSI']
    r, rs = msgbox.call(0, txt, title, buttons)
    return r
end

response = message_box("Are you sure you want to proceed?", "Proceed?", BUTTONS_YESNO)
if response == CLICKED_YES
    # insert your code here
end

There you have it. As always, let me know if you have questions, comments, or suggestions.

Thanks for stopping by!

Automating Applications with Ruby & The Windows Script Host

2007-05-30T07:55:00.000-05:00

We've talked at length about automating Windows applications through COM/OLE, using the win32ole library. But not all applications expose themselves (so to speak) to such automation. The Windows Script Host can automate the activation of windows, and the sending of keystrokes. This may sometimes be all that you need to get the job done.

The Windows Script Host (WSH) has been part of the Windows operating system since Windows 98. You can use WSH's Shell object (via COM/OLE) to send keystokes to windows.

First, require the win32ole library:


require 'win32ole'

Now we'll create an instance of the Wscript Shell object:


wsh = WIN32OLE.new('Wscript.Shell')

To send keystrokes to a window, you must first activate the window, bringing it to the forefront. This can be done with the Wscript Shell's AppActivate method, which returns true if the window was successfully activated, and false otherwise. The AppActivate method takes the window title text as it's argument:


wsh.AppActivate('Title')

The string passed to the AppActivate method can be a partial, but must be the start or ending of the window title. The method is not case sensitive, and does not accept regular expressions. To quote Microsoft: "In determining which application to activate, the specified title is compared to the title string of each running application. If no exact match exists, any application whose title string begins with title is activated. If an application still cannot be found, any application whose title string ends with title is activated. If more than one instance of the application named by title exists, one instance is arbitrarily activated."

Once you have the window activated, you may use the Wscript Shell's SendKeys method to send keystrokes to the window. The SendKeys method takes a string in quotes. Special keys (ie, ENTER, TAB, PGDN, PGUP, Function Keys) may be embedded in the string, if surrounded by braces:


wsh.SendKeys('Ruby{TAB}on{TAB}Windows{ENTER}')

The SHIFT key is represented by '+', the ALT key is represented by '%', and the CTRL key is represented by '^', so to quit an application by sending ALT-F4:


wsh.SendKeys('%{F4}')

Further details on the syntax of the SendKeys method can be found
here.

Timing is important using these methods, so you may need to insert a sleep method here and there, to get the optimal performance. For example, a 1-second (or less) wait between activating a window and sending keystrokes, or vice-versa.

So, putting it all together, here's a brief example that activates Notepad (which must be running first), inserts text, saves the file to a specific name, and quits Notepad:


# Require the win32ole library:
require 'win32ole'
# Create an instance of the Wscript Shell:
wsh = WIN32OLE.new('Wscript.Shell')
# Try to activate the Notepad window:
if wsh.AppActivate('Notepad')
    sleep(1)
    # Enter text into Notepad:
    wsh.SendKeys('Ruby{TAB}on{TAB}Windows{ENTER}')
    # ALT-F to pull down File menu, then A to select Save As...:
    wsh.SendKeys('%F')
    wsh.SendKeys('A')
    sleep(1)
    if wsh.AppActivate('Save As')
        wsh.SendKeys('c:\temp\filename.txt{ENTER}')
        sleep(1)
        # If prompted to overwrite existing file:
        if wsh.AppActivate('Save As')
            # Enter 'Y':
            wsh.SendKeys('Y')
        end
    end
    # Quit Notepad with ALT-F4:
    wsh.SendKeys('%{F4}')
end

The above code snippet can be improved upon, and I encourage you to do so. But it, hopefully, demonstrates what can be done.

Mimicing keystrokes is certainly not the ultimate in program automation, but it may sometimes be all that you need to get the job done. For example, back in the days before pop-up blockers, I had written a script that would simply run in the background, look for pop-up ads (based on a list of title strings), and close them. Simple, yet effective.

I should probably also mention AutoIt, "a freeware Windows automation language. It can be used to script most simple Windows-based tasks." I've not used it myself, but I believe that the Watir library leverages it.

That's all for now. As always, let me know if you have questions, comments, or requests for future topics.

Thanks for stopping by!

Launching Apps and Printing Docs with the Windows Shell

2007-05-24T21:42:00.000-05:00

A reader recently asked how to launch an application from within a Ruby script. A quick answer is to use the system or exec methods. But you can also leverage the Windows Shell to launch applications, and have control over the window state. You can also use the shell to print documents. Let's get right down to it, shall we?...

Require the win32ole library...


require 'win32ole'

Create an instance of the Windows Shell object...


shell = WIN32OLE.new('Shell.Application')

The shell object's ShellExecute method performs a specified operation on a specified file. The syntax is...


shell.ShellExecute(FILE, ARGUMENTS, DIRECTORY, OPERATION, SHOW)

FILE: Required. String that contains the name of the file on which ShellExecute will perform the action specified by OPERATION.

ARGUMENTS: Optional. The parameter values for the operation.

DIRECTORY: Optional. The fully qualified path of the directory that contains the file specified by FILE. If this parameter is not specified, the current working directory is used.

OPERATION: Specifies the operation to be performed. It should be set to one of the verb strings that is supported by the file (Examples: 'open', 'edit', or 'print'). If this parameter is not specified, the default operation is performed.

SHOW: Recommends how the window that belongs to the application that performs the operation should be displayed initially (0 = hidden, 1 = normal, 2 = minimized, 3 = maximized). The application can ignore this recommendation. If this parameter is not specified, the application uses its default value.

So, to launch Excel in a maximized window...


shell.ShellExecute('excel.exe', '', '', 'open', 3)

I suppose you could also launch your rails app with something like this...


shell.ShellExecute('ruby.exe', 'c:\my_rails_app\script\server', '', 'open', 1)

To print a document, hiding the application window...


shell.ShellExecute('C:\MyFolder\Document.txt', '', '', 'print', 0)

That's about it. As always, post a comment here or send me email if you have questions, comments, or would like to request a topic for discussion.

Thanks for stopping by!

The Shell Windows Collection of Internet Explorer Objects

2007-05-22T20:21:00.000-05:00

As mentioned previously, you cannot use the WIN32OLE.connect method to connect to a running instance of Internet Explorer, as you would do, for example, with Excel or Word. I'll explain now how to do this via the Windows Shell.

First, here's the code snippet, which grabs an instance of IE that has this blog displayed...


for window in WIN32OLE.new('Shell.Application').Windows
    begin
        if window.Document.Title =~ /Ruby on Windows/
            ie = window
        end
    rescue
    end
end

The Windows Shell object includes a Windows method which returns a collection of all of the open windows that belong to the Shell...


shell = WIN32OLE.new('Shell.Application')
windows = shell.Windows

This is actually a collection of Internet Explorer objects, though some of these may be Internet Explorer web browser windows and others may be Windows Explorer windows.

To get a count of the number of windows, call the Count method...


windows.Count

To reference a member of this collection by index, call the Item method, passing it the (zero-based) index...


first_window = windows.Item(0)

Internet Explorer windows will normally have a Document object, which will have a Title property, so to find the IE window that you want to work with, iterate over the Windows collection and check the Document.Title value...


for window in windows
    begin
        if window.Document.Title =~ /Ruby on Windows/
            ie = window
        end
    rescue
    end
end

...and now you have your IE application object to work with as previously discussed.

Note that not all Shell Window objects will have a Document object, so (in the example above) wrapping the code within a begin... rescue... end block would handle the error that occurs with non-IE windows.

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

Automating Internet Explorer with Ruby: Without Watir

2007-05-20T10:34:00.000-05:00

In a previous post, I discussed how to use the watir library to automate Microsoft Internet Explorer. I should also mention that you can also do this directly, using the win32ole library. Let's take a look at how to do this...

First, require the win32ole library...


require 'win32ole'

Create a new instance of Internet Explorer...


ie = WIN32OLE.new('InternetExplorer.Application')

(NOTE: You cannot use the WIN32OLE.connect method to connect to a running instance of Internet Explorer, as you would do, for example, with Excel or Word. I'll explain later how to do this via the Windows Shell. Stay tuned...)

You can show or hide the IE window by setting the Visible property...


ie.Visible = true

Navigate to a URL using the Navigate method...


ie.Navigate('http://google.com')

Wait until IE has completed loading the page, by checking the ReadyState property...


sleep(1) until ie.ReadyState == 4

When a web page is loaded into IE, the contents of the page are represented by the Document object. The document object's All method returns a collection of all elements within the document, such as input textboxes, links, buttons, etc. You may reference a document element by name, which you will find in the HTML code for that page. For example, viewing the source for the google.com top page shows us, among others things, that we have a textbox named 'q' and a button named 'btnG'...


input maxlength=2048 name=q size=55 title="Google Search" value=""
input name=btnG type=submit value="Google Search"

Let's enter a value into the textbox named 'q' by setting its Value property...


ie.Document.All.q.Value = 'ruby on windows'

...and then click the button named 'btnG', by calling its Click method...


ie.Document.All.btnG.click

...and wait until IE has completed loading the page...


sleep(1) until ie.ReadyState == 4

The Document.All.Tags method returns a collection of all elements with the specified HTML tag, such as 'a', 'tr', or 'input'. So, to get a collection of all links, you could do this...


links = ie.Document.All.Tags('a')

You could then iterate over this collection...


for link in links do
    puts link.InnerText  # print the link's text
    puts link.href       # print the link's URL
end

To quit IE, call the application object's Quit method...


ie.Quit

There is much more to automating IE than I what I have presented here, but this will hopefully get you started. As always, feel free to post a comment here or email me if there is a specific task or topic that you would like to see discussed here.

This is all about helping you make better use of Ruby on Windows.

Thanks for stopping by!

Adding Sound to Your Ruby Apps

2007-05-16T20:10:00.000-05:00

Have you ever thought about including sounds in your Ruby application? Used sparingly, sound may enhance your applications by adding audio cues or a custom touch. You could, for example, play a beep or chime that announces the completion of a lengthy process. Perhaps a humorous sound to accompany an error message .

The win32-sound library makes using sounds really simple.

If you installed Ruby with the One-Click Ruby Installer, then you probably already have the win32-sound library installed, along with other win32 utilities. Otherwise, you can install it in seconds via gem. Open up a console window and enter..


gem install win32-sound

To use the win32-sound library, add these require and include statements to the top of your script...


require 'win32/sound'
include Win32

Then, to play a sound file on the PC, call the Sound.play method, passing it the name of the file...


Sound.play('chimes.wav')
Sound.play('c:\sounds\hal9000.wav')

In the example above, you don't have to include the path to the file 'chimes.wav', because 'chimes.wav' is usually installed in the Windows folder. But in most cases, you'll want to include the full path to the sound file.

To generate a simple beep, call the Sound.beep method, passing it the tone frequency (in Hertz, between 37 and 32767) and the duration in milliseconds. For example, to play a low tone for half a second...


Sound.beep(100, 500)

...or to play an annoyingly high-pitched tone for 3 full seconds...


Sound.beep(5000, 3000)

The complete docs for this library can be found here.

Distributing your sound files with your application is also simple. You can embed your sound files in an executable created with RubyScript2Exe, or include them in an install package produced with Inno Setup.

Be careful not to overdo it, though, if your program is to be used by others besides yourself. It's a fine line between clever and annoying.

Thanks for stopping by!

FAQ: "script.rb" versus "script.rbw"

2007-05-15T19:52:00.000-05:00

What's the difference between ".rb" and ".rbw" files?

As you probably know, Windows associates certain filename extensions with certain programs. For example, double-click on a file with the ".xls" filename extension, and the file will be opened in Microsoft Excel (if it is installed). Use the One-Click Ruby Installer, and your ".rb" and ".rbw" files will be associated with the Ruby interpreter, so that double-clicking on a Ruby script automatically runs the script in the Ruby interpreter.

But there are two Ruby interpreters installed on Windows, ruby.exe and rubyw.exe. ruby.exe is the standard interpreter, which runs your script in a command/console window. rubyw.exe is essentially the same as ruby.exe, but without the console window, so any output to the console (ie, 'puts' statements or error messages) will not be seen.

Files with a ".rb" filename extension are associated with ruby.exe, while files with a ".rbw" filename extension are associated with rubyw.exe. Double-click on a ".rb" file in Windows Explorer and the script will open up a console window -- while it is running. The console window will automatically close when the script stops running. Double-click on a ".rbw" file in Windows Explorer and the script will run, but with no console window (and therefore no error messages).

To ensure you see the necessary console output from your script, run it from a command prompt, or from an editor/IDE that includes an output window.

For more answers to Frequently-Asked Questions, check out posts with the faq tag.

Thanks for stopping by!

FAQ: But don't I need an IDE?

2007-05-14T20:49:00.000-05:00

No. A good lightweight text editor is all you'll need to start developing serious applications. I recommend that you start out with a lightweight editor, so that you can first get comfortable with the language (which won't take long). But as you become more comfortable using Ruby and your projects grow in complexity, you may wish to investigate a full-blown Integrated Development Environment (IDE).

When you do go looking for an IDE, you'll now have several to choose from...

Ruby in Steel allows you to develop Ruby in Microsoft's Visual Studio IDE, while Aptana integrates the former RadRails for developing Ruby on the Eclipse platform. ActiveState's Komodo IDE is the big brother of the Komodo Editor. Borland's CodeGear division (the Delphi people) just announced plans to jump into the Ruby IDE market later this year.

As usual, Google for further details and opinions on this topic.

For more answers to Frequently-Asked Questions, check out posts with the faq tag.

Thanks for stopping by!

Ruby on Windows FAQs

2007-05-13T09:27:00.000-05:00

This series of posts is an attempt to consolidate and address some of the most frequently asked questions (FAQs) regarding using Ruby on Windows. You'll find these FAQ posts located under the faq tag.

There's no doubt more questions to be asked and answers to be provided. Please post a comment here or send me email.

Thanks for stopping by!

FAQ: What text editor should I use?

2007-05-13T09:14:00.000-05:00

Any text editor -- even Windows Notepad -- will do, so long as you can save your documents as straight text. You'll eventually want something a bit more robust than Notepad, such as the SciTE code editor that is installed by the One-Click Ruby Installer. More fully-featured code editors include the e Text Editor and ActiveState's Komodo Editor. Google for further details and recommendations.

For more answers to Frequently-Asked Questions, check out posts with the faq tag.

FAQ: How do I install new code libraries?

2007-05-13T09:01:00.000-05:00

The One-Click Ruby Installer includes RubyGems. RubyGems is a standard format for distributing Ruby programs and libraries, and provides an easy-to-use tool for managing the installation of gem packages. To install a new library such as watir via gem, open a command window and enter...


gem install watir

To uninstall, enter (you probably guessed this by now)...


gem uninstall watir

For complete details, check out the easy-to-read RubyGems User Guide.

FAQ: How do I get to a command prompt?

2007-05-13T08:58:00.000-05:00

Click your Windows Start button, then select Programs, then Accessories, then Command Prompt. Or click the Start button, select Run, type in 'cmd' and click OK. If you're not very familiar with the Windows command prompt, I recommend this brief but informative overview.

FAQ: How do I install Ruby on Windows?

2007-05-13T08:53:00.000-05:00

The One-Click Ruby Installer should meet your needs for a painless Ruby installation that includes most of the Ruby libraries you'll need starting out. It includes the SciTE Text Editor (my code editor of choice) and the WIN32OLE library, essential for COM automation.

For more answers to Frequently-Asked Questions, check out posts with the faq tag.

Automating the Windows Shell with Ruby

2007-05-06T11:18:00.000-05:00

The Microsoft Windows Shell provides a set of objects and methods that allow you to automate the Windows Shell with Ruby. You can use these objects and methods to access many of the Shell's functions.

Let's start with an example involving accessing your CD-ROM drive...

As usual, we'll start by requiring the win32ole library...


require 'win32ole'

Next, we'll create an instance of the Windows Shell object...


shell = WIN32OLE.new("Shell.Application")

Now, we'll call the shell object's NameSpace method to obtain a reference to the "My Computer" folder...


my_computer = shell.NameSpace(17)

The value passed to the NameSpace method represents a special folder ("My Computer" = 17).

To obtain a reference to the drive object, we'll call the NameSpace object's ParseName method, passing it the drive letter string for the CD-ROM drive...


cdrom = my_computer.ParseName("E:\\")

Shell objects such as drives and folders have a collection of Verbs that can be called upon. We can see the list if verbs available for a drive by iterating over the drive's Verbs collection and printing out the Name value...


cdrom.Verbs.each do |verb|
    puts verb.Name
end

The list of verbs may vary depending on the type of disc in the drive, but you may see something like this...


&Play
S&earch...
&Open
E&xplore
Auto&Play
Form&at
&Use with DLA
S&haring and Security...
Scan with &AVG Free
E&ject
&Copy
Create &Shortcut
P&roperties

Note the ampersand (&) in these verb names, which represent the context menu shortcut keys.

To perform an action represented by a Verb, locate the verb by Name, then call that Verb's doIt method. So, to eject your CD-ROM drive, you can do this...


cdrom.Verbs.each do |verb|
    verb.doIt if verb.Name == "E&ject"
end

Putting it all together, we could whip up a little CdRom class that encapsulates such functionality...


class CdRom

    attr_accessor :drive, :drive_letter, :verbs

    def initialize(drive_letter)
        my_computer = 17
        @drive_letter = drive_letter
        sh = WIN32OLE.new("Shell.Application")
        @drive= sh.NameSpace(my_computer).ParseName("#{@drive_letter}")
        @verbs = []
        @drive.Verbs.each do |verb|
            @verbs << verb.Name if verb.Name != ''
        end
    end

    def invoke_verb(verb_name)
        @drive.Verbs.each do |verb|
            verb.doIt if verb.Name== verb_name
        end
    end

    def eject
        self.invoke_verb("E&ject")
    end

    def open
        self.invoke_verb("&Open")
    end

    def explore
        self.invoke_verb("E&xplore")
    end

    def play
        self.invoke_verb("&Play")
    end

end

...which could be used like this...


cd = CdRom.new('d:\\')
puts cd.verbs
cd.eject

A tip of the hat goes to Masaki Suketa, who informed me (via the comp.lang.ruby group) that the standard InvokeVerb method does not currently work in the win32ole library, and to use the verb.doIt method instead.

That's all for now. As always, feel free to comment here or email me if you have special requests.

Thanks for stopping by!

Automating Internet Explorer with Ruby: Watir

2007-05-03T21:36:00.000-05:00

A reader has asked about automating Internet Explorer (IE) with Ruby. While you can do this using the win32ole library, I strongly recommend using the watir library instead.

WATIR (pronounced "water") stands for "Web Application Testing in Ruby". Watir is "a free, open-source functional testing tool for automating browser-based tests of web applications." Though it's designed for driving IE to test web applications, Watir is also very handy for automating IE in non-test scenarios. It leverages the win32ole library under the hood, but provides objects and methods that simplify the process.

To use Watir, first install the watir gem. Open up a Command Prompt and type the following...


gem install watir

...then, in your script, require the watir library...


require 'watir'

To create an instance of IE and navigate to a website...


ie = Watir::IE.new
ie.goto('http://rubyonwindows.blogspot.com')

To click on a link...


ie.link(:text, "win32ole").click
ie.link(:url, 'http://rubyonwindows.blogspot.com/search/label/win32ole').click

To input text into a text field...


ie.text_field(:name, 'login').set('MyLogin')

To select an option from a drop-down list...


ie.select_list( :name , "HallOfFame").select("Dave Concepcion")

To click a button...


ie.button(:value, 'Submit').click

To close the browser, call the close method...


ie.close

That's the basics. For further details and examples, you'll definitely want to check out the Watir User Guide.

For the complete Watir API reference, check out the rdocs here.

Various extensions for Watir have popped up, including WatirRecorder, WatirMaker, and WET Web Tester. Google for further details and updated links.

As always, post a comment here or send me an email with further questions or to request a topic for discussion.

Thanks for stopping by!

Automating Microsoft Project with Ruby

2007-04-29T12:22:00.000-05:00

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

2007-04-26T19:46:00.000-05:00

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

2007-04-25T06:59:00.000-05:00

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

2007-04-23T21:03:00.000-05:00

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

2007-04-17T19:30:00.000-05:00

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

2007-04-15T08:50:00.000-05:00

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

2007-04-14T07:31:00.000-05:00

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

2007-04-07T10:16:00.000-05:00

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

2007-04-06T16:15:00.000-05:00

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.