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!