Trouble using WIN32OLE for excel

[Haley Thrapp]

I am trying to do some things that seem pretty basic to me using excel.
Basically, I have a two column spreadsheet that I would like to make a
scatterplot out of. I am able to create a chart, but I run into issues
as soon as I try to give it a title. Any ideas? Also, if there are any
experts out there, I am looking for a good reference for using the
WIN32OLE in ruby. I am not very experienced with the WIN32OLE. I have
also tried setting curr_chart.HasTitle and
curr_chart.ChartTitle.Characters as I have seen in other threads, but
none of these work for me. Is it possible I am missing a package even
though I am able to handle all the other work I have tried with Excel?

require 'win32ole'
excel = WIN32OLE.new("excel.application")
excel['Visible'] = TRUE

module ExcelConsts
end

WIN32OLE.const_load(excel, ExcelConsts)

workbook = excel.Workbooks.Add
worksheet = workbook.Worksheets(1)

*snip*

charts = excel.Charts
charts.Add

curr_chart = excel.Charts(1)
curr_chart.Activate
curr_chart.ChartType=ExcelConsts::XlLineMarkers
curr_chart.ChartTitle.Text = "Testing chart title"


The error I am getting:

parse_sizeVlatency.rb:118:in `method_missing': (WIN32OLERuntimeError)
OLE error code:800A03EC in Microsoft Office Excel
Unable to set the Text property of the ChartTitle class
HRESULT error code:0x80020009
Exception occurred. from parse_sizeVlatency.rb:118



Thanks!

 

[bbiker]

I am trying to do some things that seem pretty basic to me using excel.
Basically, I have a two column spreadsheet that I would like to make a
scatterplot out of. I am able to create a chart, but I run into issues
as soon as I try to give it a title. Any ideas? Also, if there are any
experts out there, I am looking for a good reference for using the
WIN32OLE in ruby. I am not very experienced with the WIN32OLE. I have
also tried setting curr_chart.HasTitle and
curr_chart.ChartTitle.Characters as I have seen in other threads, but
none of these work for me. Is it possible I am missing a package even
though I am able to handle all the other work I have tried with Excel?

require 'win32ole'
excel = WIN32OLE.new("excel.application")
excel['Visible'] = TRUE

module ExcelConsts
end

WIN32OLE.const_load(excel, ExcelConsts)

workbook = excel.Workbooks.Add
worksheet = workbook.Worksheets(1)

*snip*

charts = excel.Charts
charts.Add

curr_chart = excel.Charts(1)
curr_chart.Activate
curr_chart.ChartType=ExcelConsts::XlLineMarkers
curr_chart.ChartTitle.Text = "Testing chart title"

The error I am getting:

parse_sizeVlatency.rb:118:in `method_missing': (WIN32OLERuntimeError)
OLE error code:800A03EC in Microsoft Office Excel
Unable to set the Text property of the ChartTitle class
HRESULT error code:0x80020009
Exception occurred. from parse_sizeVlatency.rb:118

Thanks!

This is what I found in the Microsoft Office Excel 2003 Visual Basic
Reference (VBAXL10.CHM), perhaps it will help.

Using the ChartTitle Object
Use the ChartTitle property to return the ChartTitle object. The
following example adds a title to embedded chart one on the worksheet
named "Sheet1."

With Worksheets("sheet1").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "February Sales"
End With

Remarks
The ChartTitle object doesn't exist and cannot be used unless the
HasTitle property for the chart is True.

This might work ... not tested
curr_chart.HasTitle = 'True'
curr_chart.ChartTitle.Text = "Testing chart title"