How to point to proper worksheet in excel

Ruby
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.

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

On Jul 7, 2:44 am, Marcin T. [email protected] wrote:

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/.

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

4

Marcin T. 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