Sunday, November 30, 2008

Automating Word with Ruby: Adding Bookmarks

Someone recently asked a question about using Ruby to add bookmarks to a Microsoft Word document. Here's a brief, but hopefully helpful, explanation...

Word's Document object includes a Bookmarks collection. To get a reference to this collection, call the Bookmarks() method on the Document object:


require 'win32ole'
word = WIN32OLE.connect('Word.Application')
doc = word.ActiveDocument
bookmarks = doc.Bookmarks()

To create a new bookmark, call the Add() method on the Bookmarks collection, passing it (1) a one-word name for the new bookmark, and (2) the range to be bookmarked. The following line of code adds a new bookmark, cleverly named 'Bookmark1', for the currently selected text:

bookmarks.Add('Bookmark1', word.Selection)

The Bookmarks collection includes (among others) a Count() method for getting the number of items in the collection, and an Exists() method for determining if a bookmark with that name already exists. So the following code prints the number of bookmarks, then adds a bookmark if it doesn't already exist, then prints the number of bookmarks again:

puts(doc.Bookmarks.Count)
if not doc.Bookmarks.Exists('Bookmark1')
doc.Bookmarks.Add('Bookmark1', word.Selection)
end
puts(doc.Bookmarks.Count)

To get a reference to an individual Bookmark, call the Bookmarks() method and pass it the name of the bookmark:

bookmark1 = doc.Bookmarks('Bookmark1')

To delete an individual Bookmark, call its Delete() method:

doc.Bookmarks('Bookmark1').Delete

There you have it. Let me know if you have questions or comments. And, of course, there'll be more on this topic (and many others) in the book I'm currently working on.

Thanks for stopping by!

Saturday, September 27, 2008

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!

Sunday, August 31, 2008

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!

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!

Monday, June 30, 2008

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.

Sunday, June 22, 2008

Automating Excel: Creating Charts

They say a picture is worth a thousand words, and sometimes a chart can make your point more effectively than the raw data behind it can. This is especially true when comparing relative values such as monthly revenue data or team statistics.

In our last episode, we looked at how to define titles in existing Excel charts. Sticking with the Excel Charts theme, let's now investigate how to create new charts in Excel.

A chart is a visualization of data, and for this example, the data represents Runs Scored and Runs Allowed for American League Baseball teams, as reported on the mlb.com website. Our Excel worksheet contains a row for each team, with columns for Runs Scored and Runs Allowed:



We'll use the win32ole library for automating Excel, and we'll connect to a running instance of Excel and use the already open 'mlb_stats.xls' workbook:


require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.Workbooks('mlb_stats.xls')

Let's define some parameter variables that we'll use later:

xlColumns = 2
xlColumnClustered = 51
xlWhite = 2
xlRed = 3
xlBlue = 5
xlGray = 15

To add a new Chart object to the workbook's Charts collection, call the Charts.Add method:

mychart = wb.Charts.Add
mychart.Name = "MLB Scoring"

The Charts.Add method returns a reference to the newly-created Chart object, which we've assigned to the variable mychart.

To delete an existing Chart object, call the Charts(chart).Delete method, where chart is the name or (1-based) index of the chart to delete:

wb.Charts("MLB Scoring").Delete
wb.Charts(1).Delete

Naturally, we can't produce a chart without data, and we use the SetSourceData method to define the source of the data that the chart will represent. This method takes 2 arguments, [1] a range of cells from a worksheet, and [2] a number that indicates whether to plot the graph by rows (1) or by columns (2):

mychart.SetSourceData wb.Worksheets("Runs Scored and Allowed").Range("A1:C15"), xlColumns

There are many different types of charts to choose from, and for our purpose, we'll use a 2-dimensional column [aka vertical bars] chart, setting the ChartType property to 51 (via our previously-defined xlColumnClustered variable):

mychart.ChartType = xlColumnClustered

Now, you could execute your code at this point and create a new chart. But let's tweak the colors a bit. The SeriesCollection object holds each series of data and its related properties. Our chart has 2 series, one for Runs Scored, and one for Runs Allowed. Let's make the Runs Scored columns blue and the Runs Allowed columns red, via the Interior.ColorIndex property:

mychart.SeriesCollection(1).Interior.ColorIndex = xlBlue
mychart.SeriesCollection(2).Interior.ColorIndex = xlRed

We could also dictate the name that is displayed for each series in the legend:

mychart.SeriesCollection(1).Name = "Runs Scored"
mychart.SeriesCollection(2).Name = "Runs Allowed "

If you do not define a name for each series, Excel will try to pull it from your source data worksheet.

The PlotArea represents the area on which the data (columns, in our case) is plotted. The ChartArea is the area surrounding the PlotArea, on which the title, axes, and legend are placed. For demonstration purposes, let's define specific colors for these objects by setting their Interior.ColorIndex property:

mychart.ChartArea.Interior.ColorIndex = xlWhite
mychart.ChartArea.Border.ColorIndex = xlBlue
mychart.PlotArea.Interior.ColorIndex = xlGray
mychart.PlotArea.Border.ColorIndex = xlWhite

Finally, we'll add a title to the top of the chart, and format the text:

mychart.HasTitle = true
mychart.ChartTitle.Characters.Text = "American League - Runs Scored vs. Runs Allowed"
mychart.ChartTitle.Font.Name = 'Verdana'
mychart.ChartTitle.Font.Size = 16
mychart.ChartTitle.Font.Bold = true

Note that we first have to set the HasTitle property to true. Without first doing this, trying to define the various ChartTitle properties will raise an error.

Our complete code looks like this, and produces a chart that looks like this:



Not a bad start, eh? A quick glance at this chart tells you that Boston and Chicago are doing very well, run-wise, and that it could be a very long season for Kansas City and Seattle. And we see that Texas scores a tremendous amount of runs, but allows even more.

I encourage you to investigate the vast array of chart methods and properties. Towards that end, you might want to check out Jon Peltier's Chart tutorials, which I have found to be helpful.

Questions? Comments? Suggestions? Post a comment here or send me an email.

Thanks for stopping by!

Sunday, June 15, 2008

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!

Tuesday, June 3, 2008

Running In A Console Window?

Someone recently asked me how to determine if your code is running in a console window or not. Perhaps you have a program that provides both GUI and command line interfaces and you need to know which interface is being used. You may wish to output error messages to the console, if it exists, but to a file if the console doesn't exist.

UPDATE: Perhaps the preferred means for this is the STDIN.isatty method, which returns true if running in a console window and false otherwise...


if STDIN.isatty
# do some command-line stuff
else
# do some GUI stuff
end

Thanks to Brent and Dan for their comments.

Alternatively, Nobu Nakada mentioned in a recent Ruby Forum post that you cannot open the CONIN$ (console input) device file unless you are running in a console window. So the following method (adapted from Nobu) would return true if running in a console window and false otherwise...

def in_console?
begin
open("CONIN$") {}
console = true
rescue SystemCallError
console = false
end
return console
end

Usage example:

if in_console?
# do some command-line stuff
else
# do some GUI stuff
end

There you have it. Got a question or suggestion? Post a comment or send me email.

Thanks for stopping by!

Monday, April 21, 2008

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!

Wednesday, April 2, 2008

Win32OLE Library for JRuby?

Charles O. Nutter of the JRuby project is seeking proposals for Google's Summer of Code (GSoC).

Among the suggested projects is this one of possible interest to many [J]Ruby on Windows users:

Win32OLE Library for JRuby - Implement the win32ole library for JRuby. This would also be an excellent contribution, since there's already libraries like Jacob to take some of the pain out of it, and it would be great to have it working on JRuby. (suggested by sgwong)

Update from Charlie:

BTW, if you know anyone that might be interested in doing this through GSoC, please spread the word quickly. The deadline for submissions is Monday, April 7, so there's not a lot of time left.

And to make it more attractive, there's already a very nice Java library that provides COM/OLE support (http://danadler.com/jacob/) so the task is more about wiring that library into JRuby than mucking about with any low-level native nasties.

I'd love to see this project undertaken, as it could be a big boost for those of us who currently do COM automation work with Ruby. I haven't done much with JRuby yet (a wee bit of Jython in the past), but I like the possibilities for having an easily-distributable app (JAR file?) that includes a GUI (e.g., Swing) and Win32OLE functionality.

What do you think? Would a Win32OLE library increase the likelihood of you using JRuby for some future projects?


Digg my article

Saturday, March 29, 2008

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.


Digg my article

Sunday, January 27, 2008

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.


Sunday, January 20, 2008

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 = ?



Sunday, January 13, 2008

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.


Thursday, January 3, 2008

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.

Digg my article

Wednesday, January 2, 2008

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!

Digg my article