Friday, March 30, 2007

Automating Excel with Ruby: The Application Object

Automating Microsoft Excel can involve hundreds of objects, each with its own properties and methods. Let's start by looking at the top-level Application object.

As previously mentioned here, you'll use the win32ole library to create a new instance of the Excel application object:


require 'win32ole'
excel = WIN32OLE.new('Excel.Application')

...or to connect to an existing instance of the Excel application object:

excel = WIN32OLE.connect('Excel.Application')

Note that a new instance of Excel will be not visible by default. To show it, set the application object's Visible property to true:

excel.Visible = true

You may want to first hide Excel until your data input and formatting process is complete, then make it visible. This may speed things up, and prevents the user from interfering with your program (and vice versa).

To make Excel visible, but block user input, set the Application object's Interactive property:

excel.Interactive = false

To turn off screen updating, set the ScreenUpdating object's Interactive property:

excel.ScreenUpdating = false

This can help speed up processes that involve updating a large number of cells. Just don't forget to return ScreenUpdating and Interactive to true when completed.

You may wish to define the number of worksheets included in a new workbook, to ensure you have the exact number of worksheets that you will be using. This can be done by getting or setting the application object's SheetsInNewWorkbook value:

excel.SheetsInNewWorkbook = 3

This value is retained in the application beyond this session. So, if you're including this code in an app to be run on end-users' machines, it may be polite to reset the SheetsInNewWorkbook value to what the user had previously set, after adding your workbook:

number_of_sheets = excel.SheetsInNewWorkbook
excel.SheetsInNewWorkbook = 3
workbook = excel.Workbooks.Add
excel.SheetsInNewWorkbook = number_of_sheets

Actions such as deleting worksheets or overwriting existing files may result in an alert box asking you to confirm the action. To disable such alerts, set the Excel Application object's DisplayAlerts property:

excel.DisplayAlerts = false

To exit Excel, call the application object's Quit method:

excel.Quit

That's all for now, as I try to keep individual posts brief. Next, we'll look at the Workbook object. Feel free to leave a comment or send me email if there are specific items you would like to see discussed here.

Thanks for stopping by!

Digg my article

4 comments:

Anonymous said...

Hi, David,

do you have any idea how to do it on mac?

David Mullet said...

@anon:

Unfortunately, I have no real experience with the Mac (though I often envy Mac users); and couldn't find any reference to using Excel on a Mac in the comp.lang.ruby newsgroup.

Sorry.

Unknown said...

Hi David, and thanks for the tutorial.
Do you know why i have a 'wrong number of arguments (0 for 1)' error when colling excel.Quit??

Graeme said...

Hi

How do I 'put' out into a specific cell in a predefined excel spreadsheet? I need to add pass and fails into a column in a test report - Thanks