Showing posts with label ruby. Show all posts
Showing posts with label ruby. Show all posts

Friday, September 2, 2011

Automating Outlook with Ruby: Saving Mail Messages To Files

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


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

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

require 'win32ole'

olHTML = 5
outlook = WIN32OLE.connect("Outlook.Application")
mapi = outlook.GetNameSpace("MAPI")
inbox = mapi.GetDefaultFolder(6)
msg = inbox.Items(2)
msg.SaveAs('c:\temp\message.htm', olHTML)

Further details on the SaveAs() method can be found here.
A full list of valid file type values can be found here.

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

Thanks for stopping by!

Wednesday, March 16, 2011

JRuby 1.6: Now With Win32OLE Goodness

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


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

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

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

Monday, February 7, 2011

Ruby on NetBeans: Here Comes the Cavalry

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!

Saturday, August 28, 2010

New Ruby IDE Discussion Group

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

Wednesday, February 10, 2010

Ruby & PowerPoint: Inserting HyperLinks

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

Setting the Scene


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


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

...create a new presentation...

doc = ppt.Presentations.Add()

...add a new slide...

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

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

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

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

ActionSettings and HyperLinks

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

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

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

action = text_range.ActionSettings(1)

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

link = action.Hyperlink

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

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

Our complete code looks like this:

require 'win32ole'

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

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

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

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

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

Thanks for stopping by!

Monday, August 24, 2009

Ruby & Word: Inserting Pictures Into Documents

In an earlier article, I explained how to insert an image into a range of cells in an Excel worksheet. Today we'll look at how to insert an image into a Word document.

Setting the Scene

Feel free to play along at home: Imagine that you have a Word document already open...


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

...and you want to insert an image from your PC...

image = 'C:\Pictures\Picture1.jpg'

...into a given Range object in the document:

range = doc.Sentences(2)

The InlineShapes Collection

The InlineShapes object represents a collection of pictures, OLE objects, and ActiveX controls contained within a given Document or Range object.

The AddPicture() Method

To insert an image into a document or range, we call the AddPicture() method on the InlineShapes collection. This method accepts up to four parameters:

* FileName (required) - The full path and filename of the image to insert.

* LinkToFile (optional) - If true the inserted picture will be linked to the file from which it was created. You'll usually set this to false to make the picture an independent copy of the file. The default value is false.

* SaveWithDocument (optional) - Set to true to save the linked picture with the document. The default is false. This value will be ignored unless LinkToFile is set to true.

* Range (optional) - A Range object representing the position in which to insert the picture.

The AddPicture() method returns a reference to the newly created InlineShape object that is your picture.

So, to insert an image at the start of the range that we defined earlier, we could call the method on our range object...

range = doc.Sentences(2)
pic = range.InlineShapes.AddPicture( { 'FileName' => image } )

...or we could call it on the Document object and pass the Range object as a parameter:

range = doc.Sentences(2)
pic = doc.InlineShapes.AddPicture( { 'FileName' => image,
'Range' => range } )

When providing all four parameters, the syntax looks like this:
    
pic = doc.InlineShapes.AddPicture( { 'FileName' => image,
'LinkToFile' => false,
'SaveWithDocument' => false,
'Range' => range } )

If your only parameter is the FileName, you could bypass the hash format and simply pass the string to the method, like this...

pic = range.InlineShapes.AddPicture( 'C:\Pictures\Picture1.jpg' )

To insert the image at the currently selected position, use the word.Selection.Range object:

pic = word.Selection.Range.InlineShapes.AddPicture( image )
pic = doc.InlineShapes.AddPicture( { 'FileName' => image,
'Range' => word.Selection.Range } )


And that's our show for today. Thanks for stopping by!

Wednesday, August 5, 2009

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

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

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


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

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

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

require 'win32ole'

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

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

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


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

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

And my thanks to you for stopping by!

Sunday, August 2, 2009

OCR: Converting Images to Text with MODI

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

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

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

Installing MODI


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

Accessing the MODI API

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


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

Loading the Image

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

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

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

Performing the OCR

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

doc.OCR()

...or with any of three optional parameters:

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

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

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

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

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

Getting the Text

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

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

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

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

Text, But Not Formatting

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

apple orange pear

...comes out as...

apple
orange
pear

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

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

Sunday, July 12, 2009

Ruby & Excel: Inserting Pictures Into Cells

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

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

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


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

Inserting the Image

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

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

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

Positioning the Image

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

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

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

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

Resizing the Image

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

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

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

pic.Width = range.Width

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

pic.Height = range.Height

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

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

pic.ShapeRange.LockAspectRatio = false

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

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

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

pic.Width = 400
pic.Height = 300

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

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

Thursday, July 9, 2009

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

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

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


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

Hiding Columns

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

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

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

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

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

ws.Columns.Hidden = false

Hiding Rows

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

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

...and to unhide them:

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

Hiding Worksheets

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

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

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

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

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

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

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

Sunday, June 28, 2009

wxRuby: Changing Text Fonts and Colours

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!

Tuesday, June 23, 2009

Ruby & Word: Inserting Tables

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!

Friday, June 12, 2009

Ruby & Word: Counting Words and Pages

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!

Thursday, June 4, 2009

Ruby & Word: Creating and Applying Styles

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!

Sunday, May 24, 2009

The OCRA Compiler: Tips, Tricks, and Gotchas

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!

Sunday, May 17, 2009

Handling WIN32OLE Events in Excel

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

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

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


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

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

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

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

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

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

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

loop do
WIN32OLE_EVENT.message_loop
end

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

ev.on_event('BeforeClose') do
exit_event_loop
end

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

$LOOP = true

def exit_event_loop
$LOOP = false
end

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

while $LOOP
WIN32OLE_EVENT.message_loop
sleep(0.1)
end

Our complete code looks something like this:

require 'win32ole'

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

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

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

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

$LOOP = true

def exit_event_loop
$LOOP = false
end

while $LOOP
WIN32OLE_EVENT.message_loop
sleep(0.1)
end

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

Thanks for stopping by!

Thursday, May 14, 2009

OCRA: One-Click Ruby Application Builder

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!

Monday, May 11, 2009

Ruby & Excel: Extracting Cell Comments

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!

Sunday, May 3, 2009

RubyScript2Exe and RubyGems

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!

Saturday, February 28, 2009

WIN32OLE Objects: Class Names and Methods

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

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

The code:


object.ole_obj_help.name

The explanation:

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

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

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

xl.ActiveSheet.ole_obj_help.name

...returns the string '_Worksheet'.

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

The code:

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

The explanation:

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

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

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

...but does not include a Close method:

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

There you have it. Thanks for stopping by!