Sunday, July 6, 2008

Working with Win32OLE Constants

Excel (and Word, Outlook, etc.) has hundreds of built-in constants that represent numeric values. When reviewing code written in Visual Basic, you may see these constants passed when calling methods or setting property values:


mychart.ChartType = xlColumnClustered

But the above line of code won't work on its own in Ruby, as xlColumnClustered won't be recognized as a constant. So, when translating this code to Ruby, how do you get it to work?

Do-It-Yourself

In my code examples here, I usually either provide the actual value...

mychart.ChartType = 51

...or explicitly assign the value to a constant or variable myself:

xlColumnClustered = 51
mychart.ChartType = xlColumnClustered

In Ruby, constants must begin with an upper-case letter, so 'xlColumnClustered' is really a variable in the last example above. To make it a constant, I should actually name it something like 'XlColumnClustered', with an upper-case X.

How did I know that the Excel constant xlColumnClustered equals 51? Well, I simply googled 'Const xlColumnClustered' and quickly found examples where the constant was being explicitly declared in VB/VBA code ("Const xlColumnClustered = 51"). Googling for 'Excel Constants' will return numerous pages that list all the the Excel constants and their corresponding values. Microsoft provides their own listing here.

Loading the Win32OLE Constants

But you don't have to do it yourself. Ruby's win32ole library allows you to load an object's built-in constants into a class or module. To do so, first create an empty class or module:

class ExcelConst
end

Then call the WIN32OLE.const_load method. Pass this method your previously-defined Excel application object and your new ExcelConst class:

WIN32OLE.const_load(excel, ExcelConst)

This loads the Excel application object's built-in constants into your ExcelConst class, but each constant will now begin with an upper-case letter, as required in Ruby. Now you can call Excel's built-in constants from your new ExcelConst class. So our original example...

mychart.ChartType = xlColumnClustered

...works with only a slight modification, inserting the name of our ExcelConst class and capitalizing the first letter of the constant:

mychart.ChartType = ExcelConst::XlColumnClustered

There you have it. This same method works for loading constants from other win32 application objects, such as Word or Outlook.

By the way, you can review the docs for the WIN32OLE library, including the const_load method, here.

I hope you found this useful. Feel free to post a comment here or email me if you have questions, comments, or suggestions for future articles (or the book).

Thanks for stopping by!

6 comments:

piyo said...

Truly a time-saving tip. Thank you.

Another reference for constants (albeit old, for Office 97) was this official download:
http://support.microsoft.com/kb/q112671/

It was good for Ruby use because the constants were copied to a text file.

John said...

Do you have any examples of how to generate Powerpoint presentations with Ruby? I can't seem to get past the step on adding a new slide.

David Mullet said...

@john:

I have just posted a new article on Automating PowerPoint with Ruby.

I'll post more in a later article, but this will hopefully get you started.

Thanks for the suggestion!

David

faiz said...

Thanks for the clear step-by-step. However I was not able to say: Application.Calculation = XlCalculationManual

using this procedure. Even though the proper constant was returned (-4135) the assignment statement in Ruby gave an error. I ended up using

ScreenUpdating = false

and

EnableCalculation = false

instead. Any ideas?

David Mullet said...

@faiz-

Be sure to fully qualify your constant with the namespace:

ExcelConst::XlCalculationManual

The following code runs for me:

-----
require 'win32ole'

class ExcelConst
end

excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, ExcelConst)
excel.Visible = true
wb = excel.Workbooks.Add
puts excel.Calculation # outputs -4105
excel.Calculation = ExcelConst::XlCalculationManual
puts excel.Calculation # outputs -4135
-----

If you still have problems with this, send me your code and I'll take a look at it.

David

faiz said...

Thanks David,

I'll try your tip. Another related information that took a long time googling (since I did not know what to look for) was Application.AutomationSecurity. It has 3 values (ByUI, ForceDisable, Low). I wanted to parse over 6500 XLS files but some of those had auto Macros that triggered dialog boxes (error or otherwise) that needed user interaction. Some went away with DisplayAlerts. But some persisted even when I put Maximum Security for Macros in Excel. Setting this property seems to have solved the problem. I will try to set the macro security to Low in the UI and then see if the Ruby code overrides it or not.