The OCRA Compiler: Tips, Tricks, and Gotchas

I've previously mentioned Lars Christensen's One-Click Ruby Application Builder, a "compiler" for Ruby scripts that shows a lot of potential (and current value). There's not much documentation yet, but folks are installing it, using it, and providing feedback. It works right out of the box for many purposes, but here are a few things to keep in mind as you use it...

"Failed to create directory" Error

One user reported receiving the error "Failed to create directory" when running the compiled executable. As a possible workaround, try running ocra.rb from the directory where your script is located. For example, instead of running...

ocra.rb "C:\code\rubyscripts\application.rbw"

...navigate to the "C:\code\rubyscripts" directory, then run:

ocra.rb application.rbw

Note that while this resolved the problem on my machine, it didn't help the person who originally reported the problem.

Require RubyGems

When you compile your script (which uses one or more gems) with OCRA and then run the executable, you may receive a 'no such file to load' error. Try adding the line...

require 'rubygems'

...to the top of your script, above the other require statements. I've found that a script that runs pre-compiled without this statement may not execute once compiled.

Compile Without Running

I mentioned earlier that it would be nice to have an option to avoid fully running the script, similar to RubyScript2Exe's exit if RUBYSCRIPT2EXE.is_compiling? idiom. A tip of the hat goes to reader "BackOrder", who offered the following solution:

exit if Object.const_defined?(:Ocra)

Put all your require statements at the top of your code, followed by this line.

OCRA and Mechanize: "libxml2.dll not found"

There may be a problem compiling a script that requires the mechanize gem. Running the compiled executable resulted in a "libxml2.dll was not found" error on my machine.

Note that the above observations relate to version 1.0.2 (current as of this writing) of the ocra gem.

More information is available in the OCRA forums and bug tracker.

Thanks again to Lars for creating OCRA, and to the users who have installed it, used it, and provided their feedback.

Thanks for stopping by!

OCRA: One-Click Ruby Application Builder

I recently mentioned the fact that RubyScript2Exe 0.5.3 doesn't play well with recent versions of RubyGems. At the end of that post I mentioned that there are alternatives emerging for creating executables from your Ruby code, including Lars Christensen's OCRA, the One-Click Ruby Application Builder.

I've had a chance to take OCRA for a short test drive and it looks promising. Like Erik Veenstra's RubyScript2Exe, OCRA lets you "compile" your ruby code into an EXE file that you can distribute to others, without requiring that the users have Ruby installed on their PCs. To quote the OCRA page:

The executable is a self-extracting, self-running executable that contains the Ruby interpreter, your source code and any additionally needed ruby libraries or DLL.

OCRA can be installed via RubyGems: open a console window and type:

gem install ocra

It's also available from the RubyForge page. Version 1.0.2 is the latest as of this writing.

The syntax to compile a script is...

ocra.rb [option] your/script.rb

...where option is one or more of the following:

--dll dllname    Include additional DLLs from the Ruby bindir.
--no-lzma        Disable LZMA compression of the executable.
--quiet          Suppress output.
--help           Display this information.
--windows        Force Windows application (rubyw.exe)
--console        Force console application (ruby.exe)
--no-autoload    Don't load/include script.rb's autoloads

Thus far, I have exercised only the --windows and --console options.

To create a non-console application, either use the --windows option or give your script the .rbw filename extension. Compiling a basic non-console script is as simple as opening a console window, navigating to the folder containing your script, and typing:

ocra.rb myscript.rbw

OCRA will then run your script to check for dependencies, gather the necessary files, and create your executable. An option to avoid fully running the script would be nice, similar to RubyScript2Exe's exit if RUBYSCRIPT2EXE.is_compiling? idiom.

OCRA uses LZMA compression, so the resulting executable is relatively small. A simple wxRuby app, for example, resulted in a 2.5 Mb executable. The same app compiled with RubyScript2Exe weighed in at over 9 Mb. Apps that do not require a GUI toolkit will be even smaller, perhaps 500k.

I haven't spent a lot of time with OCRA yet, but I think it shows great potential and I want to thank Lars Christensen for his efforts. If you have a need to distribute Ruby programs to non-technical users, you should check it out and pass your feedback along to Lars.

Thanks for stopping by!

RubyScript2Exe and RubyGems

I've written here previously about Erik Veenstra's RubyScript2Exe "compiler". I use it frequently to create standalone EXE files on Windows.

If you use RubyScript2Exe, you should note that the current version (0.5.3) doesn't appear to play well with the new version (1.3.2) of Rubygems released a few weeks ago. Attempting to compile a script results in the error:

    undefined method `list' for Gem::Specification:Class

Some additional discussion can be found in this Ruby Forum thread.

Perhaps Erik will update RubyScript2Exe to utilize the Gem.loaded_specs method, as suggested by Eric Hodel.

In the meantime, RubyGems 1.3.1 continues to work well for me with RubyScript2Exe. Be aware, however, that Daniel Berger points out in the above thread that version 1.3.2 addresses a couple issues related to the win32-file library.

There are alternatives emerging for creating executables from your Ruby code, including ocra and crate. I haven't had the opportunity to try either of these yet, but plan to in the near future. If you have experience with these or similar tools, feel free to share your comments here. Thanks!

Automating Excel with Ruby: One Cell, Multiple Fonts

As a loyal reader mentions in the comments to my article about Formatting Worksheets, he wanted to apply different formatting to separate words in a single cell. Specifically, he had cells containing a surname, a comma, and then first name, such as:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

His goal was to format the last name in bold, while leaving the first name in normal weight font:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

(BTW, the sample names are mine. How many days until Opening Day? Or at least until pitchers and catchers report? But, alas, I digress...)

My suggested solution was to use the Characters object to get a reference to a specific subset of characters within the cell, rather than to all of the cell's text.

Call the Characters(start, length) method on a Range object (usually a cell) to return a reference to the specific characters. Note that the Characters object's index is 1-based.

Once you have your subset of characters you can apply Font formatting much like you would with a Cell object, setting the Font object's Name, Size, Bold, ColorIndex, and other properties.

So, assuming you have a worksheet object, ws, and you want to format the values in cells A1 through A3 as described above, try the following code:

ws.Range('A1:A3').Cells.each do |cell|
    # find the index of the first comma:
    comma = cell.Value.to_s.index(',')
    # get the range of characters before the comma
    lastname = cell.Characters(1, comma)
    # get the range of characters after the comma
    firstname = cell.Characters(comma + 2, cell.Value.to_s.size)
    # apply formatting:
    lastname.Font.Bold = true
    firstname.Font.Bold = false
end

There you have it. Let me know if you have questions or comments.

Thanks for stopping by!

Amen, Brother

In an article entitled "The Best Environment for Rails on Windows", Fabio Akita writes:

The same goes for Windows: some Railers would say that you need to give up on Windows completely and simply go to Ubuntu or Mac OS X. I know for a fact that there are lots of people simply unable to do just that. And “give up their jobs” – as some kindly suggest – is not an option. Some of the people that are starting Rails in closed-minded companies are exactly the seeds those companies need to start to change their minds and every time we, Railers, tell them to just move to another job, we are killing a precious opportunity to introduce Rails to those companies.

In a followup comment to a post on Rails Inside, Fabio adds:

Please keep this in mind: there are several circumstances that keep people locked to Windows. Specially if this person works for a closed minded company that won't allow him to use Ubuntu. The instant you tell this person "give up, Ruby is unable to be used under Windows", you lose the single person that could actually bootstrap Ruby adoption inside this company. People have to start somewhere and sometimes Windows is this way. So let's help them get started and one of three things will happen: 1) he will be super happy and will evangelize Ruby to his peers, leading to mass adoption on this company; 2) he will lose hope on his employer and will try to find another job; 3) he will simply give up and none of us lose nothing.

You should read Fabio's article and the comments at both links above; even if, like me, you don't use Rails as much as you use Ruby itself.

It's very easy (and very fashionable -- all the cool kids do it!) for the Mac OS and Linux crowds to take shots at the Windows developers. And I tip my hat to those that actually changed jobs just so that they could avoid working with Windows. Good for you.

But some of us actually like our jobs and our companies, despite having to work with -- Gasp! Cover the children's ears, Mother! -- Windows. Besides, as Marcos Ricardo commented on Fabio's site, "We can never throw way 90% of market share."

Scripted GUI Testing with Ruby

Anyone who reads this blog knows that I love Ruby, and I love Automation.

Ian Dees is wise enough to realize that automation is not the Ultimate Solution to Everything, pointing out that "some domains are better suited than others for automation". "So," he asks, "why not let the computers and people each do what they're good at?" To that end, he offers his new book "Scripted GUI Testing with Ruby", a book for testers who code and coders who test -- and maybe for others, as well.

As the title implies, test scripts are written in the Ruby language -- and its Java implementation, JRuby -- and the author assumes that readers will have some experience with Ruby. If you've written and run a few Ruby scripts, you'll be fine. Ian doesn't require you to be a black-belt Rubyist to understand what's going on here, and his humor helps keep it interesting.

Ian's guinea pig for client-side testing is LockNote, a simple text editor that saves your notes with password-protected encryption. The program is freely available for Windows, and Ian has developed his own cross-platform Java/Swing version, dubbed "JunqueNote". Using these two applications, Dees teaches us how to automate testing of GUI applications on both the Windows and Java platforms. You'll learn how to launch the app and use API calls to find windows, automate keystrokes and mouse-clicks, and more. This can be valuable as either a means to an end, or as the end goal itself; whether you're testing software, or simply looking to automate it via the user interface.

In one chapter, Dees provides a gentle introduction to the popular RSpec Behaviour Driven Development framework. In another, he shows how we can simplify our test code by separating out the common code from the platform-specific code.

Because the focus is on software testing, the author devotes a chapter to leveraging randomness to expose bugs that might otherwise be missed. Another chapter focuses on the ZenTest test matrix library. A later chapter delves into testing web-based applications using Selenium and WATIR, and how to combine these with RSpec.

As a Ruby on Windows advocate, I'm pleased to see a book that devotes more than just a half-dozen pages to Windows-specific task automation. But "Scripted GUI Testing with Ruby" spends a good deal of time discussing Java-based testing, as well.

This book is targeted at software testers, and they'll certainly be the section of the market that gets the maximum value from it. But it has potential value beyond that niche. There's something useful to be learned by both testers and non-testers, on both Java and Windows platforms.

Thanks for stopping by!

Automating PowerPoint with Ruby

Here at Ruby on Windows, we've looked at how to automate a variety of applications using Ruby. One popular MS Office app that I haven't yet discussed is PowerPoint, Microsoft's ubiquitous presentation software. But, like other Office apps, PowerPoint exposes a full-featured object model for automating, so there's virtually nothing that you can't do with PowerPoint via Ruby code.

As usual, we'll be leveraging the win32ole library:

require 'win32ole'

We'll start by creating an instance of the PowerPoint Application object, and assigning that object to a variable we'll call ppt :

ppt = WIN32OLE.new('PowerPoint.Application')

Note that PowerPoint (like Outlook and Project) is a single-instance application. This means that if an instance of PowerPoint is already running, that instance will be returned, even though you called the WIN32OLE.new method. If PowerPoint is not currently running, a new instance will be launched.

Like most MS Office applications, the PowerPoint application window is not visible by default when launched from code. To show or hide the Application window, set the Visible property...

ppt.Visible = true

The Presentations collection contains the list of presentation documents currently open. To create a new Presentation, we'll call the Presentations.Add() method...

doc = ppt.Presentations.Add()

To open an existing presentation, we'll call the Presentations.Open() method and pass it the path and name of the PPT file...

doc = ppt.Presentations.Open('c:\docs\mully1.ppt')

The Presentations.Add() and Presentations.Open() methods add the new Presentation object to the Presentations collection, and return a reference to the Presentation object. As you can see above, we're assigning that object to a variable we've called doc.

The Presentation object contains a collection of Slide objects. To add a new slide to your presentation, call the Slides.Add() method and pass it two integers as arguments: the position at which to insert the new slide, and the layout to use for the new slide...

ppLayoutText = 2
slide = doc.Slides.Add(1, ppLayoutText)

The position argument must be between 1 and the current number of slides plus 1.

The Slides.Add() method adds the new slide to the Slides collection and returns a reference to the new slide, which we've assigned to the variable slide.

To get the current count of slides, call the Slides.Count() method.

So, we could use this code to add a new blank slide to the end of the collection:

slide = doc.Slides.Add(doc.Slides.Count + 1, ppLayoutBlank)

You can find a complete list of Slide Layout constants in this Microsoft document.

Each slide object contains a Shapes collection of all objects on the slide, such as textboxes, pictures, and OLE objects.

In the words of Microsoft, a shape object's TextFrame "contains the text in the text frame and the properties and methods that control the alignment and anchoring of the text frame". The TextFrame object's TextRange property "returns a TextRange object that represents the text in the specified text frame". You can get/set the text via the TextRange object's Text property. Got all that? There'll be a quiz later.

Putting this all together, we can insert or change the text of the first textbox on our slide using a line of code like this:

slide.Shapes(1).TextFrame.TextRange.Text = "Hello, World!"

To save your newly-created presentation, call the SaveAs() method and pass it the path and filename:

doc.SaveAs('c:\docs\presentation1.ppt')

To save changes to a previously-saved presentation, call the Save() method:

doc.Save()

Call the presentation object's Close() method to close the presentation, and call the application object's Quit() method to exit the PowerPoint application:

doc.Close()
ppt.Quit()

Well, that's our show for today. There's much more to be covered on this topic. Please let me know if you have specific questions or suggestions for future articles.

Thanks for stopping by!

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!

Ruby on Windows: The Book

A reader recently commented:

"Have you written a book on Ruby + Windows? I'd love to see the material expanded and put down on paper. I suppose I can print it out myself, but it's just not the same."

I've been asked about this before. While I currently have no such "Ruby on Windows" book to offer, I can now say that I have finally started on it.

I welcome any comments, suggestions, or advice you may have -- as readers and/or writers.

Automating Excel: Chart, Axis, and Legend Titles

A reader writes, "I need to use Ruby to automate Excel... How can I set the chart title, axes title, or legend name, etc in the chart by using Ruby?" I confess that my knowledge of Excel charts is limited, but let's dive in and see what we can learn...

As usual, you'll start by using the win32ole library. For our example, we'll connect to a running instance of Excel:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')

Referencing a Chart Object

Let's assume that ws is your worksheet object. You can obtain a reference to a Chart object either...

...via the Charts collection, if it is a chart sheet...

chart = ws.Charts(1).Chart

or via the ChartObjects collection, if it is an embedded chart (not a chart sheet)...

chart = ws.ChartObjects(1).Chart

...or via the ActiveChart method if the chart is the currently active object:

chart = xl.ActiveChart

Setting the Chart Title

In order to set the value of a chart's title, you must ensure that the HasTitle property is enabled:

chart.HasTitle = true

Then set the value of the ChartTitle.Text property:

chart.ChartTitle.Text = "2008 Sales"

Setting the Axes Title

Select one of the chart's axes by calling the Chart object's Axes method and passing it an integer representing either the category, value, or series axis:

XL_CATEGORY = 1
XL_VALUE = 2
XL_SERIESAXIS = 3
axis = chart.Axes(XL_CATEGORY)

Much like with the chart title above, in order to set the value of an axis' title, you must ensure that the HasTitle property is enabled:

axis.HasTitle = true

Then set the value of the ChartTitle.Text property:

axis.AxisTitle.Text = "July Sales"

Setting the Legend Names

You can reference a legend via the Chart object's SeriesCollection method, passing it the (1-based) index of the item you wish to reference:

chart.SeriesCollection(1).Name = "Jan"
chart.SeriesCollection(2).Name = "Feb"
chart.SeriesCollection(3).Name = "Mar"

And there you have it. If you found this to be useful, you may want to check out this Microsoft MSDN Chart object reference.

Got a question, comment, or suggestion? Post your comment here or send me an email.

Thanks for stopping by!

Shutting Down Windows from Ruby Code

Someone recently asked how to shut down or restart Windows from their Ruby code. Windows provides an executable, "shutdown.exe", to accomplish this. Execute this command from your script with the appropriate parameters and you can log off, shutdown, or restart the OS.

Parameters accepted include, but are not limited to:

-l        Log off (cannot be used with -m option)
-s        Shutdown the computer
-r        Shutdown and restart the computer
-t xx     Set timeout for shutdown to xx seconds
-f        Forces running applications to close without warning

So, the following line of code will close all applications without a warning and instantly shut down and restart your PC:

system('shutdown.exe -r -f -t 0')

To get the full list of parameters, open a console window and enter

shutdown.exe /?

Further details can be found in this Microsoft article. And Gordon Thiesfeld offers WMI and win32ole alternatives in this Ruby Forum thread.

Thanks for stopping by!

Windows: Ruby's Red-Headed Stepchild

In case you missed it, there has been increased online discussion these past few days regarding the viability of Windows as a development platform for Ruby. This was triggered by at least two blog posts...

Luis Lavena:

"Is Windows a supported platform for Ruby? I guess not"

Peter Cooper:

"Is Windows a first class platform for Ruby, or not?"

"All the cool kids are doing it."

I've been disappointed by some of the readers' comments to these posts. As in so many discussions within the open source community, there's a significant anti-Microsoft sentiment. There are many reasons to dislike Microsoft and Windows. But those who wonder aloud if anyone still uses Windows, or that comment that "real programmers don't use Windows" are either tragically ignorant or blinded by their passion for Linux and/or MacOS. Yes, I know, we're wandering back towards the Ruby in the Enterprise debate.

“The race is not always to the swift, nor the battle to the strong, but that's the way to bet.” -Damon Runyon

Fortunately, there are voices of reason in this discussion, including Peter Cooper, who, while not a Windows user, says "a poor Windows Ruby ecosystem could harm the growth and spread of the language". Whether you like it or not, Windows is far and away the most popular desktop OS. Many desktop developers in the [insert Darth Vader/Evil Empire theme here] Corporate World are required to develop for Windows because that's where the users are. We might be able to "sneak Ruby through the system", but expecting a company to switch hundreds or thousands of users from Windows to Linux or Mac is childish.

If you love Ruby, and care about the future of The Language, then you want Windows to be a "first-class platform for Ruby". It's not about developer preferences. It's about market forces.

Ruby is more than just Rails.

Software development is more than just websites.

Ruby needs Windows more than Windows needs Ruby.

Think about it.

RubyGarden Archives: Scripting Excel

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. The website has recently dropped off the face of the earth. The following "Scripting Excel" article was salvaged from the Google cache and is provided here in its entirety.

---

Although the Pickaxe book has some examples of scripting Excel, I still had quite a lot of digging to do for some of the scripts I needed. I got some by looking at the M$ docs (poor as they are**), and some by looking at Perl examples. So this page is for collecting examples of code that others might adapt (and add to).

See also: ScriptingOutlook, ScriptingAccess

o You can download the Office XP Excel Object Reference help file as part of an expanded help bundle here [1]. It's large [19MB], but it includes a bunch of VBA help files that I couldn't track down otherwise. The file you need for Excel items is VBAXL10.CHM. It appears to have complete docs on all the Excel objects. -- ChrisMorris

o A great resource for general Excel and programming related questions (well with VBA) are Chip Pearsons [Excel Pages]. I learned a lot from his article [Cell References In A Range] for example. -- BernhardLeicher

First of all:

require 'win32ole'

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new('excel.Application')
workbook = excel.Workbooks.Open('c:\examples\spreadsheet.xls')
worksheet = workbook.Worksheets(1) #get hold of the first worksheet
worksheet.Select  #bring it to the front -need sometimes to run macros, 
#    not for working with a worksheet from ruby
excel['Visible'] = true #make visible, set to false to make invisible 
#    again. Don't need it to be visible for script to work

reading data from spreadsheet

worksheet.Range('a12')['Value']  #get value of single cell
data = worksheet.Range('a1:c12')['Value'] #read into 2D array

finding the first empty row (using empty column A)

line = '1'
while worksheet.Range("a#{line}")['Value']
   line.succ!
end #line now holds row number of first empty row

or to read as you go

line = '1'
data = []
while worksheet.Range("a#{line}")['Value']
   data << worksheet.Range("a#{line}:d#{line}")['Value']
   line.succ!
end

writing data into spreadsheet, example

worksheet.Range('e2')['Value'] = Time.now.strftime 
    '%d/%m/%Y' #single value
worksheet.Range('a5:c5')['Value'] = ['Test', '25', 'result']

loading all Excel constants into a class

class ExcelConst
end
WIN32OLE.const_load(excel, ExcelConst)

Now the constant xlDown is accessible as

ExcelConst::XlDown

To find out what constants to use you can use this script. You run it by passing in a string which is matched against the constant names.

require 'win32ole'

module ExcelConsts
end

excel = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(excel, ExcelConsts)
excel.quit()

puts 'Matches for: ' + ARGV[0]
ExcelConsts.constants.each {|const|
    match = const.match(/#{ARGV[0]}/)
    value = eval("ExcelConsts::#{const}")
    puts ' '*4 + const + ' => ' + value.to_s unless match.nil?
}

An example would be looking for the constant to center text. I ran

ruby search_excel_consts.rb Center 

and the following results came up:

XlCenterAcrossSelection => 7
XlVAlignCenter => -4108
XlCenter => -4108
XlLabelPositionCenter => -4108
XlPhoneticAlignCenter => 2
XlHAlignCetner => -4108
XlHAlignCenterAcrossSelection => 7

calling macros

excel.Run('SortByNumber')

Setting background colour

worksheet.Range('a3:f5').Interior['ColorIndex'] = 36 #pale yellow
# Set background color back to uncoloured (rnicz)
worksheet.Range('a3:f5').Interior['ColorIndex'] = -4142 # XlColorIndexNone constant
# or use Excel constant to set background color back to uncoloured
worksheet.Range('a3:f5').Interior['ColorIndex'] = ExcelConst::XlColorIndexNone

Adding Formulae

emptyRow = 15
worksheet.Range("t#{emptyRow}")['Formula'] = "=(Q#{emptyRow}+L#{emptyRow}+I#{emptyRow}+S#{emptyRow})"

saving changes

workbook.Close(1)
# or
workbook.SaveAs 'myfile.xls'
# default path is the system defined My Documents folder

ending session

excel.Quit

If you're experimenting from within irb and are having problems with processes hanging around after you've called excel.Quit - try deleting the reference to excel and invoking the garbage collector.

excel.Quit
excel = nil
GC.start

Hopefully this is of some use. Please add anything else you have discovered.

Some further stuff that I learned so far...
It partly overlaps with what ChrisMorris already wrote, maybe we can merge it later on. -- BernhardLeicher

Start Excel, create new workbook and save it:

require 'win32ole'
excel = WIN32OLE.new("excel.application")
excel.visible = true     # in case you want to see what happens 
workbook = excel.workbooks.add
workbook.saveas('c:\examples\spreadsheet1.xls')
workbook.close

Or, suppose Excel is already started and a few Excel files are opened (=workbooks in Excel jargon): Connect to the running instance of Excel, activate one of the workbooks and write something.

This also shows that Excel collections can be iterated very handy with "each", and that collections can sometimes be indexed by number or by name:

excel = WIN32OLE.connect("excel.application")
excel.workbooks.each{|wb|puts wb.name}  # loop through workbooks and display names
excel.workbooks(1).activate             # activate by number
excel.workbooks("Mappe1").activate      # or by name     
excel.range("b5").value="soso"          # write something to cell B5

Connecting to Excel is particularly good fun when done interactively from irb: You instantly see what happens!

irb(main):001:0> require 'win32ole'
true
irb(main):002:0> excel=WIN32OLE.connect('excel.application')
#
irb(main):003:0> excel.workbooks.each{|wb|puts wb.name}
PERSONL.XLS
Mappe1
Mappe2
Mappe3
nil
irb(main):004:0> excel.workbooks(1).name
"PERSONL.XLS"
irb(main):005:0> excel.workbooks("Mappe1").activate
true
irb(main):006:0> excel.range("b5").value="soso"
nil

Excel => workbook => worksheet => range(cell)

What always bugged me when browsing through examples were the various ways of referring to a particular cell on a particular worksheet in a particular workbook. When Excel is started and "Mappe1" is the currently active workbook and "Tabelle1" is the currently active worksheet, all following statements do the same thing:

excel.workbooks("Mappe1").worksheets("Tabelle1").range("a1").value
excel.worksheets("Tabelle1").range("a1").value
excel.activeworkbook.activesheet.range("a1").value
excel.activesheet.range("a1").value
excel.range("a1").value

My confusion was probably caused by the fact that a lot of properties/methods can be called on "excel" directly and then default to the currently active workbook/worksheet. It's more a matter of taste to specify "activesheet" and "activeworkbook" or not.

And regarding the hierarchy, it seems to be as simple as: When Excel is up and running, it contains 0 (no file opened) or more workbooks (Workbook? Just Excel jargon for an Excel file!), with each workbook/file containing 1 or more worksheets.

Various methods for addressing a cell or range of cells

In the Excel object model there isn't something like a cell object, it's all covered by the "Range" object. A range can represent only one cell or a whole bunch of them (a column, a row, a rectangular block of cells, ....).

Let's assume for the following examples, that "sheet" contains a reference to an Excel worksheet, obtained e.g. by:

require 'win32ole'
excel = WIN32OLE.connect('excel.application')   # connect to running instance of Excel
sheet = excel.activesheet

sheet.range(cellname/cell[, cellname/cell])

A range can be obtained by using the worksheet's Range property. A range with only one cell:

sheet.range("a1")

Or a rectangular block of cells (A1 to C3):

sheet.range("a1", "c3")

Same with one argument:

sheet.range("a1:c3")

Whole Column A:

sheet.range("a:a")

Whole Row 3:

sheet.range("3:3")

A range itself has a range property, thus allowing to write:

sheet.range("c3").range("a1").address     # >> "$C$3"

Doesn't make much sense, one might note that the second range's address becomes relative to the first range.

sheet.cells(rowindex, columnindex)

This is all wonderful, but shouldn't there be a way of addressing cells by row and column number? The worksheet's Cells property does that: It gets you a range with one cell by specifying the row and column number. The indices are counted from 1, so Cells(1,1) gives you cell A1:

sheet.cells(3,1).address                  # >> "$C$3"

Combined with range to get range A1:C3:

sheet.range(sheet.cells(1,1), sheet.cells(3,3))   

And when applied to another range, row and column index become relative to the first range:

sheet.range("b2").cells(2,2).address      # >> "$C$3"

The index can be negative:

sheet.range("c3").cells(-1,-1).address    # >> "$A$1"
range.offset(rowoffset, columnoffset)

If you have a range, this can be used to return another range that is y rows and x columns away (or offset) from this one. This time the offsets count from 0:

sheet.range("a1").offset(0,0).address      # >> "$A$1"
sheet.range("b5").offset(1,1).address      # >> "$C$6"

While offset somehow reminds of the cells function, this might make a difference: If range contains a block of cells, an offset block of cells is returned too:

sheet.range("a1:c3").offset(0,2).address   # >> "$C$1:$E$3"

Negative offsets can be specified too:

sheet.range("b2").offset(-1,-1).address    # >> "$A$1"

Getting cell values

There isn't only one method for obtaining a cell's value, but at least three of them: text, value, value2. So which should one use, what is the difference between them?

Sidenote:
An Excel cell's content is somewhat relative, what you see isn't necessarily what is actually inside the cell, because a cell's content is displayed according to a specified format. A cell might contain "0.12345", but is displayed as "0.12" or "0.12 DM" or whatever. It might be good to know, that internally a cell's content is either a text or a floating point number. That's it, nothing else.

Just for curiosity:
Dates are represented internally as floating point values too (more details at Chip Pearson's site: HTTP://www.cpearson.com/excel/datetime.htm):³59³ "Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time."
So if the content is 37936.0 and its format is "date", it's displayed as "11.11.03" or "Nov 2003".

For the following examples let's assume a content of:

A1 => 10.12345 => formatted with 2 decimal digits => 10.12
B1 => 10.12345 => formatted as currency => 10,00 DM
C1 => 11.11.03 => date => 11.11.03 

range.text

Text property retrieves the value as it is displayed, as a string. It's readonly, so only for getting values. Because my country settings are "German", floats are displayed with a comma.

sheet.range("a1").text      # >> "10,12"
sheet.range("b1").text      # >> "10,12 DM"
sheet.range("c1").text      # >> "11.11.03"

range.value

This is the result when retrieved with value:

sheet.range("a1").value       # >> 10.12345
sheet.range("b1").value       # >> "10,1235"
sheet.range("c1").value       # >> "2003/11/11 00:00:00"

Retrieves the "internal" value (A1 becomes a float), whereby currency and date are still returned as strings albeit somewhat different than before.

range.value2

According to the Excel documentation value2 behaves just like value, but additionally retrieves currency and dates as doubles (the actual internal content):

sheet.range("a1").value2        # >> 10.12345
sheet.range("b1").value2        # >> 10.12345
sheet.range("c1").value2        # >> 37936.0 

Yes, seems to work as advertised.

Setting values

Seems that only "value" is useful here. An integer or float arrives as expected as number in Excel:

sheet.range("a1").value = 1.2345
sheet.range("a1").value = 2

For strings Excel does the same processing that it does when something is interactively entered. It thus depends on how Excel interprets the string:

sheet.range("a1").value = "10.11.2003"     # becomes a date
sheet.range("a1").value = "1,2345"           # becomes a number (at least with German country settings)

Iterating ranges...

...with each

Ranges can be iterated with each:

sheet.range("a1:a10").each{|cell|puts cell.value}

If the range is a block of cells the iteration goes from left to right, then down one line, and so on:

sheet.range("a1:b5").each{|cell|puts cell.value}

Iterating block of cells by row and output the first cell of each row:

sheet.range("b3:c7").rows.each{|r|puts r.cells(1,1).value}

and by column:

sheet.range("b3:c7").columns.each{|c|puts c.cells(1,1).value}

Result of row.value is an array within an array:

sheet.range("b3:c7").rows.each{|r|puts r.value.inspect}  # >> [1.0, 10.0]?

...with activecell

Like moving around an Excel sheet with the cursor. Moving down one cell:

sheet.activecell.offset(1,0).activate

Walking down from the active cell until an empty cell is encountered:

sheet.activecell.offset(1,0).activate while excel.activecell.value

...with an index

range = sheet.range("b3:c7")
noofrows = range.rows.count
(1..noofrows).each{|i|puts range.cells(i,1).value}

Named Ranges

Named ranges make Excel spreadsheets more usable for the end user. To create a named range "myRange":

sheet.names.Add( { 'Name' => 'myRange', 'RefersTo' => 'A2:A216' } )

One problem! This doesn't work. Use a Range object for RefersTo?, not a String:

myRange = sheet.Range( 'A2:A216' )
sheet.names.Add( { 'Name' => 'myRange', 'RefersTo' => myRange } )

How do you use named ranges in ruby? Named ranges are kept in the worksheet as well as the workbook. You may need to check both locations.

Something like the following works for named ranges manually defined by the user:

rangeString = workbook.names( 'Sheet1!myRange' ).Value

1. Remove "=" prefix (e.g. "=Sheet1!$A$2:$A$4")

rangeString = rangeString.slice( 1, rangeString.length - 1 ) if ( rangeString =~ /^=/ ) worksheet.range( rangeString ).value = 'testing...'

Finding Data Regions...

Data don't always start in row 1, column A, the number of columns might not be fixed, and the number of rows is most often variable. There are two handy methods that can help to find that "block of data".

Let's assume, that B3:C7 contain data.

...with CurrentRegion

Given any cell inside the "data block", CurrentRegion?() finds the surrounding contiguous data region and returns its range:

sheet.range("b5").currentregion.address   # >> "$B$3:$C$7"

...by "jumping around"

There's a shortcut key in Excel "+", that allows you to jump to the end/start of regions with content. With our example and the cursor in B3, pressing + would jump the cursor to cell B7, pressing that shortcut again would move the cursor to the last line of the spreadsheet to B65536. There's an equivalent method: "End()".

Finding the last row with data:

sheet.range("b3").end(-4121).address   # >> "$B$7"

The parameter indicates the direction, the Excel constants are:

xlDown    = -4121
xlToLeft  = -4159
xlToRight = -4161
xlUp      = -4162

Saving to CSV (or other formats)

Note that Excel can show quite a lot of warnings / confirm request. To supress these:

excel.DisplayAlerts = false

Then:

workbook.SaveAs 'myfile.csv', xlCSV

where xlCSV = 6.

Here are some common file formats:

xlCSV=6
xlCSVMac=22
xlCSVMSDOS=24
xlCSVWindows=23
xlCurrentPlatformText=-4158
xlExcel9795=43
xlTextMSDOS=21
xlTextPrinter=36
xlTextWindows=20

See also: ScriptingOutlook, ScriptingAccess

- How about OpenOffice and Ruby scripting? Anything in this area? The examples all seem to rely on

require 'win32ole'

and I believe this will only work on Windows OSes.

---

RubyGarden Archives: Scripting Access

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. That website has recently disappeared. The following "Scripting Access" article was salvaged from the Google cache and is provided here in its entirety.

---

Here is a quick example on getting data from a Microsoft Access database.

Recordset with a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

conn = WIN32OLE.new("ADODB.Connection")
conn["Provider"] = "Microsoft.Jet.OLEDB.4.0"
conn.Open('c:\ruby\dev\calldata.mdb')

rs = conn.Execute("select date, dialednumber, extension, cost from
    callrecords where call = 2 and date >=#01-jan-2005#")
rs.getrows.each do |row|
    puts row
end

conn.Close

Recordset without a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")

qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\data\calldata.mdb'

rs.open(qry,constr)

rs.getrows.each do |row|
    puts row
end

Recordset without connection using an ODBC source. My program took about 28MB mem vs. 39MB for the JET version. The difference is massive when working with a lot of data Requires that you create the ODBC data source!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")
qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
rs.open(qry,"DSN=calldata;")

rs.getrows.each do |row|
    puts row
end

Here's a function you can drop at the top of your program that alters the definition of Object. It will work for loading access data into any object class that supports "<<" and "transpose" - such as Array. Transpose is required since the recordset comes back with the rows "rotated".

public
def readMDB(qry,dsn)
    rs = WIN32OLE.new("ADODB.recordset")
    rs.open(qry,"DSN=#{dsn};")
    rs.getrows.each do |row|
        self << row
    end
    self.transpose
end

Usage example

qry = "select date, dialednumber, extension, cost from callrecords"
dsn = "DSN=calldata;"

a = []
a.readMDB(qry, dsn)
p a

Question: Is there a way to use paramters on the query like the ones from DBI? E.g.: (note the '?')

select * from callrecords where call = ?

---

RubyGarden Archives: Scripting Outlook

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. The website has recently dropped off the face of the earth. The following "Scripting Outlook" article was salvaged from the Google cache and is provided here in its entirety.

---

With some help of the the ScriptingExcel page and the Office helpfiles mentioned there a short script to list your Outlook messages. The script should be pretty easy to expand. One method that was really helpfull in figuring out what is possible is:

OLEObject.ole_methods

MarkJanssen

require 'win32ole'

myApp = WIN32OLE::new("outlook.Application")

# load Outlook OLE constants

class OutlookConst
end

WIN32OLE.const_load(myApp, OutlookConst)

p "OlMailItem = #{OutlookConst::OlMailItem}"

ns = myApp.GetNameSpace("MAPI")
#ns.Logon # uncomment for online usage
folders = ns.Folders

new_messages = 0

folders.each { 
    | folder |
     puts "+" + folder.Name

    begin
        folder.Folders.each {
            | folder |
            # puts " " + folder.Name
            if ["Inbox","Sent Items"].member? folder.Name
                folder.Items.each { 
                    | msg |
                    if msg['UnRead']
                        new_messages += 1
                    end
                    puts "  From: " + msg['SenderName']
                    puts "  Subject: " + msg['Subject']
                }
            end
        }
    rescue
        
         puts " Unable to open"
    end
}
puts "You have #{new_messages} new message(s)"

Glauber 2003-10-09:

* Here's a quick tip that would have saved me some time: When iterating over a list of Outlook items (e.g.: MailItems) in order to move or delete some of them, you must do so backwards (GetLast... GetPrevious... GetPrevious...). In other words, don't attempt to access an item that's after the one you deleted or moved.
* Here's another tip: there's no good way to find the From Internet email address of a message. Most times, creating a reply and extracting the first recipient for it will work, but sometimes not (it may give you an Exchange X.400 address instead of what you're looking for). Don't send the reply, discard it.

---

IronRuby, Ruby.NET, and the Differences

Following on the heels of Pat Eyler's interview of M. David Peterson, Mr. Peterson has posted an article entitled Ruby.NET vs. IronRuby: What's The Difference?.

Microsoft's John Lam has posted a follow-up on his blog.

It's good to see increased public discussion of these two important projects.

Parsing Spreadsheets with the Roo Gem

I've talked at length about using the win32ole library to automate Microsoft Excel. But there are alternatives for accessing data in Excel spreadsheets -- some of which don't even require Excel to be installed. One of these is the roo gem, which allows you to extract data from Excel, OpenOffice and Google spreadsheets. Roo provides read-only access to Excel and OpenOffice spreadsheets, but both read and write access to Google spreadsheets.

To install the roo gem, including its dependencies, open a console window and enter:

gem install roo -y

Require the roo library in your script:

require 'roo'

To parse an Excel worksheet, we first create an instance of the Excel workbook object by calling the Excel.new method and passing it the path and filename:

xl = Excel.new('C:\my_workbook.xls')

The next step is to define which worksheet in the workbook we will be working with. We do this by setting the Excel.default_sheet value to one of the worksheets in the Excel.sheets array:

xl.default_sheet = xl.sheets[0]

To extract the value from a particular cell, call the cell method, passing it the row number and either the column number or column letter. Examples:

val = xl.cell(3, 5)
val = xl.cell(3, 'E')

The row method returns an array of values from the specified row number, so...

values = xl.row(3)

...returns the values from the third row.

Similarly, the column method returns an array of values from a column:

values = xl.column(5)

Get the full details from the roo homepage, and the roo Rdoc page.

That's all for now. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Ruby.NET/IronRuby Interview With M. David Peterson

Over on the On Ruby blog, Pat Eyler interviews M. David Peterson about the Ruby.NET project. Peterson comments on the relationships between Ruby.NET, IronRuby, Microsoft, and the larger Ruby community. Check it out.

Automating Outlook with Ruby: Saving File Attachments

A while back, we looked at accessing the Outlook Inbox and managing messages. A reader recently asked how to iterate over the messages and save file attachments to their hard drive. Specifically, they wanted to save only those file attachments that exceeded 500k in size.

In Outlook, a message (MailItem) object's file attachments are accessed via the Attachments collection:

message.Attachments.each do |attachment|
    # Do something here
end

The Attachment object has few properties and methods, but one you will use is the FileName property, which returns the, uh, filename.

To save the attachment, call its SaveAsFile method, passing it a path and filename:

attachment.SaveAsFile("c:\\attachments\\#{attachment.FileName}")

The attachment object does not offer a method/property for determining the file size. If you were looking to save only files of a certain size, you could save the file, then check the saved file's size and delete it if necessary. This is not optimal, but it does the trick.

So, putting this all together, we'd have something like the following code which iterates over your Inbox messages and saves all attachments of 500,000 bytes or larger:

require 'win32ole'

outlook = WIN32OLE.new('Outlook.Application')
mapi = outlook.GetNameSpace('MAPI')
inbox = mapi.GetDefaultFolder(6)
inbox.Items.each do |message|
    message.Attachments.each do |attachment|
        filename = "c:\\attachments\\#{attachment.FileName}"
        attachment.SaveAsFile(filename)           
        File.delete(filename) if File.size(filename) < 500000
    end
end

That's our show for today. Questions? Comments? Suggestions? Post a comment here or send me an email.

Thanks for stopping by!

The Ruby.NET Compiler: A Little Love

Amidst all the IronRuby hype, you could be forgiven if you didn't hear mention of the Ruby.NET Compiler. But if you use Ruby on Windows -- and you probably do if you're reading this blog -- you owe it to yourself to at least be aware of the work that Wayne Kelly and friends have been doing.

To quote the Ruby.NET page:

Ruby.NET is a compiler that translates Ruby source code into .NET intermediate code. This allows components implemented using the Ruby language to:

* Natively execute on the .NET platform
* Be linked with third party components developed using other .NET languages such as C#.
* Utilize the extensive resources of the .NET platform including visual design and debugging tools, the security framework and an extensive collection of class libraries used, for example, to create Windows forms, web and database applications.

Ruby.NET version 0.9 was released a couple weeks ago and can be installed via a binary installer from here. It requires the Microsoft .Net Framework version 2.0.

There's an optional Visual Studio integration package, which requires Visual Studio 2005 SDK version 4.0.

I've just started tinkering with this, so I can't provide any meaningful analysis or instruction on it yet. But I like the possibilities that it presents...

* Compile your code to a .Net executable
* Use the .NET framework's Forms library for your GUI
* Leverage the vast array of .NET framework libraries
* Work is currently underway to port the win32ole library to Ruby.NET

I'd like to see more documentation and examples, including Windows forms code samples. If you have Ruby.NET examples you would like to share, feel free to email them to me or post in the comments.

There's also a Ruby.NET Compiler Discussion group here.

So check it out and maybe, as Antonio Cangiano recommends, give Ruby.NET some love. I think this is a project that deserves much more attention.