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!