Help with correct syntax in win32ole and excel

[Li Chen]

Hi all,

I want to use win32ole to do some automation with excel. Here is a line
code
which works.
...
ChartTypeVal=4
excel.Range("a1:b3").Select();
excelchart = workbook.Charts.Add();
excelchart.Type = ChartTypeVal;
...

I check Excel documentation: ChartTypeVal=4 is to draw a graph with
line and the name for this graph type is called xlLine. But when I
replace number 4 with name "xlLine" the script doesn't work. Which
syntax is used so that Ruby knows xlLine instead of number 4?

Thank you in advance,

Li

 

[Jan Svitok]

Hi all,

I want to use win32ole to do some automation with excel. Here is a line
code
which works.
...
ChartTypeVal=4
excel.Range("a1:b3").Select();
excelchart = workbook.Charts.Add();
excelchart.Type = ChartTypeVal;
...

I check Excel documentation: ChartTypeVal=4 is to draw a graph with
line and the name for this graph type is called xlLine. But when I
replace number 4 with name "xlLine" the script doesn't work. Which
syntax is used so that Ruby knows xlLine instead of number 4?

Thank you in advance,

Li

Try prefixing it with something. I've found at [1] that they refer to it as
Excel.XlChartType.xlLine

So try something similar.

[1] http://msdn2.microsoft.com/en-gb/library/microsoft.office.tools.excel.chart.linegroups(VS.80).aspx

 

[Li Chen]

Try prefixing it with something. I've found at [1] that they refer to it
as
Excel.XlChartType.xlLine

So try something similar.

[1]
http://msdn2.microsoft.com/en-gb/library/microsoft.office.tools.excel.chart.linegroups(VS.80).aspx

Hi,

if I change this line
excelchart.ChartType = ChartTypeVal;

into excelchart.ChartType= excelchart.XlChartType.xlLine

I get the following message:

excel1.rb:37:in `method_missing': unknown property or method
`XlChartType' (WIN32OLERuntimeError)
HRESULT error code:0x80020006
Unknown name. from excel1.rb:37

Li

 

[Li Chen]

Excel.XlChartType.xlLine won't work the same way in Ruby as in Visual
Basic.

The reason is that in the VB example, the constant xlLine in
XlChartType is accessed. In Ruby, constants must start with a capital
e.g. XlLine. This is however not implemented in Win32ole, so you must
load all the constants of the Excel typelibrary into a class or module
of your own:

class ExcelConst
end

WIN32OLE.const_load(excel, ExcelConst)

excelchart.ChartType = ExcelConst.XlLine

Note that xlLine now becomes XlLine.

The first four lines of the example are taken from
http://wiki.rubygarden.org/Ruby/page/show/ScriptingExcel which is the
best guide to automating Excel via Ruby that exists.

Rgds,
Christian

Many thanks to you all.

Here is the wroking script on my XP:
###########
require 'win32ole'

class ExcelConst
end

excel = WIN32OLE.new("Excel.Application")

workbook = excel.Workbooks.Add
excel.Range("a1:b1").Value= [3,10]
excel.Range("a2:b2").Value= [2,6]
excel.Range("a3:b3").Value = [1,3]

excel.Range("a1:b3").Select
excelchart = workbook.Charts.Add
WIN32OLE.const_load(excel, ExcelConst)
excelchart.ChartType = ExcelConst::XlConeCol

savefile='C:\Ruby\self\excel3.xls'
workbook.SaveAs(savefile)
excel.ActiveWorkbook.Close(0)
excel.Quit()

#########
In Ruby constant is accessed as ExcelConst::XlLine but not as
ExcelConst.XlLine.



Li