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.

Find & Replace with MS Word

While browsing through some DZone Ruby Snippets, I came across this nice little example of using Ruby to automate Find & Replace in Microsoft Word, compliments of Tim Morgan.

You could use such a snippet as part of a mail-merge type solution. To use it, create a form letter in a Word document, with bracketed placeholders where the actual values are to be inserted. Example:

Dear [name],

On [date] account [account_number] was charged a [amount] fee.

Let's take a walk through the code...

Load the win32ole library:

require 'win32ole'

Launch Microsoft Word:

word = WIN32OLE.new('Word.Application')

Open the template document:

doc = word.Documents.Open('c:\file_to_open.doc')

Iterate over a hash containing keys and values. Each key is the placeholder code and the corresponding value is the text to insert:

{
  'name' => 'Tim Morgan',
  'date' => Date.today.strftime('%B %d, %Y'),
  'account_number' => '123456',
  'amount' => '$45.76'
}.each do |key, value|

Start the search at the beginning of the document:

word.Selection.HomeKey(unit=6)

Grab a reference to the Find object:

find = word.Selection.Find

Set the Find object's text string (the hash key) to locate, surrounded by brackets:

find.Text = "[#{key}]"

For each occurrence of the string found (via the Find object's Execute method), insert the replacement text (the hash value):

while word.Selection.Find.Execute
  word.Selection.TypeText(text=value)
end

Save the document with a new name:

doc.SaveAs('c:\output_file.doc')

Close the document:

doc.Close

The example above shows the hash hard-coded in the script, but you could build on this to load an array of hashes from a text or YAML file, Excel worksheet, or database table.

My thanks to Tim Morgan, who posted the original snippet on Dzone, and to you, for stopping by to read it!

Getting Started with the wxRuby GUI Toolkit

Many readers have asked "What is the best GUI toolkit?". There is no single answer to this question, and I won't try to lobby for my GUI of choice. Use whatever works best for you and your users.

I use the wxRuby library. It's cross-platform, provides a native look-and-feel, and is easy to install (via a gem) and distribute with your application (via RubyScript2Exe). Further features can be found in the wxRuby wiki here. wxRuby is the Ruby interface to wxWidgets, a very stable and widely-used widget toolkit.

Installing wxRuby is simple using RubyGems:

gem install wxruby

Though you can use a GUI designer, creating most forms "by hand" is probably simpler than you suspect. A useful wxRuby user interface can be created in less than 50 lines of code, as we shall see...

Let's create a simple form with a label, text box, combo box, and button. We start by requiring the wx library and including the Wx namespace:

require 'wx'
include Wx

We create a new class which inherits from the Wx::Frame class and includes an initialize method:

class MyFrame < Frame
  def initialize()
    super(nil, -1, 'My Frame Title')
  end
end

The Frame class' super constructor method takes the following arguments (all but Parent are optional):

Parent: The window parent. This may be NULL. If it is non-NULL, the frame will always be displayed on top of the parent window on Windows.

ID: The window identifier. It may take a value of -1 to indicate a default value.

Title: The caption to be displayed on the frame’s title bar.

Position: The window position. A value of (-1, -1) indicates a default position, chosen by either the windowing system or Widgets, depending on platform.

Size: The window size. A value of (-1, -1) indicates a default size, chosen by either the windowing system or Widgets, depending on platform.

Style: The window style (ie, if the minimize, maximize, and close boxes appear on the Frame).

Further details on the Frame class can be found in the wxRuby wiki here.

Next, let's add the code to the initialize method to create a panel, which will contain the other controls:

@my_panel = Panel.new(self)

The self passed to the new constructor is a reference to the Frame object, which we are passing as the parent of the Panel object.

Next, we'll create a variety of controls, passing the newly created @my_panel as the parent of each control:

@my_label = StaticText.new(@my_panel, -1, 'My Label Text', 
  DEFAULT_POSITION, DEFAULT_SIZE, ALIGN_CENTER)
@my_textbox = TextCtrl.new(@my_panel, -1, 'Default Textbox Value')
@my_combo = ComboBox.new(@my_panel, -1, 'Default Combo Text', 
  DEFAULT_POSITION, DEFAULT_SIZE, ['Item 1', 'Item 2', 'Item 3'])
@my_button = Button.new(@my_panel, -1, 'My Button Text')

I recommend assigning all your form controls to instance variables. This is the reason for the '@' preceding each control variable name.

We want to bind the button click to a "my_button_click" method which we will add to this class later. We do with the evt_button method:

evt_button(@my_button.get_id()) { |event| my_button_click(event)}

Now we'll proceed to the layout: arranging the controls in the Panel. We'll do this using a BoxSizer, which we'll have arrange the controls vertically:

@my_panel_sizer = BoxSizer.new(VERTICAL)
@my_panel.set_sizer(@my_panel_sizer)

Next we add each control to the panel's sizer by calling the sizer's add method:

@my_panel_sizer.add(@my_label, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_textbox, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_combo, 0, GROW|ALL, 2)
@my_panel_sizer.add(@my_button, 0, GROW|ALL, 2)

See the Sizer.Add documentation for an explanation of the various parameters.

Our last line of code in the initialize method makes the frame visible:

show()

Don't forget to add the my_button_click method that is called by the button click:

def my_button_click(event)
    # Your code here
end

That concludes our MyFrame class. Now we want to create a MyApp class that will call our MyFrame class:

class MyApp < App
  def on_init
    MyFrame.new
  end
end

Finally, we create a new instance of our MyApp class and call its main_loop method:

MyApp.new.main_loop()

View the complete code here.

There you have it. A complete, but simple, wxRuby form. It's not much to look at...



...but it demonstrates the basics and we'll enhance it shortly.

Soon, we'll cover related topics: dressing up our GUI a little, using the wxSugar extensions, using a Forms designer such as wxFormBuilder.

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

Thanks for stopping by!

RubyConf 2007

I'll be attending my first RubyConf here in Charlotte this weekend and there's a lot of great stuff on the agenda. In addition, I learned from Bill Plummer that Microsoft is hosting an event Thursday night with John Lam talking about IronRuby. Unfortunately, I will have to miss it but I hope that John will duplicate the best parts of it in his RubyConf talk Saturday morning. Charlie Nutter follows that with his JRuby presentation. Of course, "Writing Client and Desktop Applications in Ruby" by Bruce Williams also caught my eye, as I am a desktop apps developer.

There's been a bit of a lull in my blog postings (and replies to readers) of late, as I've been swamped with work, but I plan to post more frequently going forward. I have a few topics suggested by readers, but welcome any comments, questions, or suggestions you may have. I still feel that Windows is Ruby's red-headed stepchild, with far more potential than the attention given to it. That may change (somewhat) in the future as projects such as IronRuby and the Ruby.Net compiler mature.

And if you're at RubyConf, stop by and say 'Hey!'

Windows XP Visual Style Controls with wxRuby

wxRuby uses the native Microsoft Windows common controls when displaying widgets on a Windows OS. You may have noticed, however, that the form controls do not have the Windows XP look and feel. They have the flat and 2-dimensional look of earlier Windows versions...



...rather than having the rounded corners and 3-dimensional look of Windows XP...



Note, among other things, the "glowing button" effect when the mouse hovered over the "Get Films Data" button.

This is because Windows XP comes with both version 5 and version 6 of the Common Controls, but unless specifically instructed to use version 6, it will default to using version 5 controls.

So, how do you instruct Windows to use the version 6 controls for your wxRuby GUI application? By including a simple manifest XML file with your application.

Copy the following text into a new text file. Save the file as "rubyw.exe.manifest" in your ruby/bin folder, the same folder that contains rubyw.exe. You might also save a copy to the same folder as "ruby.exe.manifest".

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
    <assemblyIdentity version="1.8.6.0" processorArchitecture="X86" 
        name="Microsoft.Winweb.Ruby" type="win32"/>
        <description>Ruby interpreter</description>
        <dependency>
         <dependentAssembly>
                <assemblyIdentity 
                    type="win32" 
                    name="Microsoft.Windows.Common-Controls" 
                    version="6.0.0.0" 
                    processorArchitecture="X86" 
                    publicKeyToken="6595b64144ccf1df" 
                    language="*"
                />
        </dependentAssembly>
    </dependency>
</assembly>

When the ruby.exe or rubyw.exe interpreter is run, Windows will look for a manifest file matching the program name in the same folder. When it finds our manifest file, it will then use the version 6 controls, as defined in the <dependency> section of the manifest.

We place the file in our c:\ruby\bin directory because that is where ruby.exe and rubyw.exe are located. We name the file ruby.exe.manifest or rubyw.exe.manifest to match the name of the interpreter being run.

If you are compiling your application with RubyScript2Exe, copy the manifest file into the folder with your script, then embed the manifest file in your compiled executable using the RUBYSCRIPT2EXE.bin statement:

RUBYSCRIPT2EXE.bin = ["rubyw.exe.manifest"]

When your compiled executable is run, your manifest file will be extracted to the same temporary "/bin" folder as the rubw.exe (or ruby.exe) interpreter.

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

Thanks for stopping by!

Hide & Seek: Using NTFS Alternate Data Streams

The NTFS file system utilized by Windows (NT and later) includes support for Alternate Data Streams (ADS). ADS was implemented in NTFS to provide compatibility with Apple's Macintosh Hierarchical File System (HFS), which uses resource forks to store icons and other information for a file, but such streams can be used to store any type of data that a normal file would store. You could, for example, use an ADS to store metadata about the file to which the ADS is attached. You could even store binary data inside an ADS. This might be useful for storing backup versions of a file before making changes.

To work with an alternate data stream for a file, simply append a colon and the stream name to the filename. The following code appends a stream named 'stream1' to file.txt:

open('file.txt:stream1', 'w') do |f|
    f.puts('Your Text Here')
end

If file.txt did not exist, it would have been created and would be 0 bytes in size, since no data was written to the main stream. But you can append streams to an existing file, as well. The reported size of the file would not change, though it now includes alternate data streams of embedded data.

Reading from a stream is just as simple:

stream_text = open('file.txt:stream1').read

Alternate data streams can also be used for storing binary data, including executables:

bytes = open('MyApp.exe', 'rb').read
open('file.txt:MyApp.exe', 'wb') do |f|
    f.write(bytes)
end

bytes = open('file.txt:MyApp.exe','rb').read
open('MyApp2.exe', 'wb') do |f|
    f.write(data)
end

A file can contain multiple data streams, so you could, for example, include a data stream for text and another stream for binary data.

Another possible use for ADS would be to create a backup of the file which could be restored later:

def create_backup(filename)
    open("#{filename}:backup", "wb") do |f|
        f.write(open(filename, "rb").read)
    end
end

def restore_backup(filename)
    if not File.exist?("#{filename}:backup")
        puts("Backup stream does not exist!")
        return
    end
    backup = open("#{filename}:backup", "rb").read
    open("#{filename}", "wb") do |f|
        f.write(backup)
    end
end

Note: A file's alternate data streams will be preserved on the NTFS disk, but would be stripped off of a file when copied to a non-NTFS disk, such as a flash drive or CD/DVD disk; or when a file is copied via ftp. For this reason you may not want to rely on alternate data streams for storing critical data.

There you have it. Sound interesting? Further details on Alternate Data Streams can be found here and here.

Thanks for stopping by!

Using Ruby & SQL-DMO to Automate SQL Server Tasks

If you work with Microsoft SQL Server, you might like to know that you can automate many of your administrative tasks by leveraging Distributed Management Objects (SQL-DMO). SQL Server's Enterprise Manager is based on DMO, so most (if not all) of what you do through the Enterprise Manager interface can be automated through SQL-DMO -- and Ruby. Create, manage, and backup databases, tables, views, jobs, stored procedures, and more. Let's take a look at some of the available objects and Ruby code that uses them.

We start by creating an instance of the SQLServer object:

require 'win32ole'
server = WIN32OLE.new('SQLDMO.SQLServer')

To connect to your server using SQL Authentication, call the Connect method providing the server IP address or name, the login, and password:

server.Connect('127.0.0.1', 'login', 'password')

To connect using Windows Authentication, set the LoginSecure property to true, then call the Connect method with just the server IP address or name:

server.LoginSecure = true
server.Connect('127.0.0.1')

When you're done, be sure to close your connection by calling the Disconnect method:

server.Disconnect

The Databases method returns a collection of Database objects. You can access a specific Database object by passing the database name to the Databases method:

database = server.Databases('my database')

The Database object includes dozens of methods and several collections, including Tables, Views, and StoredProcedures. This code, for example, prints the names of all tables:

for table in database.Tables
    puts table.Name
end

Furthermore, many database objects expose a Script method that returns the SQL code for creating that object. This little block of code saves the SQL for creating all your Table objects:

for table in database.Tables
    File.open("#{table.Name}.sql", "w") do |f|
        f.puts table.Script
    end
end

The following block of code writes the text of each stored procedure to a text file:

database.StoredProcedures.each do |sp|
    File.open("#{sp.Name}.txt", "w") do |f|
        f.puts sp.Text
    end
end

The SQL Server Agent service, which runs and manages jobs, is accessed via the JobServer object. The following code starts and stops this service:

server.JobServer.Start
server.JobServer.Stop

The JobServer object's Jobs method returns a collection of all SQLServer Agent jobs:

for job in server.JobServer.Jobs
    puts "Name: #{job.Name}\nDescription: #{job.Description}\n\n"
end

I have a script that iterates over the Jobs collection and creates a "jobs inventory", an Excel workbook listing each job's name, description, frequency (daily, weekly, etc.) and start time.

Further details on SQL-DMO can be found in this MSDN article, and in this SQL Team article.

That's all for today, but if you use SQL Server, perhaps this has given you some ideas on how to use your Ruby skills to make SQL Server admin tasks a little easier.

UPDATE: You may also be interested in this earlier article about using Ruby and ADO for your SQL Server queries.

Feel free to post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Using Rake to Automate Windows Desktop App Builds

We've recently talked about compiling your Ruby app with RubyScript2Exe, and creating an install package with Inno Setup.

I was inspired by RoW reader Luis Lebron, who recently shared with me his Rakefile for automating the running of RubyScript2Exe from within the NetBeans Ruby IDE.

My process for packaging an application for distribution involves, among other things:

• Compiling the code with RubyScript2Exe
  
• Replacing the default EXE icon with my own (using Resource Hacker)
  
• Moving the EXE file to my Install folder
  
• Updating the ReadMe file with the new version number
  
• Updating the Inno Setup script with the new version number
  
• Running the Inno Setup script to create a new Setup.exe file
  

This isn't a particularly time-consuming list of manual tasks. But I spend my workdays developing tools to automate my fellow employees' manual tasks. So it was inevitable that I would seek to do the same for myself.

I had no prior experience with Rake, a DSL created by Jim Weirich to automate project builds. I'm neither a C programmer nor a web developer, so I wasn't really motivated to investigate what Rake might have to offer. But after receiving Luis' email, I looked into using Rake, and then researched the options for running Resource Hacker and the Inno Setup compiler from the command line.

I still know almost nothing about Rake, but I learned enough to create a simple Rakefile to automate all of the above tasks. This could, of course, be done in Ruby without the using Rake, but NetBeans' Rake integration and templates make it handy. Now I can right-click on my project icon in NetBeans, select Run Rake Task => create_setup, and Rake takes care of the rest.

In case you're interested, here's an example of my Rakefile. It can no doubt be improved upon, but should give you an example to start from (Beware of line-wrap):

require 'rake'
require 'fileutils'
include FileUtils

# set constant values:
LIB_FOLDER = File.expand_path('../lib')
INSTALL_FOLDER = File.expand_path('../install')
ISCC = "C:/Program Files/Inno Setup 5/iscc.exe"
RESHACKER = "C:/Program Files/ResHacker/ResHacker.exe"
ISS_FILE = "#{INSTALL_FOLDER}/Setup.iss"
README_FILE = "#{INSTALL_FOLDER}/ReadMe.txt"

# extract values from main.rb file:
main_rb = open('../lib/main.rb').read
APP_TITLE = main_rb.scan(/APP_TITLE = '(.+)'/)[0][0]
EXE_NAME = main_rb.scan(/EXE_NAME = '(.+)'/)[0][0]
EXE_BASENAME = EXE_NAME.gsub('.exe', '')
APP_VERSION = main_rb.scan(/APP_VERSION = '(.+)'/)[0][0]

# rake tasks:
task :default => [:create_setup]

desc "Create setup.exe"
task :create_setup => [:move_exe, :modify_icon, :create_iss_script, 
    :edit_readme] do
    puts "Creating setup.exe"
    Dir.chdir(INSTALL_FOLDER)
    system(ISCC, ISS_FILE)
end

desc "Create ISS script"
task :create_iss_script => [:move_exe] do
    puts "Creating ISS script"
    Dir.chdir(INSTALL_FOLDER)
    data = ISS_TEXT.gsub('[APP_TITLE]', APP_TITLE)
    .gsub('[APP_VERSION]', APP_VERSION)
    .gsub('[EXE_NAME]', EXE_NAME)
    .gsub('[EXE_BASENAME]', EXE_BASENAME)
    File.open(ISS_FILE, 'w') do |f|
        f.puts(data)
    end
end

desc "Edit ReadMe.txt"
task :edit_readme do
    puts "Updating ReadMe.txt file"
    Dir.chdir(INSTALL_FOLDER)
    txt = nil
    open(README_FILE) do |f|
        txt = f.read
    end
    old_version = txt.scan(/Version (\d\d\.\d\d\.\d\d)/)[0][0]
    txt = txt.gsub(old_version, APP_VERSION)
    File.delete(README_FILE)
    open(README_FILE, 'w') do |f|
        f.puts(txt)
    end
end

desc "Modify EXE icon"
task :modify_icon => [:move_exe] do
    puts "Modifying EXE icon"
    Dir.chdir (INSTALL_FOLDER)
    arg = " -addoverwrite #{EXE_NAME}, #{EXE_NAME}, application.ico, 
          icongroup, appicon, 0"
    system(RESHACKER + arg)
end

desc "Move EXE to install folder"
task :move_exe => [:compile_code] do
    puts "Moving EXE to install folder"
    mv("#{LIB_FOLDER}/main.exe", "#{INSTALL_FOLDER}/#{EXE_NAME}")
end

desc "Compile code into EXE"
task :compile_code do
    puts "Compiling main.rb into EXE"
    system("rubyscript2exe.cmd", "#{LIB_FOLDER}/main.rb")
end

# text of Inno Setup script:
ISS_TEXT =<<-END_OF_ISS

[Setup]
AppName=[APP_TITLE]
AppVerName=[APP_TITLE] version [APP_VERSION]
AppPublisher=David L. Mullet
AppPublisherURL=http://davidmulletcom
AppContact= david.mullet@gmail.com
AppVersion=[APP_VERSION]
DefaultDirName=C:\\[APP_TITLE]
DefaultGroupName=[APP_TITLE]
UninstallDisplayIcon={app}\\[EXE_NAME]
Compression=lzma
SolidCompression=yes
OutputDir=.
OutputBaseFilename="[EXE_BASENAME]_Setup"

[Files]
Source: "[EXE_NAME]"; DestDir: "{app}"; Flags: ignoreversion
Source: "Readme.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\\[APP_TITLE]"; Filename: "{app}\\[EXE_NAME]"; 
    WorkingDir: "{app}"
Name: "{group}\\View ReadMe File"; Filename: "{app}\\ReadMe.txt"; 
    WorkingDir: "{app}"

END_OF_ISS

As always, post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Installing Your Ruby App with Inno Setup Installer

In my last article, we looked at 'compiling' your Ruby app into a single portable executable file using RubyScript2Exe.

As a reader commented, "For windows it becomes so important to make things 'one click'. I guess the next step would be to also wrap this in some kind of simple installer script to store it in the right place and add the appropriate menu/desktop icons."

Which brings us to the topic of today's discussion: using Inno Setup to create an Install program (ie, setup.exe) for your Ruby applications.

Inno Setup, created, by Jordan Russell, has been around for about a decade, is very well documented, and frequently updated. It's very customizable and extendable, and allows you to create professional installation packages that will allow you to create directories, install files to multiple locations (install folder, Windows system folder, etc.), create Start menu Program groups and icons, and place shortcut icons on the user's Desktop and Quick Launch bar. And it's free of charge.

Inno Setup creates your Setup.exe file based on parameters that you define in a text-based script. Your ISS script file is divided into sections, with parameter=value pairs, much like a Windows INI file. Starting out, you'll probably use the Inno Setup user interface to create and edit your scripts, and then to compile your installation package into a setup.exe and run it.



Section names may include [Setup], [Files], [Icons], and [Code]. Inno Setup includes a comprehensive help file detailing all of the options, and there is an extensive FAQ document on the website.

A typical ISS script might look something like this:

[Setup]
AppName=My Ruby Application
AppVerName=My Ruby Application version 09.23.07
AppPublisher=David Mullet
AppPublisherURL=http://davidmullet.com
AppContact=david@davidmullet.com
AppVersion=09.23.07
DefaultDirName=C:\My Ruby Application
DefaultGroupName=My Ruby Application
UninstallDisplayIcon={app}\MyRubyApp.exe
Compression=lzma
SolidCompression=yes

[Files]
Source: "MyRubyApp.exe"; DestDir: "{app}"; Flags: ignoreversion
Source: "ReadMe.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\My Ruby Application"; Filename: "{app}\MyRubyApp.exe"; WorkingDir: "{app}"
Name: "{group}\View ReadMe File"; Filename: "{app}\ReadMe.txt"; WorkingDir: "{app}"

Inno Setup is highly customizable. You can, for example, customize the install wizard, or include a [Code] section with Pascal procedures (see the Help file for details and examples). The scripts are straight text, and the compiler can be called from the command line, so you can create and compile your ISS script all within Ruby, if you like, such as in a Rakefile. I'll include just such an example in an upcoming article.

So, you can now create a single executable file for your Ruby application and use a professional-looking installer to distribute it.

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

Thanks for stopping by!

Compiling Your Ruby App with RubyScript2Exe

I've mentioned RubyScript2Exe previously. This tool allows you to 'compile' a script/application into a portable executable file (EXE) that you can easily provide to your users without requiring them to install Ruby and the required libraries. RubyScript2Exe traces and gathers all the necessary files, including the Ruby interpreter, and 'compiles' them into a single EXE file. You can easily embed images and icon files, and DLLs such as SQLite.

I put the word 'compile' in quotes above because RubyScript2Exe does not transform your code as a C compiler or .Net compiler would. Rather, it collects all the files necessary to run your application and bundles them into a single EXE file. When the user runs that EXE file, that bundle is quickly extracted to a temporary file and your Ruby code is executed.

Installing RubyScript2Exe is as easy as falling off a log, thanks to RubyGems. Just get to a command prompt and enter:

gem install rubyscript2exe

Include the following require statement at the top of your script:

require 'rubyscript2exe'

Whenever possible, include all your require statements at the top of your script. This ensures that RubyScript2Exe successfully traces and includes all the necessary files your application will need.

If you are compiling a non-console script and therefore want to use the rubyw.exe interpreter, rather than the ruby.exe interpreter, include the following module variable near the top of your script:

RUBYSCRIPT2EXE.rubyw = true

When I use the NetBeans Ruby IDE, which defaults a new project's main script name to "main.rb", I include the above code to avoid renaming the script with a ".rbw" extension or providing command-line parameters to the RubyScript2Exe compiler. More on that later.

A Ruby Forum reader and RubyScript2Exe user recently mentioned "I want to be able to wrap the icon file along with the rest of the application." You can embed additional files such as icons or DLLs in the executable like this:

RUBYSCRIPT2EXE.bin = ["my_icon.ico", "sqlite3.dll"]

When you run your compiled executable, RubyScript2Exe extracts all the files from your executable into a temporary directory. But sometimes you need to know the location of the folder the executable was originally run from. Just call the RUBYSCRIPT2EXE.exedir method:

APPLICATION_PATH = RUBYSCRIPT2EXE.exedir

Enough preparation! Let's compile our application. Go to a command prompt and enter:

rubyscript2exe my_script.rb

...or...

rubyscript2exe my_script.rbw

If your script has a filename extension of .rb, RubyScript2Exe will include the ruby.exe interpreter and a console window. If your script has a filename extension of .rbw, RubyScript2Exe will include the rubyw.exe interpreter and your app will therefore not have a console window; this is the same as if you had included RUBYSCRIPT2EXE.rubyw = true in your code.

The size of your compiled executable can vary widely depending on what files are needed to be included. A simple console app may be 1mb in size, a wxRuby 0.6 GUI app may be 3-4mb, and a wxRuby 2 GUI app may be 6-8mb in size. Part of this size is due to a known 'bug' that may cause some files (the 8mb wxRuby2.so file, for example) to be included twice. This affects the size of the EXE file but not the performance.

There you have it. But this post just scratches the surface. RubyScript2Exe's creator, Erik Veenstra, has done a great job maintaining and documenting this tool, and you should take a few minutes and read the docs here.

Questions? Comments? Suggestions?

Post a comment here or send me an email.

Thanks for stopping by!

Ruby & Excel: 911 Characters Only, Please

I was writing some code this week to query an SQL Server database and write the results to an Excel workbook.

I was defining a range in an Excel worksheet and inserting data using code something like this:

worksheet.Range("A2").Resize(data.size, data[0].size).Value = data

The above code defines a range starting at cell A2 and extending the width and length of my 2-dimensional array, then inserts my array into that range of cells.

This worked great -- about 95 percent of the time. But occasionally it would bomb. My debugging uncovered the fact that it would always and only bomb if a string exceeding 911 characters was inserted into a cell.

Some quick googling revealed that this is a known Excel 2003 bug, acknowledged by Microsoft. Microsoft's suggested workaround is "don't do that"; don't try to insert more than 911 characters into a cell as part of an array. Gee, thanks.

I was using the method above because it is significantly faster than inserting data one cell at a time. This isn't a big deal for 100 lines x 10 columns of data, but when you're inserting a million cells of data, there's a huge time difference. I could successfully insert the data one cell at a time, but my users might have to take extended coffee breaks.

I could write the data to a tab-delimited, comma-delimited, or XML text file; then open it in Excel and tweak it if necessary. But I hoped to avoid that if possible.

In case you're wondering, I resolved it with a compromise. Rather than insert the data all at once or cell-by-cell, I inserted it row-by-row:

worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row

This will still raise an exception when attempting to insert more than 911 characters into a cell. But we'll handle those exceptions by inserting that row's data cell-by-cell.

The revised code looks something like this:

data.each_with_index do |row, r|
    begin
        # try to insert the entire array into the row
        worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row
    rescue
        # if exception, then insert each cell individually
        row.each_with_index do |field, c|
            worksheet.Cells(r + 2, c + 1).Value = field
        end
    end
end

It's not as fast as an all-at-once insert, but much faster than a cell-by-cell insert.

By the way, the '+1' offset for column number is because Ruby arrays have zero-based indexes, while Excel's column indexes are 1-based. And the '+2' offset for row number is for the same reason, plus 1 to skip the first row of the worksheet, which contains the field names.

So, insert data into worksheets as arrays when you can. But don't let the 911-Characters Bug bite.

NetBeans Ruby IDE

If you're looking for a new Ruby editor, I suggest you check out NetBeans. Tor Norbye and the gang have been doing a great job adapting this Java IDE for use with Ruby and Rails.

I usually use SciTE, the Scintilla text editor, for writing my Ruby code. It's fast, flexible, and lightweight, consuming a fraction of the resources of a full-blown IDE. It's still my editor of choice for small scripts. But I've begun using NetBeans for larger projects and am very pleased so far.

NetBeans is now available in a Ruby-only version which, presumably, is a little slimmer than the full Java + Ruby IDE. And you can trim a little more fat by deactivating a few plug-ins. On my Windows XP systems, memory usage is in the 80-120Mb range. That's acceptable, even on my more memory-challenged machine, when you consider the potential productivity gains offered by the IDE features. And the fact that it's free is certainly a plus.

Further details can be found on the NetBeans Ruby Support page, on the NetBeans Ruby wiki, and in numerous in-depth reviews like this one by Daniel Spiewak.

New builds are posted every few hours, and the latest stable build can always be found here. I usually replace my nbrubyide folder every day or two.

So if you're looking for a full-featured Ruby IDE, take NetBeans for a test drive and see if it meets your needs. It may be a dumb name, but it's a good Ruby IDE.

RubyConf 2007 Comes to Charlotte

Somehow, I had missed the fact that RubyConf 2007 will be held here in Charlotte, North Carolina (USA) November 2-4.

Fortunately, a friend made me aware of this fact in time for me to register to attend.

Lots of good stuff is on the agenda. Here's a couple talks that caught my eye as a Windows desktop developer:

John Lam on The State of IronRuby
Bruce Williams on Writing Client and Desktop Applications in Ruby

Registration is still open, but I hear that seats are going fast.

Automating Outlook with Ruby: Inbox & Messages

In response to my series on Automating Outlook with Ruby, several readers have asked about accessing the Inbox and managing messages.

We start by using the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

To get a reference to the Inbox folder, call the MAPI object's GetDefaultFolder method, passing it the integer 6, which represents the Inbox folder:

inbox = mapi.GetDefaultFolder(6)

To get the Personal Folders object, call the MAPI object's Folders.Item method, passing it the name of the folder.

personal_folders = mapi.Folders.Item('Personal Folders')

To reference a subfolder, call the parent folder object's Folders.Item method, passing it the name of the folder.

baseball_folder = personal_folders.Folders.Item('Baseball')

You can get a count of a folder's unread items by calling the UnreadItemCount method:

puts "#{inbox.UnreadItemCount} unread messages"

A folder object's Items method returns a collection of message objects, which you can iterate over:

inbox.Items.each do |message|
    # Your code here...
end

You can also pass the Items method a (1-based) index to retrieve a single message:

first_message = inbox.Items(1)

Once your code tries to access methods and properties of a Message object, an Outlook security dialog will prompt the user to allow access to Address Book entries. The user must click a checkbox to allow access and select a time limit between 1 and 10 minutes.

Message objects have dozens of methods/properties, including:

SenderEmailAddress
SenderName
To
Cc
Subject
Body

To see a complete list, you could call the ole_methods method, as explained here. So to view a sorted list of the Message object's methods and properties, we could do this:

methods = []
inbox.Items(1).ole_methods.each do |method|
    methods << method.to_s
end
puts methods.uniq.sort

To delete a message, call its Delete method:

message.Delete

To move a message to another folder, call its Move method, passing it the folder object:

baseball_folder = personal_folders.Folders.Item('Baseball')
message.Move(baseball_folder)

So, if we wanted to check our Inbox and move all messages that contain 'Cardinals' in the subject line to our 'Baseball' folder, we could do something like this:

inbox.Items.Count.downto(1) do |i|
    message = inbox.Items(i)
    if message.Subject =~ /cardinals/i
        message.Move(baseball_folder)
    end
end

As reader Ana points out, we should use the Count and downto methods to ensure that our inbox.Items index stays in sync, even as we move messages out of the Inbox. Otherwise, we run the risk of a message being skipped when the message above it is moved or deleted.

That concludes our program for today. Feel free to post a comment here or send me email with questions, comments, or suggestions.

Automating Outlook with Ruby: Address Books

We recently discussed working with Outlook Contacts. On a related note, a reader asked how to access the Outlook Address Books. So let's dive right in...

We start by using the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

The MAPI object's Session.AddressLists method, when called without parameters, returns a collection of the various Address Books available to Outlook, such as "Contacts", "Personal Address Book", and "Global Address List". The following code iterates over this collection and prints the name of each available Address Book:

mapi.Session.AddressLists.each do |list|
    puts list.Name
end

To work with a particular Address Book, call the MAPI object's Session.AddressLists method and pass it the name of the Address Book:

address_list = mapi.Session.AddressLists('Global Address List')

This returns a single AddressList object. Call this object's AddressEntries method to obtain a list of AddressEntry objects:

address_entries = address_list.AddressEntries

Upon executing the above line of code, an Outlook security dialog will prompt the user to allow access to Address Book entries. The user must click a checkbox to allow access and select a time limit between 1 and 10 minutes.

Once access to the Address Book entries has been granted, you can iterate over the AddressEntries collection and get the Name and Address for each entry, or only for entries that meet certain criteria:

address_entries.each do |address|
    if address.Name =~ /Machiavelli/
        name = address.Name
        email_address = address.Address
    end
end

To search for an Address, you could call the AddressEntries collection's Item method. This accepts an integer as a (1-based) index:

address_entry = address_entries.Item(27)

...but also accepts a text string:

address_entry = address_entries.Item("sinatra, f")

This seems to return only the first AddressEntry object that meets the text string criteria. If no AddressEntry meets the text criteria, it seems to return the closest item following. So don't assume that the object returned meets your search criteria -- check the Name and/or Address properties to be certain.

There you have it. As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Windows Developer Power Tools

While taking a brief respite from tutorialism and browsing the stacks at Barnes & Noble recently, I stumbled across Windows Developer Power Tools, an O'Reilly book by James Avery and Jim Holmes that covers over 170 free tools for Windows developers.

I've only scratched the surface thus far, but have already begun using several of the tools mentioned in the book, including SQLite Administrator, SharpDevelop, and Snippet Compiler. Some tools are .NET-specific, but many more are not.

The companion web site is here.

If you work with SQLite, my personal favorite database, you'll probably find SQLite Administrator quite helpful. It provides a GUI front end that allows you to create, design, and manage SQLite database files. Check out the website for full details and screenshots.

Automating Outlook with Ruby: Contacts

We've looked at sending email, working with calendar appointments, and creating and managing tasks with Outlook. Next we'll take a look at how to create new contacts and read contacts data from Outlook.

You know by now how this starts out: we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

Creating a New Contact

To create a new contact, call the Outlook Application object's CreateItem method, passing it the number 2, which represents a Contact Item. This returns a new Contact object:

contact = outlook.CreateItem(2)

Then set values for various properties of the new Contact object:

contact.FullName = 'Stan Musial'
contact.CompanyName = 'St. Louis Cardinals'
contact.JobTitle = 'Hitter'
contact.BusinessTelephoneNumber = '(314)555-1234'
contact.Email1Address  = 'stan_the_man@stlcardinals.com'

What properties are available to set? Dozens. To see a list, you could call contact.ole_methods, Google for it (Outlook Contact Properties), or use an OLE object browser.

When you've completed setting property values, call the Contact object's Save method:

contact.Save

Getting Existing Contacts Data

To obtain a collection of Contacts, call the MAPI object's GetDefaultFolder method, passing it the integer 10, which represents the Contacts folder. Then call the Items method on this object:

contacts = mapi.GetDefaultFolder(10).Items

You can now iterate over this collection of Contact objects, calling the methods/properties to get the data you need:

contacts.each do |contact|
    puts contact.FullName
    puts contact.Email1Address
    puts contact.BusinessTelephoneNumber
end

For further information, check out these Microsoft TechNet articles:

Creating a New Contact in Microsoft Outlook
Exporting Contact Information

As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Automating Windows Media Player with Ruby

My recent article about automating iTunes has resulted in several requests for a similar article about Windows Media Player (WMP). And so, without further ado...

We start by using the win32ole library to connect to the WMPlayer object:

require 'win32ole'
player = WIN32OLE.new('WMPlayer.OCX')

Note that this will not display the WMP user interface. That happens next.

To play a song, call the player object's OpenPlayer method, passing it the path to the song file:

player.OpenPlayer('c:\music\van halen\right now.wma')

The Media Collection

Next, we'll grab the MediaCollection object:

media_collection = player.mediaCollection

To quote Microsoft, "The MediaCollection object represents all the items stored in the Windows Media Player media collection. You will typically query the MediaCollection object to return collections of media items."

The MediaCollection object's getAll method returns a collection of all items in the library:

all_media = media_collection.getAll()

The getByAttribute method can be used to get a collection of all audio files:

audio_media = media_collection.getByAttribute("MediaType", "Audio")

To get a collection of songs by a specific artist, use the getByAuthor method:

sinatra_songs = media_collection.getByAuthor("Frank Sinatra")

You can also get items by Album:

album = media_collection.getByAlbum('Come Fly with Me')

Or by Genre:

jazz_tunes = media_collection.getByGenre('Jazz')

And, of course, you can get a collection of items by name:

songs = media_collection.getByName('Fly Me to the Moon')

Note that this will return a collection of items, even if it contains only one item. You reference a specific item by calling the collection's Item method, passing it a (zero-based) index value. So to get the first item:

first_song = songs.Item(0)

As mentioned earlier, to play a song, call the player object's OpenPlayer method, passing it the song object's sourceURL property. sourceURL is simply the path to the song file:

player.OpenPlayer(first_song.sourceURL)

To add a song to your Media Collection, call the MediaCollection object's Add method, passing it the path and filename:

song = media_collection.Add('C:\music\Just in Time.wma')

To delete a song, from the Media Collection, call the MediaCollection object's Remove method, passing it the song object and the boolean value 'true':

songs = media_collection.getByName('Fly Me to the Moon')
media_collection.Remove(songs.Item(0), true)

You may need to restart the Media Player user interface to see additions and removals.

The PlayLists Collection

PlayLists are managed via the player's PlaylistCollection object:

playlists = player.PlaylistCollection

The PlaylistCollection object has methods similar to the MediaCollection object. Its getAll method returns a collection of all PlayList objects:

all_playlists = playlists.getAll()

Its getByName method returns a collection of PlayList objects by name:

split_enz_playlist = playlists.getByName('Split Enz')

Remember, this returns a collection, even if that collection contains a single item. So to get the first (and possibly only) item, we call the Item method on the returned collection:

split_enz_playlist.Item(0)

A PlayList is a collection of Song items, just like those returned by the MediaCollection object's methods. You can call each item in the collection by index. So to print the name of each song in our playlist:

(0..my_playlist.Count - 1).each do |i|
    song = my_playlist.Item(i)
    puts song.Name
end

To create a new playlist, call the PlaylistCollection object's newPlaylist method, passing it the name of the new playlist:

playlists = player.PlaylistCollection
playlists.newPlaylist('New Playlist')

This creates a .wpl playlist file. You then have to add the new playlist file to the MediaCollection object, by calling the Add method and passing it the path and name of the newly created PlayList file:

media_collection.Add('D:\Music\My Playlists\New Playlist.wpl')

The path to the playlist file will be defined in the 'Rip music to this location' setting in Media Player's options.

To remove a playlist, call the PlaylistCollection object's Remove method, passing it the name of the PlayList object:

playlists = player.PlaylistCollection
split_enz_playlist = playlists.getByName('Split Enz').Item(0)
playlists.Remove(split_enz_playlist)

To add a song to a playlist, call the PlayList object's appendItem method, passing it the song object:

song = media_collection.getByName('Fly Me to the Moon').Item(0)
playlist = playlists.getByName('Frank & Dino').Item(0)
playlist.appendItem(song)

To remove a song from a playlist, call the PlayList object's removeItem method, passing it the song object:

playlist.removeItem(song)

Playing a playlist is much like playing an individual song. First get the individual PlayList object, then pass its sourceURL value to the player object's OpenPlayer method:

playlist = playlists.getByName('Frank & Dino').Item(0)
player.OpenPlayer(playlist.sourceURL)

And there you have it.

Further details can be found in this Microsoft TechNet article.

As always, feel free to post a comment or send email with questions, comments, or suggestions.

Thanks for stopping by!

Automating and Managing iTunes with Ruby

We've spent a lot of time on this blog looking at how to get work done with Ruby and Microsoft Office (Excel, Word, Access, Outlook). Let's take a break and go play... with iTunes.

Apple's popular iTunes application for Windows includes a COM interface. This allows you to automate and manage iTunes with Ruby. You can launch the application, search for and play songs, set user interface properties, and manage your iTunes library and PlayLists using Ruby code. Let's take a look...

First, of course, we start by connecting to the iTunes application object, launching iTunes if it isn't already running:

require 'win32ole'
itunes = WIN32OLE.new('iTunes.Application')

Controlling the iTunes User Interface

To place iTunes into MiniPlayer mode (or return it to Full Mode), set the BrowserWindow.MiniPlayer property:

itunes.BrowserWindow.MiniPlayer = true

To toggle the play/pause button, simply call the PlayPause method:

itunes.PlayPause

To increase or decrease sound volume, adjust the SoundVolume property:

itunes.SoundVolume = itunes.SoundVolume + 50
itunes.SoundVolume = itunes.SoundVolume - 25

To go to the previous or next track, call those methods:

itunes.PreviousTrack
itunes.NextTrack

Managing Your iTunes Library

OK, that covers several standard user interface features. Now, let's look at how you can work with your content.

Let's create an instance of the Library, which is a child object of the Application object:

library = itunes.LibraryPlaylist

We'll be working with this LibraryPlaylist object a lot going forward. It provides several methods that will return collections of Track and PlayList objects. For example, calling the Tracks method returns a collection of all tracks in the library:

tracks = library.Tracks

You can then select a song by name from this collection of Track objects:

song = tracks.ItemByName('At Long Last Love')

...and then play the track by calling its Play method:

song.Play

To search the LibraryPlaylist, call its Search method, passing it a string to search for, and an integer that determines what field to search, such as Artist, Album Title, or Track Title:

artist_tracks = library.Search('Sinatra', 2)
album_tracks = library.Search('Come Fly With Me', 3)
title_tracks = library.Search('Fly Me To The Moon', 5)

The Search and Tracks methods return a collection of Track objects, which you could iterate over. We might want to add all track objects in this collection to a ruby array called songs:

songs = []
for track in tracks
    songs << track
end

This would then allow us to use Ruby's excellent sort_by method to sort the songs array:

songs = songs.sort_by{|song| [song.Artist, song.Year, song.Album, song.TrackNumber]}

The Track object includes dozens of attributes pertaining to this track. As indicated above, there's Artist, Album, Year, and TrackNumber. But there's also Time (in minutes and seconds), Duration (total seconds), BPM (beats-per-minutes), Composer, Genre, and many more.

You could iterate over the songs collection and write data for each track to a file:

songs.each do |song|
    my_file.puts [song.Artist, song.Album, song.Name, song.Time].join("\t")
end

...or add the data to an Excel worksheet or SQLite database.

Working with iTunes PlayLists

To create a new PlayList, call the Application object's CreatePlaylist method, passing it the name of the new PlayList:

playlist = itunes.CreatePlaylist('My Playlist')

To add a song to a PlayList object, first get a Track object by calling the ItemByName method on a Tracks collection:

song = library.Tracks.ItemByName('At Long Last Love')

...then call the PlayList's AddTrack method, passing it the Track object:

playlist.AddTrack(song)

In the iTunes object model, PlayLists are child objects of Source objects. So, to create an array of all PlayList objects, we iterate over each Source object and get each PlayList object:

playlists = []
itunes.Sources.each do |source|
    source.PlayLists.each do |playlist|
        playlists << playlist
    end
end

This gives us a collection of PlayList objects that we can now work with:

for playlist in playlists do
    puts playlist.Name
end

To select a single PlayList by name:

playlist = itunes.Sources.ItemByName('Library').Playlists.ItemByName('All Sinatra')

To play the first track of a PlayList:

playlist.PlayFirstTrack

To exit the iTunes application, call its Quit method:

itunes.Quit

Further Reading

Further details can be found in this Microsoft TechNet article.

For further examples in Ruby, you might want to check out the itunes-control (itch) library source code.

And for those running iTunes on Mac OS, you may find this blog post by Zack Hobson to be of interest.

I hope you have found this information helpful. As always, post a comment here or send me an email if you have questions, comments, or suggestions.

Thanks for stopping by!

Automating Outlook with Ruby: Tasks

In our last episode, we used Ruby to extract appointments data from the Outlook Calendar, delete an appointment, and create a new appointment. Today we'll look at managing Outlook Tasks with Ruby.

Getting Existing Tasks

As usual, we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

Outlook consists of several Folder objects, including Inbox, Tasks, and Calendar. To get a folder object, call the MAPI object's GetDefaultFolder method, passing it an integer representing the folder to return. For the Tasks folder, this number is 13:

tasks = mapi.GetDefaultFolder(13)

The Tasks folder's Items method returns a collection of all tasks, which you can iterate over. The following code prints out some of the most-often used properties for each task:

for task in tasks.Items
    puts task.Subject
    puts task.Body
    puts task.DueDate
    puts task.PercentComplete
    puts task.Status
    puts task.Importance
    puts task.LastModificationTime
end

The Status method/property returns an integer, representing one of the following values:

0 -- Not started
1 -- In progress
2 -- Complete
3 -- Waiting on someone else
4 -- Deferred

The Importance method/property returns an integer, representing one of the following values:

0 -- Low
1 -- Normal
2 -- High

Filtering Your Tasks Collection

You can filter your collection of Task items by calling the Items collection's Restrict method, passing it a filter string. The following code gets all 'Order Yankees World Series Tickets' tasks, and then deletes each one:

for task in tasks.Items.Restrict("[Subject] = 'Order Yankees World Series Tickets'")
    task.Delete
end

Other possible filters include:

tasks.Items.Restrict("[Status] = 'Not started'")
tasks.Items.Restrict("[Importance] = 'High'")

As illustrated in the above example, to delete a Task, call its Delete method.

Creating New Tasks

To create a new task, call the Outlook Application object's CreateItem method, passing it the number 3, which represents a Task Item. This returns a new Task object:

task = outlook.CreateItem(3)

Then set values for various properties of the new Task object:

task.Subject = 'Send flowers to wife'
task.Body = 'Call flower shop and send roses.'
task.ReminderSet = true
task.ReminderTime = '7/19/2007 12:00 PM'
task.DueDate = '7/20/2007 12:00 PM'
task.ReminderPlaySound = true
task.ReminderSoundFile = 'C:\Windows\Media\Ding.wav'

When you've completed setting property values, call the Task object's Save method:

task.Save

Further details can be found in this Microsoft TechNet article.

As always, feel free to post a comment here or send me email with questions, comments, or suggestions.

Thanks for stopping by!

Automating Outlook with Ruby: Calendar Appointments

In a previous article, we looked at how to automate Microsoft Outlook to create and send a new email message. This time around, we'll use Ruby to extract appointments data from the Outlook Calendar, delete an appointment, and create a new appointment.

As usual, we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:

require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

Outlook consists of several Folder objects, including Inbox, Tasks, and Calendar. To get a folder object, call the MAPI object's GetDefaultFolder method, passing it an integer representing the folder to return. For the Calendar folder, this number is 9:

calendar = mapi.GetDefaultFolder(9)

The calendar folder's Items method returns a collection of all appointments, which you can iterate over. Each appointment object includes dozens of properties. The following code prints out six of the most-often used properties:

calendar.Items.each do |appointment|
    puts appointment.Subject
    puts appointment.Location
    puts appointment.Start
    puts appointment.Duration
    puts appointment.End
    puts appointment.Body
end

To delete an appointment, call its Delete method. For example, the following code locates an appointment based on its Subject value, then deletes it, if found:

calendar.Items.each do |appointment|
    if appointment.Subject == 'Punch Bud Selig in the Nose'
        appointment.Delete
    end
end

To create a new appointment, call the Outlook Application object's CreateItem method, passing it the number 1, which represents a Calendar Item. This returns a new Appointment object:

appointment = outlook.CreateItem(1)

Then set values for various properties of the new Appointment object:

appointment.BusyStatus = 2
appointment.Start = '7/29/2007 11:00 AM'
appointment.Duration = 300
appointment.Subject = 'Baseball Hall of Fame Induction'
appointment.Body = 'Tony Gwynn and Cal Ripken Jr.'
appointment.Location = 'Cooperstown, NY'
appointment.ReminderMinutesBeforeStart = 15
appointment.ReminderSet = true

The BusyStatus value is an integer with the following possible values:

olFree = 0
olTentative = 1
olBusy = 2
olOutOfOffice = 3

The Duration value is an integer representing the appointment length in minutes.

For an all-day event, forget the Duration property and instead set the AllDayEvent property to true:

appointment.AllDayEvent = true

To make an appointment recurring, you'll want to work with the Appointment object's Recurrence child object. You get this object by calling the GetRecurrencePattern method:

recurrence = appointment.GetRecurrencePattern

Now you'll set the RecurrenceType to one of the following values:

0 -- To set an appointment that occurs each and every day.
1 -- To set an appointment that occurs on the same day each week.
2 -- To set an appointment that occurs on the same day each month.
5 -- To set an appointment that occurs on the same day each year.

So, for an appointment that occurs on this day and time (as defined in your Start property above) each week:

recurrence.RecurrenceType = 1

Next, we'll set the PatternStartDate property. This value needs to be on or before the Start value defined above:

recurrence.PatternStartDate = '8/6/2007'

Finally, we set the PatternEndDate property:

recurrence.PatternEndDate = '8/27/2007'

When you've completed setting property values, call the Appointment object's Save method:

appointment.Save

And there you have it. Further details can be found in this Microsoft TechNet article.

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

Thanks for stopping by!

Using Ruby & WMI to Get Win32 Process Information

In an earlier post, we looked at using Windows Management Instrumentation (WMI) to determine if a "USB Mass Storage Device" is inserted. Today we'll use WMI to get information about the Win32 processes being run.

WMI is installed and already running on all recent versions of Windows, so we'll connect to it using the win32ole library's connect method:

require 'win32ole'
wmi = WIN32OLE.connect("winmgmts://")

Next, we'll call WMI's ExecQuery method to query the Win32_Process table:

processes = wmi.ExecQuery("select * from win32_process")

This returns a collection of all WMI Win32_Process objects.

The Win32_Process class has dozens of properties. You can find a complete list of those properties here. You could also produce a list of these properties by calling the Properties_ method (note the underscore) on one of the Win32_Process objects. This returns a collection of Properties; then call the Name method/property for each of these Property objects. For example:

for process in processes do
    for property in process.Properties_ do
        puts property.Name
    end
    break
end

To obtain data about a process, call that Process object's relevant property/method. For example, the following code prints four properties for each process:

for process in processes do
    puts "Name: #{process.Name}"
    puts "CommandLine: #{process.CommandLine}"
    puts "CreationDate: #{process.CreationDate}"
    puts "WorkingSetSize: #{process.WorkingSetSize}"
    puts
end

That's all for now. Got a question or suggestion? Post a comment or send me email.

Thanks for stopping by!

Querying the Windows Registry for the Default Mail Client

Following up on my post about sending email with MS Outlook, a reader asks, "I wonder how one can mine out the chosen default mail client... Is that somewhere in the Registry?"

It does indeed appear to be in the Windows Registry, and can therefore be extracted using the win32/registry library. I've just started hacking around with this library, but this bit of code seems to do the trick...

require 'win32/registry'

def get_mail_client
    Win32::Registry::HKEY_LOCAL_MACHINE.open('Software\Clients\Mail') do |reg|
        reg_typ, reg_val = reg.read('')
        return reg_val
    end
end

mail_client = get_mail_client

...though I am sure it can be improved upon.

On my machine, this returns 'Microsoft Outlook'.

Well, there you have it. As always, post comments or send email with enhancements, questions, or suggestions for future topics.

Thanks for stopping by!

Automating Outlook with Ruby: Sending Email

Just as we have done with Excel and Word, we can use the win32ole library to automate various tasks in Microsoft Outlook. Let's take a look at how to create an new email message.

Of course, we'll want to require the win32ole library:

require 'win32ole'

Create an instance of the Outlook Application object:

outlook = WIN32OLE.new('Outlook.Application')

Outlook is a single-instance application, so if it is already running, calling the WIN32OLE.new method will behave the same as the WIN32OLE.connect method, returning the running instance.

To create a new item in Outlook, call the Application object's CreateItem method and pass it a numeric value that represents the type of item to create. Valid arguments include:

olMailItem = 0
olAppointmentItem = 1
olContactItem = 2
olTaskItem = 3
olJournalItem = 4
olNoteItem = 5
olPostItem = 6

So, to create a new email message (ie, MailItem), we call the CreateItem method with an argument of 0 (zero):

message = outlook.CreateItem(0)

This returns the newly-created mail item object. We'll proceed by setting values for this MailItem object's properties and calling some of its methods.

To define the subject line of the message, set its Subject value with a string:

message.Subject = 'Double-Header Today'

To define the message body, set the Body value to a string:

message.Body = 'This is the message body.'

Alternatively, for an HTML message body, set the HtmlBody value to an HTML-encoded string.

Define the recipient by setting the To property...

message.To = 'ted.williams@redsox.com'

...or call the Recipients.Add method one or more times:

message.Recipients.Add 'ted.williams@redsox.com'
message.Recipients.Add 'joe.dimaggio@yankees.com'

Got attachments to add? Call the Attachments.Add method one or more times, passing it the path and name of the attachment:

message.Attachments.Add('c:\my_folder\my_file.txt', 1)

The second argument, 1, indicates that this is an attached file, rather than a link to the original file.

You can save the unsent message in your Outbox by calling the Save method...

message.Save

This would then allow you to open and review the message before manually clicking the Send button.

Of course, you can send the message with the Send method:

message.Send

Note that when using the Send method, Outlook may display a security alert that forces the user -- after a forced pause of about five seconds -- to click 'Yes' to allow the message to be sent. I am unaware of a method for avoiding this alert dialog.

UPDATE: I have discovered, but not yet used, a free Outlook add-in from MAPILab that allows the end user to set Outlook security settings. And the related ($99) Security Manager claims to allow you to bypass the Outlook security alerts, with a single line of code. I can't recommend either solution, not having tried them, but mention them here for your information.

That about wraps our show for today. Would you like to see more about Automating Outlook with Ruby? As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Using Ruby & ADO to Work with Excel Worksheets

In an earlier article, I discussed using ActiveX Data Objects (ADO) to access a Microsoft Access database. A reader commented that ADO can also be used to access data in an Excel worksheet. Here's a brief demonstration...

As usual, we'll use the win32ole library:

require 'win32ole'

Create a new ADODB.Connection object:

connection = WIN32OLE.new('ADODB.Connection')

To open a connection to your Excel workbook, we'll call the Connection object's Open method and pass it a connection string. You can use same the Microsoft Jet driver used for accessing an MS Access database, but we need to append an "Extended Property" to specify that this is an Excel woorkbook:

conn_string =  'Provider=Microsoft.Jet.OLEDB.4.0;'
conn_string << 'Data Source=c:\my_folder\my_workbook.xls;'
conn_string << 'Extended Properties=Excel 8.0;'
connection.Open(conn_string)

Now, we'll create an ADO recordset object:

recordset = WIN32OLE.new('ADODB.Recordset')

When calling the RecordSet object's Open method, pass it your SQL statement and the open connection object. When working with an Excel worksheet as your table, append '$' to the worksheet table name and wrap it in brackets:

recordset.Open("select * from [Sheet1$];", connection)

The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:

data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:

connection.close

There you have it! My thanks to reader Khaoz for the suggestion of using ADO with Excel.

Other articles about working with ADO can be found under the ado label to the right.

As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Automating Excel with Ruby: Formatting Worksheets

If you're going to automate the generation of an Excel worksheet, you might as well make it look good. There are a near-infinite number of methods that can be called upon to format rows, columns, ranges, and cells. Let's take a look at some of the most common.

To format the first row of a worksheet as bold-font, set the Font.Bold value:

worksheet.Rows(1).Font.Bold = true

To format numbers in a range, set the NumberFormat value.

To format column D as Currency:

worksheet.Columns(4).NumberFormat = "$###,##0.00"

To format column A as a date (mm/dd/yy):

worksheet.Column("A").NumberFormat = "mm/dd/yy"

Alternatively, you can set the Style value:

worksheet.Column("A").Style = "Currency"
worksheet.Column("B").Style = "Percent"

To set the alignment on a range, set its HorizontalAlignment value:

worksheet.Rows(1).HorizontalAlignment = 2          # Left
worksheet.Columns("A:F").HorizontalAlignment = 4   # Right
worksheet.Cells(3, 5).HorizontalAlignment = -4108  # Center

To auto-fit the width of a column, or the height of a row, call its AutoFit method:

worksheet.Columns.AutoFit
worksheet.Rows(1).AutoFit

To set the width of a column, set its ColumnWidth value:

worksheet.Columns(4).ColumnWidth = 25.0

To set the height of a row, set its RowHeight value:

worksheet.Rows.RowHeight = 15.0

To apply highlighting to a range, set its Interior.ColorIndex value:

worksheet.Rows(10).Interior.ColorIndex = 6   # Yellow

That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.

Thanks for stopping by!

Making Use of Ruby's ENV Object

Ruby provides the ENV object for easy access to a variety of environment variables. ENV is not actually a hash, but you access individual values like you would a hash:

ENV['Path']

If you need to, you can convert it into a Hash using the to_hash method, so to see a complete list of keys and values stored in ENV, you could do this:

ENV.to_hash.each do |key, value|
    puts("#{key}\t#{value}")
end

Many of these values you may never use, but a few are worth noting...

HOMEDRIVE returns the home drive, for example:

ENV['HOMEDRIVE']    # returns "C:"

APPDATA returns the path to the Application Data folder(ie, "C:")

ENV['APPDATA']    # returns "C:\Documents and Settings\Joe DiMaggio\Application Data"

USERPROFILE returns the user's home directory:

ENV['USERPROFILE']    # returns "C:\Documents and Settings\Joe DiMaggio"

USERNAME returns the Windows user's name or Windows login:

ENV['USERNAME']    # returns "Joe DiMaggio"

ProgramFiles returns the path to the Program Files folder:

ENV['ProgramFiles]    # returns "C:\Program Files"

windir returns the path to the Program Files folder:

ENV['windir']    # returns "C:\Windows"

Writing to Environment Variables

To quote The Pickaxe:

"A Ruby program may write to the ENV object, which on most systems changes the values of the corresponding environment variables. However, this change is local to the process that makes it and to any subsequently spawned child processes. This inheritance of environment variables is illustrated in the code that follows. A subprocess changes an environment variable and this change is seen in a process that it then starts. However, the change is not visible to the original parent. (This just goes to prove that parents never really know what their children are doing.)"

A tip of the hat to reader Revence, who shared a code snippet that utilized the ENV object, reminding me of its value.

That's all for now. As always, leave a comment here or via email if you have questions or suggestions for future topics.

Thanks for stopping by!

Automating Word with Ruby: Formatting Text

A reader asks "Can you tell me how to format text in Word using Ruby? I'm most interested in controlling justification, indenting paragraphs, changing fonts, using italics, bold, and underlining, etc."

This is the latest in a series of articles about Automating Word with Ruby.

There's a variety of formatting that you can apply to Range objects in Word. Let's look at some of the most common...

Fonts

To change the font of a Range object, set its Font.Name value to a valid font name:

document.Words(1).Font.Name = 'Verdana'

To change the font size of a Range object, set its Font.Size value:

document.Words(1).Font.Size = 18

To set Bold, Italic, and Underline properties of a Range object, supply a boolean value:

document.Sentences(1).Font.Bold = true
document.Sentences(1).Font.Italic = true
document.Sentences(1).Font.Underline = true

Indenting

To indent a paragraph, call its Indent method. For example, to indent all paragraphs in the document:

document.Paragraphs.Indent

To un-indent a paragraph, call its Outdent method. For example, to un-indent only the first paragraph:

document.Paragraphs(1).Outdent

Alignment

To align a paragraph, set its Alignment value to one of the following values:

Left = 0
Center = 1
Right = 2
Justify = 3

To center-align the second paragraph, for example:

document.Paragraphs(2).Alignment = 1

That's all for now, and I hope you find it helpful. As always, post a comment here or send me an email to request future topics.

Thanks for stopping by!