From: Nathan Smith <nlloyds@...>
Date: 2006-12-19T12:47:22+09:00
Subject: Suggestions for 'AutoOffice'?

------=_Part_24064_1599626.1166500039149
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

After using Ruby's win32ole library to automate some menial tasks at work
with MS Office, I started putting some frequently used methods together in a
library. I'd like to release a gem soon that could possibly be useful to
others, I'll call it "AutoOffice", but I wanted to ask a couple of questions
and post a sample here first.

It has a Base class which inherits from WIN32OLE. The other classes (Access,
Excel, Word, etc.) inherit from the Base class. It's intended to used
interactively or in scripts, and requires Ruby and Microsoft Office (tried
on 2003, XP most likely works, earlier versions and 2007 I have no idea.)

AutoOffice makes certain assumptions. For example, to hide images in the
active workbook with plain win32ole you would do:

x.activeWorkbook.displayDrawingObjects = XlHide

With AutoOffice it would be:

x.hide_pictures.

Most Excel methods work on the active sheet in the active workbook, and most
Word methods would work with the active document. I think this makes it
easier to work interactively, but you can use any of the standard OLE
methods at any time (and you will have to in most cases.) Not really a DSL,
it's more a nice set of shortcuts to use with win32ole.

A quick example: Opening a spreadsheet and auto filtering the first row.

file_name = 'c:\\work\\report.xls'
range_name = '1:1'

WIN32OLE:

require 'win32ole'
x = WIN32OLE.new('Excel.Application')
x.visible = true
x.open('file_name')
x.activesheet.enableautofilter = true
activesheet.range(range_name).autofilter.range = range_name

AutoOffice:

require 'auto_office'
x = Excel.new(file_name)
x.auto_filter(range_name)


A few questions:

What's the best way to determine when to add new methods and when to just
use win32ole? There are literally thousands of methods in those libraries
and sometimes it's not much harder to type x.activeworkbook.close than
x.close. I'm also trying to avoid generally non useful methods like
save_daily_report_from_outlook_as_a_zip_file_and_then_extract_it_then_open_it_up_in_excel()
:)

Any problem with using names for classes that might be trademarked? (I'll
ask my lawyer if I need an absolute answer, but just wondering.)

I haven't written any tests, but any tips on writing tests for a project
like this?

Could someone point me to some help with creating gems that require Windows
and gem_require in general? I'm using newgem and can't run 'rake package' on
Windows because of tar (the GNU version I installed doesn't have the
required features), Or I'll just use Linux to package it.

In:

def get(range = DefaultRange)
  range(range).value
end

is it OK to call the argument 'range' or should I just call it 'r'?

A sample of the code is below. Though there's still much missing (exception
handling, for example), any criticism is appreciated. Thanks to everyone for
the all the help.

Nate



require 'win32ole'

module AutoOffice
    class Base < WIN32OLE
        # Need to add CONST module
        OlFolderInbox = 6
        OlMailitem = 0
        XlHide = 3
        XlCSV = 6
        OfficeApps = ['msaccess', 'excel', 'frontpg', 'outlook',
                                    'powerpnt', 'mspub', 'winword']

        def show
            visible = true
        end

        def hide
            visible = false
        end

        def Base.kill_office
            system("taskkill" +
                OfficeApps.map {|app| " /im #{app}.exe"}.to_s +
                " /f")
        end

        def Base.kill(app)
            system("taskkill /im #{app}.exe /f")
        end
    end

    class Access < AutoOffice::Base
        def initialize(file_name = "")
            super('access.application')
            open(file_name) if(file_name != "")
            show
        end

        def open(file_name)
            openCurrentDataBase(file_name)
        end

        def run_macro(macro_name)
            doCmd.runMacro(macro_name)
        end
    end

    class Excel < AutoOffice::Base
        DefaultRange = 'a1'

        def initialize(file_name = "")
            super('excel.application')
            if(file_name == "")
                workbooks.add
            else
                open(file_name)
            end
            show
        end

        def auto_filter(range = DefaultRange)
            activesheet.enableautofilter = true
            activesheet.range(range).autofilter.range = range
        end

        def auto_fit(range = DefaultRange)
            range(range).columns.autofit
        end

        def first_nil(range = DefaultRange)
            num_cells = 0
            range(range).each do |cell|
                num_cells += 1
                break if(cell.value == nil)
            end
            num_cells
        end

        def close
            activeworkbook.close
        end

        def get(range = DefaultRange)
            range(range).value
        end

        def hide_pictures
            activeworkbook.displaydrawingobjects = XlHide
        end

        def open(file_name)
            workbooks.open(file_name)
        end

        def save_as_csv(file_name = activeworkbook.name.gsub(/\.xls/, ''))
            activeworkbook.saveas(file_name, XlCSV)
        end

        def set(range = DefaultRange, value = nil)
            range(range).value = value
        end
    end
end

------=_Part_24064_1599626.1166500039149--