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!