ADO, Excel, and Data Types

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

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

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

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

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

    SELECT * FROM ORDERS WHERE ORDER_NUMBER = '12345' ;

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

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

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

More information can be found here.

Automating Outlook with Ruby: Saving Mail Messages To Files

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

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

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

require 'win32ole'

olHTML = 5

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

mapi = outlook.GetNameSpace("MAPI")

inbox = mapi.GetDefaultFolder(6)

msg = inbox.Items(2)

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

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

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

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

Thanks for stopping by!

JRuby 1.6: Now With Win32OLE Goodness

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

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

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

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

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!

NetBeans Drops Support for Ruby and Rails

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

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

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

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

Setting the Scene

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

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

...create a new presentation...

doc = ppt.Presentations.Add()

...add a new slide...

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

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

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

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

ActionSettings and HyperLinks

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

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

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

action = text_range.ActionSettings(1)

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

link = action.Hyperlink

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

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

Our complete code looks like this:

require 'win32ole'

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

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

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

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

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

Thanks for stopping by!

Saving Microsoft Office Documents as PDFs

A recent discussion in the Ruby Forum reminded me that it is possible with Microsoft Office 2007 applications to save a document in Adobe PDF format.

In the Microsoft Word object model, you can call the Document object's SaveAs() method, passing it a filename, and the document will be saved in the default format.

document.SaveAs('c:\temp\MyDocument.doc')

But the SaveAs() method accepts an optional second parameter, an integer that specifies the file format. In the Word object model, the PDF format is represented by the value 17. So, where document represents your Document object, you can do the following:

document.SaveAs('c:\temp\MyDocument.pdf', 17)

In Microsoft Excel, the Workbook object's SaveAs() method accepts the value 57 to specify PDF format:

workbook.SaveAs('c:\temp\MyWorkbook.pdf', 57)

And in Microsoft PowerPoint, the magic number is 32:

presentation.SaveAs('c:\temp\MyPresentation.pdf', 32)

You can find enumerations for file types over at MSDN:

• Microsoft Word
  
• Microsoft Excel
  
• Microsoft PowerPoint
  

A tip of the hat to Joe Peck for mentioning this in the Ruby Forum.

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

Connecting to One of Many Open Documents

If you've hung around here for a little while, then you probably already know that you can use the WIN32OLE.connect() method to connect to a running instance of applications like Microsoft Word. Just pass the method the ProgID of the Application object:

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

That works great if you have just one instance of the application running, with one or multiple documents open. But suppose you have multiple instances of the application running? How can you be sure that you connect to the instance with Document B, and not the instance with Document A?

Well, assuming that Document B has been saved and you know the full filename, then you can connect to the Document object, rather than to the Application object. And you do this by passing the WIN32OLE.connect() method the full filename:

document = WIN32OLE.connect('C:\Path To File\DocumentB.doc')

This returns an instance of the Word.Document object. You can then grab
an instance of that Document's Application object:

word = document.Application

This works with other multiple-instance applications, such as Excel. The following code connects to a specific instance of an Excel.Workbook object:

workbook = WIN32OLE.connect('C:\Path To File\WorkbookB.xls')
xl = workbook.Application

By the way, some applications, such as PowerPoint, are single-instance applications, so there will never be multiple instances of the application running, avoiding the need to use the process outlined above.

There you have it. Please let me know if you have specific questions or suggestions for future articles.

Thanks for stopping by!

Creating an iTunes Song Inventory in Excel

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

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

require 'win32ole'

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

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

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

data = []

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

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

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

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

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

data.sort!

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

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

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

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

...and make the application window visible:

xl.Visible = true

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

wb = xl.Workbooks.Add()

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

ws = wb.Worksheets(1)

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

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

Then we insert our data:

rng.Value = data

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

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

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

ws.Rows(1).AutoFilter()

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

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

Thanks for stopping by!

Ruby & Word: Inserting Pictures Into Documents

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

Setting the Scene

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

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

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

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

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

range = doc.Sentences(2)

The InlineShapes Collection

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

The AddPicture() Method

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

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

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

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

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

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

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

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

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

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

When providing all four parameters, the syntax looks like this:

    
pic = doc.InlineShapes.AddPicture( { 'FileName' => image, 
                                     'LinkToFile' => false, 
                                     'SaveWithDocument' => false, 
                                     'Range' => range } )

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

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

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

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

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

About the Book

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

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

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

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

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

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

require 'win32ole'

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

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

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

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

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

And my thanks to you for stopping by!

OCR: Converting Images to Text with MODI

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

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

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

Installing MODI

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

Accessing the MODI API

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

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

Loading the Image

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

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

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

Performing the OCR

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

doc.OCR()

...or with any of three optional parameters:

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

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

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

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

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

Getting the Text

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

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

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

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

Text, But Not Formatting

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

apple    orange    pear

...comes out as...

apple
orange
pear

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

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

Ruby & Excel: Inserting Pictures Into Cells

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

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

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

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

Inserting the Image

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

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

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

Positioning the Image

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

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

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

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

Resizing the Image

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

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

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

pic.Width = range.Width

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

pic.Height = range.Height

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

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

pic.ShapeRange.LockAspectRatio = false

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

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

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

pic.Width = 400
pic.Height = 300

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

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

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

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

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

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

Hiding Columns

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

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

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

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

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

ws.Columns.Hidden = false

Hiding Rows

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

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

...and to unhide them:

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

Hiding Worksheets

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

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

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

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

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

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

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

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!

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!

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!