Interop with Excel


#1

The following code runs fine:

require ‘Microsoft.Office.Interop.Excel, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’
Excel = Microsoft::office::Interop::Excel

but when I try to get the Application object as follows:

app = Excel.Application.new

I get the following error:

excel.rb:0: undefined method `Application’ for
Microsoft::office::Interop::Excel:Module (NoMethodError)

am I not understanding how this works or is this just not working yet?


#2

I’m replying to my own post because it looks like I found part of my
problem. I changed the code to:

require ‘Microsoft.Office.Interop.Excel, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’
include Microsoft::office::Interop::Excel
excel = ApplicationClass.new
excel.Visible = true

and that works. But I’m still having trouble getting access a Workbook:

p excel.ActiveWorkbook
workbooks = excel.Workbooks
p workbooks.respond_to?(‘Open’)

returns:
nil
false

I would be grateful for any help. Thanks, Jeff

Jeff Siebold wrote:

The following code runs fine:

require ‘Microsoft.Office.Interop.Excel, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’
Excel = Microsoft::office::Interop::Excel

but when I try to get the Application object as follows:

app = Excel.Application.new

I get the following error:

excel.rb:0: undefined method `Application’ for
Microsoft::office::Interop::Excel:Module (NoMethodError)

am I not understanding how this works or is this just not working yet?


#3

When I do this, Excel opens up, but it opens up without a workbook. If I
open a file manually (via Excel’s GUI), then the ActiveWorkbook is
populated.

I don’t know the Excel API, but if excel.workbooks.open(“Foo.xls”) is
supposed to open a workbook in the Excel instance, then you’ve found a
bug. Excel.workbooks.open is being overridden by Ruby’s Kernel#open
method.

If this is the case, can you open a bug at
http://rubyforge.org/tracker/?func=browse&group_id=4359&atid=16798 and
assign it to Tomas?

Thanks,
JD


#4

It’s arathe a missing feature. We don’t support COM yet.

Tomas


#5

This should now mostly work.

respond_to? is not supported for COM yet, but “methods” is (try
workbooks.methods(false)).

require ‘Microsoft.Office.Interop.Excel, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’
include Microsoft::office::Interop::Excel
excel = ApplicationClass.new
excel.Visible = true
workbooks = excel.Workbooks
workbooks.Open(‘c:\foo.xsl’)

You can also do:

def newCOM progId
System::Activator.CreateInstance(System::Type.GetTypeFromProgID(progId))
end

ex = newCOM(“Excel.Application”)

ex.Visible = true
nb = ex.Workbooks.Add
ws = nb.Worksheets[1]
p ws.Name

10.times do |i|
10.times do |j|
ws.Cells[i + 1, j + 1] = (i + 1) * (j + 1)
end
end

Tomas


#6

The COM interop stuff looks great.
Does this mean that there’s now a way to “cast” a COM object to a type
(and
perform a QueryInterface) ?