Unable to use Range method for excel

Hi,

Why the Range function (excel)inside my def is not recognize?
Below is my code:
class XLS
@@excel = WIN32OLE::new(“excel.Application”)
@@workbook = @@excel.Workbooks.Open(“D:\TestData.xlsx”)
@@worksheet = @@workbook.WorkSheets(2)

def write(text)
@@worksheet.Range(“f3”).value = text
end

@@workbook.Save
@@workbook.Close
@@excel.quit
end

When I run this code an error message displayed like below:
test_write(Write::Testing):
WIN32OLERuntimeError: (in OLE method `Range’: )
OLE error code:0 in

HRESULT error code:0x800a01a8

D://Testing/xls.rb:7:in `method_missing'
D://Testing/xls.rb:7:in `write'

But When I move the code (@@worksheet.Range(“f3”).value = “test”) under
" @@worksheet = @@workbook.WorkSheets(2)" it work fine.

On Mon, Jul 16, 2012 at 5:59 AM, agung surya [email protected]
wrote:

@@worksheet.Range(“f3”).value = “test”
end

@@workbook.Save
@@workbook.Close
@@excel.quit
end

I don’t know how WIN32OLE stuff works, but from the method names it
seems that at this point, after defining the class, the workbook and
the excel application are closed. Code that appears in the class body
is executed right away. Only the code inside the defined methods is
executed when the methods are called.

So in your case, you open the excel application, open the workbook,
select a worksheet, then define the method write (which does nothing
until called), then save and close the workbook, and then quit the
excel application. Then, I guess in another piece of code you create
an instance of this class and call the write method, in which the
@@worksheet variable points to an invalid object, cause everything was
already closed and exited.

I also think that you don’t need class variables, you can do with
regular instance variables. Check something like this (untested):

class XLS
def initialize file
@excel = WIN32OLE::new(“excel.Application”)
@workbook = @excel.Workbooks.Open(file)
end

def write sheet_num, cell, value
worksheet = @workbook.WorkSheets(sheet_num)
worksheet.Range(cell).value = value
end

def close
@workbook.Save
@workbook.Close
@excel.quit
end
end

xls = XLS.new “D:\TestData.xlsx”
xls.write 2, “f3”, “test”
xls.close

There are other variations related to when you open the workbook or
the excel application depending on your use cases, but this should
give you some ideas.

Jesus.

“Jesús Gabriel y Galán” [email protected] wrote in post
#1068886:

On Mon, Jul 16, 2012 at 5:59 AM, agung surya [email protected]
wrote:

@@worksheet.Range(“f3”).value = “test”
end

@@workbook.Save
@@workbook.Close
@@excel.quit
end

Hi Jesus,

Thank you for your reply and solved my issue :slight_smile:
I understand the problem now.