Friday, June 29, 2007

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:

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.

Sunday, June 24, 2007

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:

"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.

Friday, June 15, 2007

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."

Tuesday, June 12, 2007

While I was away, a reader asked, "Anyone know how to monitor Mass Storage Insert events (eg. When someone inserts a USB drive) from Ruby?" So let's take a look at how to use Windows Management Instrumentation (WMI) to determine if a "USB Mass Storage Device" is inserted.

Microsoft says that WMI "is the primary management technology for Microsoft® Windows® operating systems." 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:

Monday, June 4, 2007

A reader in the Ruby forum recently asked about using Ruby to get data from a Microsoft Access database. This can be done easily using the ActiveX Data Objects (ADO), via the win32ole library. Let's walk through it...

As usual, require the win32ole library:

require 'win32ole'

Next, create a new ADODB Connection object, which will manage the connection to the database:

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

To open a connection to the Access database, call the Connection object's Open method, passing it the Provider and Data Source (your Access mdb file) parameters:

To perform a query that returns a recordset of data, first create a new ADODB Recordset object:

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

Then call the Recordset object's Open method, passing it the SQL statement and your existing Connection object:

recordset.Open(sql, connection)

This loads the query results into the Recordset object. 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:

Saturday, June 2, 2007

Sometimes the only user interface you need is a message box. Maybe you just need to alert the user to the completion of a process; or maybe you need to ask a question to which the user responds Yes or No. You'd rather not resort to a console window; but neither do you want to load an entire GUI library to display a simple message box. "Dave", you say, "there must be a better way."

Indeed, there is. Use the DL library to call the MessageBoxA Windows API function.

First, require the dl library:

require 'dl'

We'll feed the function our message text, a dialog box title, and an integer that defines what buttons to display. Let's define some meaningful constants to represent the possible button values: