# Suggestions for 'AutoOffice'?

**URL:** <https://www.ruby-forum.com/t/suggestions-for-autooffice/80014>\
**Category:** Ruby\
**Created:** [December 20, 2006, 9:17pm UTC](https://www.ruby-forum.com/t/suggestions-for-autooffice/80014 "2006-12-20T21:17:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![confundido](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/90ced4/32.png) [@confundido](https://www.ruby-forum.com/u/confundido)\
**Post date:** [December 20, 2006, 9:17pm UTC](https://www.ruby-forum.com/t/suggestions-for-autooffice/80014/1 "2006-12-20T21:17:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![confundido](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/90ced4/32.png) [@confundido](https://www.ruby-forum.com/u/confundido)\
**Post date:** [December 21, 2006, 11:58am UTC](https://www.ruby-forum.com/t/suggestions-for-autooffice/80014/2 "2006-12-21T11:58:04Z")

</div>

Citát Nathan S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

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

Probably falls under fair use unless you’re selling those classes as  
commercial  
components. (I’m not saying the latter case doesn’t fall under fair use,  
I’d  
just ask a lawyer then.)

David V.