So, you know how to create the major WIN32OLE objects that you need, such as Excel's application, workbook, and worksheet objects. But what can you really do with them? Ruby allows you to use Object.methods to get a list of methods that can be called on the Object:
"Baseball".methods
...returns the following array of String methods...
["methods", "instance_eval", "%", "rindex", "map", "<<", "split", "any?",
"dup", "sort", "strip", "size", "instance_variables", "downcase", "min", "gsub!",
"count", "include?", "succ!", "instance_of?", "extend", "downcase!", "intern",
...
"object_id", "length", "entries", "chomp", "=~", "require", "public_methods",
"upcase", "sub!", "squeeze", "__send__", "upcase!", "crypt", "delete!", "equal?",
"freeze", "detect", "zip", "[]", "lstrip!", "center", "[]=", "to_f"]
For easier browsing, you can sort this array by calling the sort method...
"Baseball".methods.sort
This is handy when you need to call a Ruby method that you don't recall the exact name of. Scan the list of methods for the object you're working with.
Call this on an object created through win32ole, and you see this...
excel = WIN32OLE.new('Excel.Application')
excel.methods
["methods", "instance_eval", "dup", "_setproperty", "instance_variables",
"instance_of?", "extend", "eql?", "ole_func_methods", "each", "hash", "id",
"singleton_methods", "setproperty", "taint", "frozen?", "instance_variable_get",
...
"ole_get_methods", "nil?", "untaint", "gem", "send", "ole_obj_help", "display",
"inspect", "clone", "=~", "object_id", "_getproperty", "require",
"public_methods", "__send__", "equal?", "freeze", "ole_put_methods", "[]", "[]="]
Looks like the fairly generic Object methods, doesn't it? What would be helpful, in regards to OLE/COM automation, would be a method that returns the OLE methods of the object. Fortunately, the win32ole library provides just that in Object.ole_methods. This method returns an array of WIN32OLE objects. To get a list of methods for the Excel application object...
excel = WIN32OLE.new('Excel.Application')
excel.ole_methods
=> [QueryInterface, AddRef, Release, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames,
Invoke, Application, Creator, Parent, ActiveCell, ActiveChart, ActiveDialog,
ActiveMenuBar, ActivePrinter, ActivePrinter, ActiveSheet, ActiveWindow,
...
CalculateFull, FindFile, CalculationVersion, ShowWindowsInTaskbar, ShowW
indowsInTaskbar, FeatureInstall, FeatureInstall, GetTypeInfoCount, GetTypeInfo,
GetIDsOfNames, Invoke]
You can't sort this array of WIN32OLE objects as-is, but you can convert each element to a string and then sort the array of strings...
excel.ole_methods.collect!{ |e| e.to_s }.sort
["ActivateMicrosoftApp", "ActiveCell", "ActiveChart", "ActiveDialog",
"ActiveMenuBar", "ActivePrinter", "ActivePrinter", "ActiveSheet", "ActiveWindow",
"ActiveWorkbook", "AddChartAutoFormat", "AddCustomList", "AddIns", "AddRef",
...
"Width", "Width", "WindowState", "WindowState", "Windows", "WindowsForPens",
"Workbooks", "WorksheetFunction", "Worksheets", "_Default", "_Evaluate",
"_FindFile", "_Run2", "_WSFunction", "_Wait"]
Note that a similar method exists, ole_get_methods, which does not return the exact same results as ole_methods. The WIN32OLE documentation does not make clear (to me) the difference between these two methods. If ole_methods does not provide what you are looking for, try ole_get_methods.
A graphical alternative to the above Ruby methods is to use an OLE Object Browser, such as the one included with Excel's Visual Basic Editor. To launch it from Excel or Word, select Tools => Macro => Visual Basic Editor. From the VB Editor, select View => Object Browser. Similar third-party and open source OLE browsers are also available.
Okay, now you've got a list of WIN32OLE methods reference you can do something with. Browse through this list of meaningful method names and you'll be on the road to gaining some valuable insight into all that you can do with a given WIN32OLE object. Now that you have a method name, go to Google and search for it (ie, excel DisplayInfoWindow) and you'll find details on the method/property and how you may use it.
13 comments:
I had always wondered how to do this. These articles are very helpful, please keep them coming!
Just for clarity, the line:
xl.ole_methods.collect!{ |e| e.to_s }.sort
should be
excel.ole_methods.collect!{ |e| e.to_s }.sort
This blog is excellent! I came across this after about 4 hours of googling my way around. Just the kind of info I've been looking for. Keep them coming!
Quite a useful post, thanks ...
A few other ways of inspecting objects that may be of help:
1. To get the methods of an object that is a (direct) subclass of Object (without the methods of Object itself), such as the excel object in your post, use:
excel.methods - Object.methods
Also, if the object has several ancestors in its inheritance hierarchy, to get only the methods defined by the class to which the object belongs, use:
(Assume class C inherits from class B which in turn inherits from class A)
c_object.methods - c_object.ancestors.methods
(haven't checked the above one - it might not work as .ancestors returns an array if I remember right, so it might end up 'subtracting' the methods of Array instead. We might have to use code in a loop to 'subtract' the methods of each of the ancestors, from the the c_object object:
c_object_methods = c_object.methods
c_object.ancestors.each do
|ancestor|
c_object_methods -= ancestor.methods
end
2. To find out if an object supports a method name containing some substring, use:
object.methods.grep /substring/
e.g.
excel.methods.grep /ole/
to find methods of the excel object whose names contain the substring 'ole'.
or use:
excel.methods.grep /ole/i
to ignore case in the grep (search).
Vasudev Ram
Site: http://www.dancingbison.com
Blog: http://jugad.livejournal.com
You can define this new method for WIN32OLE_METHOD class
class WIN32OLE_METHOD
def <=>( other )
self.to_s <=> other.to_s
end
end
Thanks to all for your comments! I'll keep the articles coming, so long as I have something useful to contribute.
dwhite618: Thanks for catching my typo! I have corrected it in the article.
Vasudev & Stephane: Thanks for adding your valuable input! Isn't this a Beautiful Language?
>You're welcome. Yes, it sure is! :-)
In fact, it sparkles :)
For completeness - you can also provide a block like this:
excel.ole_methods.sort { |a,b| a.to_s <=> b.to_s }
Though I prefer Stéphane's advice
Thanks for the blog!
Hi guys
I'm using Netbeans, and it's giving me 'no such file to load' error when I have "require 'win32ole'" in the code.
Any Ideas how to load the 'win32ole' library to the Netbeans IDE 6.1 ?
Regards
Peter
Peter-
Using NetBeans, my first thought is to wonder if your project is set to use the built-in JRuby platform (the NetBeans default), rather than the standard Ruby platform.
The win32ole library is not yet available on the JRuby platform.
Right-click on the project in the Projects (or Files) window and select Properties. In the Project Properties dialog, select Run and verify that the Ruby Platform value is not set to "Built-in JRuby". If it is, click the drop-down list and select "Ruby (1.8.6)" or whatever the alternative is.
David
thanks... that was so helpful... in the same way can you guide me in parsing the word documents using ruby on rails...
hi David, thank you for your excellent post.
i am able to run your "notepad" version, but once i changed to
"\"C:\\Program Files\\Microsoft Office\\OFFICE11\\POWERPNT.EXE\""
it failed, eventhough I am able to run (& already open) powerpnt using above line.
could you advice?
when I use this
require 'win32ole'
excel = WIN32OLE.new('Excel.Application')
I get this error
WIN32OLERuntimeError: unknown OLE server: 'Excel.Application'
DOnt matter wha I use alw the same error I tried w
WIN32OLE.new('word.Application')
WIN32OLE.new('Word.Application')
WIN32OLE.new('wordpad.Application')
WIN32OLE.new('WordPad.Application')
Im have Ruby 1.9 on Windows env
Post a Comment