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!

Using Ruby & WMI to Detect a USB Drive

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:

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

Next, we'll call WMI's ExecQuery method to query the Win32_USBControllerDevice to obtain a collection of USB Controller Device objects:

devices = wmi.ExecQuery("Select * From Win32_USBControllerDevice")

Each device in the collection has a Dependent method that returns a string something like this:

\\CASABLANCA\root\cimv2:Win32_PnPEntity.DeviceID="USB\\ROOT_HUB20\\3&41601B64&0"

To extract the Device ID value, we want to remove the double-quotes and grab the portion of the string after the "=":

device_name = device.Dependent.gsub('"', '').split('=')[1]

Now we'll use that Device ID to query the Win32_PnPEntity to obtain a collection of USB Device objects:

usb_devices = wmi.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '#{device_name}'")

Finally, we will iterate over the collection and call the Description method for each, looking for a value of 'USB Mass Storage Device':

for usb_device in usb_devices do
    if usb_device.Description == 'USB Mass Storage Device'
        # DO SOMETHING HERE
    end
end

Putting it all together, we have something like the following:

require 'win32ole'

wmi = WIN32OLE.connect("winmgmts://")

devices = wmi.ExecQuery("Select * From Win32_USBControllerDevice")
for device in devices do
    device_name = device.Dependent.gsub('"', '').split('=')[1]
    usb_devices = wmi.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '#{device_name}'")
    for usb_device in usb_devices do
        puts usb_device.Description
        if usb_device.Description == 'USB Mass Storage Device'
            # DO SOMETHING HERE
        end
    end
end

The above code is functional, but can certainly be improved upon and modified to meet your specific needs.

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

Thanks for stopping by!

Using Ruby & ADO to Work with MS Access Databases

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:

connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:\baseball\lahman54.mdb')

To execute an SQL statement that does not return a recordset (ie, CREATE, ALTER, INSERT), you can call the Connection object's Execute method:

connection.Execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

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:

data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:

recordset.Close

We could create a simple class to wrap up this functionality:

class AccessDb
    attr_accessor :mdb, :connection, :data, :fields

    def initialize(mdb=nil)
        @mdb = mdb
        @connection = nil
        @data = nil
        @fields = nil
    end

    def open
        connection_string =  'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        connection_string << @mdb
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        recordset = WIN32OLE.new('ADODB.Recordset')
        recordset.Open(sql, @connection)
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            @data = recordset.GetRows.transpose
        rescue
            @data = []
        end
        recordset.Close
    end

    def execute(sql)
        @connection.Execute(sql)
    end

    def close
        @connection.Close
    end
end

This class is very similar to an SqlServer class I previously discussed.
We would use this AccessDb class as follows:

db = AccessDb.new('c:\Baseball\lahman54.mdb')
db.open

db.query("SELECT * FROM AllStar WHERE playerID = 'conceda01';")
field_names = db.fields
rows = db.data

db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

db.close

That about wraps up our show for today. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Displaying a MessageBox Using the Windows API

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:

BUTTONS_OK = 0
BUTTONS_OKCANCEL = 1
BUTTONS_ABORTRETRYIGNORE = 2
BUTTONS_YESNO = 4

The function will return an integer representing the button that the user clicked, so let's define some meaningful constants to represent the possible return code values:

CLICKED_OK = 1
CLICKED_CANCEL = 2
CLICKED_ABORT = 3
CLICKED_RETRY = 4
CLICKED_IGNORE = 5
CLICKED_YES = 6
CLICKED_NO = 7

Here's our method to call the MessageBoxA function from the user32 DLL:

def message_box(txt='', title='', buttons=0)
    user32 = DL.dlopen('user32')
    msgbox = user32['MessageBoxA', 'ILSSI']
    r, rs = msgbox.call(0, txt, title, buttons)
    return r
end

Here's how we call it display a message box with an OK button:

message_box("Hello, World!", "Hi!", BUTTONS_OK)

Here's how we call it to display a message box with 'Yes' and 'No' buttons, and process the response:

response = message_box("Are you sure you want to proceed?", "Proceed?", BUTTONS_YESNO)
if response == CLICKED_YES
    # insert your code here
end

Finally, here it is in its entirety:

require 'dl'

# button constants
BUTTONS_OK = 0
BUTTONS_OKCANCEL = 1
BUTTONS_ABORTRETRYIGNORE = 2
BUTTONS_YESNO = 4

# return code constants
CLICKED_OK = 1
CLICKED_CANCEL = 2
CLICKED_ABORT = 3
CLICKED_RETRY = 4
CLICKED_IGNORE = 5
CLICKED_YES = 6
CLICKED_NO = 7

def message_box(txt, title=APP_TITLE, buttons=BUTTONS_OK)
    user32 = DL.dlopen('user32')
    msgbox = user32['MessageBoxA', 'ILSSI']
    r, rs = msgbox.call(0, txt, title, buttons)
    return r
end

response = message_box("Are you sure you want to proceed?", "Proceed?", BUTTONS_YESNO)
if response == CLICKED_YES
    # insert your code here
end

There you have it. As always, let me know if you have questions, comments, or suggestions.

Thanks for stopping by!