Using ruby to open an Excel file on a mac

Ruby
1

Hello everyone,

I have a bit of an issue that I’m trying to work through pertaining to
opening an excel file I want to open using ruby. I tried looking in
google and all sorts of places but all I could really find is how to
open a new file of Excel. This I can do no problem, however I am unable
to open an existing file.

I am currently using:

Mac OS X v 10.4.11 PowerPC G4.
Ruby v1.8.6
Rubygems v1.3.4

On the mac my beginning code looks like this

#!/usr/local/bin/env ruby

require ‘rubygems’
require ‘appscript’

#This script retrieves the data from excel and puts it into arrays
class Test_arraydata

def EXTCOdata(*arrays listed here, removed for security purposes)
begin

#opens excel
$excel=Appscript.app(‘Microsoft Excel’)
workbook=$excel.activate(’\Automation\Safari<Data location>’)

If anyone could help me out with this, it would be greatly appreciated.

Thank you.

2

Ryan W. [email protected] wrote:

require ‘appscript’
workbook=$excel.activate(‘\Automation\Safari<Data location>’)
So, you’re doing this with appscript? Well then, in the first place,
“activate” is not “open” (and in any case “activate” doesn’t take any
arguments, and doesn’t return a value). If you mean “open”, say “open”.

Next we come to your argument. What is that funny string with backward
slashes? If it is meant to be a posix path, it would need forward
slashes.

But in any case, “open” does not take a posix path. You need to use an
alias (i.e. a MacTypes::Alias). So, for example:

require ‘appscript’
f = MacTypes::Alias.path(“/Users/mattleopard/Desktop/testing.xls”)
excel = Appscript.app(“Microsoft Excel”)
excel.activate
excel.open f

I can’t tell whether what you need to learn is AppleScript or
rb-appscript, but I have a written a print book about the one and an
online book about the other:

AppleScript:

rb-appscript:

http://www.apeth.com/rbappscript/00intro.html

Hope this helps - m.

3

I have another question pertaining to individual tabs in the excel
workbook itself. I looked through the link on appscript that you sent
and maybe I missed it and if I did my apologies.

In windows I’m able to to do the following in order to get data from
different worksheets within the excel workbook

worksheet1= workbook.Worksheets(‘Testdata’)
worksheet2= workbook.Worksheets(‘Items’)

I have workbook defined as:
workbook = MacTypes::Alisas.path(/insert path here/workbookname.xls)

This should be the last question I have. Thanks for again for the help

4

Ryan W. [email protected] wrote:

I have workbook defined as:
workbook = MacTypes::Alisas.path(/insert path here/workbookname.xls)

This should be the last question I have. Thanks for again for the help

But this is what I mean about learning AppleScript / appscript. What you
are really asking, I think, is just “how do I refer to worksheets of a
workbook?” But Excel’s scripting dictionary tells you that, clearly.
This is very basic stuff. The stuff I linked you to tells you how to
read the scripting dictionary, discover the worksheets element of a
workbook object, and use it within rb-appscript. For the scripting
dictionary, see chapter 8 of the online book:

http://www.apeth.com/rbappscript/08dictionary.html

If nothing else, you could just say (assuming you’ve installed
ASDictionary, ):

require ‘appscript’
excel = Appscript.app(“Microsoft Excel”)
excel.workbooks.help

And chapter 5 of the online book tells you how to form the desired
reference:

http://www.apeth.com/rbappscript/05propel.html

Incidentally, Hamish Sanderson has just released a new version of
rb-appscript so be sure to update it (along with ASDictionary and
ASTranslate). I’ll be updating the online book Real Soon Now to take
account of the changes. m.

5

Thanks again for the response. I will admit that I have never really
programmed before, never really taken a class. Was asked to do this for
a project where I work and been stumbling along trying to learn as I go.
So my apologies on being a little slow on not knowing the basics.

6

Ryan W. [email protected] wrote:

Thanks again for the response. I will admit that I have never really
programmed before, never really taken a class. Was asked to do this for
a project where I work and been stumbling along trying to learn as I go.
So my apologies on being a little slow on not knowing the basics.

You’re doing very well, don’t worry! Contact me directly by email if you
need to, but I think you’ll find if you’ll take a day to read the
online rb-appscript documentation I’ve written, the answers to your
questions so far are there. m.