Using ruby to open an Excel file on a mac

[Ryan Westerberg]

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.
--
Posted via http://www.ruby-forum.com/.

[Matt Neuburg-2]

Ryan Westerberg <phoenix_016@...> wrote:

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

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:

http://oreilly.com/catalog/9780596102111/

rb-appscript:

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

Hope this helps - m.

[Ryan Westerberg]

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
--
Posted via http://www.ruby-forum.com/.

[Matt Neuburg-2]

Ryan Westerberg <phoenix_016@...> wrote:

> 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

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.

[Ryan Westerberg]

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.
--
Posted via http://www.ruby-forum.com/.

[Matt Neuburg-2]

Ryan Westerberg <phoenix_016@...> 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.