How to point to proper worksheet in excel

ruby-talk
1

I have following code:

@xl = WIN32OLE.new('Excel.Application')
@xl['Visible'] = TRUE
@wb = @xl.Workbooks.Add
@ws = @xb.Worksheets("XXXXX")

The problem is that last line throws exception: moethod_missing:
Worksheets

Why? I had read examples from net and did everything according to them.

Another question is how to create a chart in excel and:
add several series to it
named it
assign names to axis
assign names to the series
set chart type.

···

--
Posted via http://www.ruby-forum.com/.

2

I have following code:

@xl = WIN32OLE.new('Excel.Application')
@xl['Visible'] = TRUE
@wb = @xl.Workbooks.Add
@ws = @xb.Worksheets("XXXXX")

Try this:

@wb.Sheets.Add #this adds a new 'generic' worksheet to your workbook (also note that you have a typo in the last line of the code above @xb -> @wb)
new_sheet = @wb.Worksheets(4)
new_sheet.Name = "XXXXX"

The problem is that last line throws exception: moethod_missing:
Worksheets

Why? I had read examples from net and did everything according to them.

If you poke around in irb you can get the OLE methods that you can use via a method called "ole_methods"

Another question is how to create a chart in excel and:
add several series to it
named it
assign names to axis
assign names to the series
set chart type.

···

--
If you're really set on using ruby, I'd build a macro in XL to do what you want and then work backwards from the macro-code to try and automate this process via ruby.
Alternatively (and perhaps easier) is to simply build a workbook with an embedded macro to build a chart of your choosing it in the first place and use this as a starting point--you could even call the macro from ruby.
Or if the data will always be in the same cells, you can pre-build the chart and simply use ruby (or whatever) to populate the relevant cells and the chart will already be there!
There are many possibilities, but the best probably depends on the specifics of what you're trying to do.

Posted via http://www.ruby-forum.com/.

This email and any attached files are confidential and intended solely for the intended recipient(s). If you are not the named recipient you should not read, distribute, copy or alter this email. Any views or opinions expressed in this email are those of the author and do not represent those of the company. Warning: Although precautions have been taken to make sure no viruses are present in this email, the company cannot accept responsibility for any loss or damage that arise from the use of this email or attachments.

3

Marcin Tyman wrote:

I have following code:

@xl = WIN32OLE.new('Excel.Application')
@xl['Visible'] = TRUE
@wb = @xl.Workbooks.Add
@ws = @xb.Worksheets("XXXXX")

The problem is that last line throws exception: moethod_missing:
Worksheets

Why? I had read examples from net and did everything according to them.

As Paul mentioned, you had a typo, where "@xb" should be "@wb".

A newly-created workbook (which you created via the Workbooks.Add
method) will have the default worksheet names (ie, Sheet1", "Sheet2")...

@ws = @wb.Worksheets("Sheet1")

You'll find details about working with Excel's workbook and worksheet
objects, with Ruby, here:

Another question is how to create a chart in excel and:
add several series to it
named it
assign names to axis
assign names to the series
set chart type.

See my reply to your other post here specifically about Excel charts.

David

···

--
Posted via http://www.ruby-forum.com/\.

4

Here's an exzmple that may help

require 'win32ole'
excel = WIN32OLE::new('excel.Application')
excel['Visible'] = true
workbook = excel.Workbooks.Open(filename)
worksheet = workbook.Worksheets(1) #get hold of the first worksheet

You can also find a lot of information on using Ruby with Excel here

Hope this helps.

Luis

···

On Jul 7, 2:44 am, Marcin Tyman <m.ty...@interia.pl> wrote:

I have following code:

@xl = WIN32OLE.new('Excel.Application')
@xl['Visible'] = TRUE
@wb = @xl.Workbooks.Add
@ws = @xb.Worksheets("XXXXX")

The problem is that last line throws exception: moethod_missing:
Worksheets

Why? I had read examples from net and did everything according to them.

Another question is how to create a chart in excel and:
add several series to it
named it
assign names to axis
assign names to the series
set chart type.
--
Posted viahttp://www.ruby-forum.com/.