Ruby on Windows

ADO, Excel, and Data Types

noreply@blogger.com (David Mullet) — Sat, 03 Mar 2012 13:00:00 +0000

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

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

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

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

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

SELECT * FROM ORDERS WHERE ORDER_NUMBER = '12345' ;

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

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

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

More information can be found here.

Automating Outlook with Ruby: Saving Mail Messages To Files

noreply@blogger.com (David Mullet) — Sat, 03 Sep 2011 00:27:00 +0000

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

noreply@blogger.com (David Mullet) — Wed, 16 Mar 2011 12:01:00 +0000

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

noreply@blogger.com (David Mullet) — Mon, 07 Feb 2011 13:49:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 27 Jan 2011 13:27:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 26 Sep 2010 22:48:00 +0000

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

noreply@blogger.com (David Mullet) — Sat, 28 Aug 2010 19:03:00 +0000

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

noreply@blogger.com (David Mullet) — Wed, 10 Feb 2010 12:59:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 24 Jan 2010 12:55:00 +0000

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

noreply@blogger.com (David Mullet) — Wed, 20 Jan 2010 12:57:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 14 Jan 2010 13:11:00 +0000

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

noreply@blogger.com (David Mullet) — Mon, 24 Aug 2009 11:45:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 20 Aug 2009 11:38:00 +0000

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

noreply@blogger.com (David Mullet) — Wed, 05 Aug 2009 12:26:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 02 Aug 2009 20:33:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 12 Jul 2009 23:01:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 09 Jul 2009 12:34:00 +0000

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

noreply@blogger.com (David Mullet) — Mon, 29 Jun 2009 00:15:00 +0000

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

noreply@blogger.com (David Mullet) — Tue, 23 Jun 2009 13:00:00 +0000

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

noreply@blogger.com (David Mullet) — Fri, 12 Jun 2009 12:06:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 04 Jun 2009 13:00:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 24 May 2009 22:00:00 +0000

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

noreply@blogger.com (David Mullet) — Sun, 17 May 2009 16:03:00 +0000

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

noreply@blogger.com (David Mullet) — Thu, 14 May 2009 12:12:00 +0000

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

noreply@blogger.com (David Mullet) — Mon, 11 May 2009 12:16:00 +0000

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!