Re: Unable to use Range method for excel

On Mon, Jul 16, 2012 at 5:59 AM, agung surya <[e-mail]> wrote:
> 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(result,id)
>   @@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.