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!

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: 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!

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!