# Help with correct syntax in win32ole and excel **URL:** \ **Category:** Ruby\ **Created:** [November 12, 2006, 4:19pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706 "2006-11-12T16:19:10Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\ **Post date:** [November 12, 2006, 4:19pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706/1 "2006-11-12T16:19:10Z")
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 ---
**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\ **Post date:** [November 12, 2006, 6:12pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706/2 "2006-11-12T18:12:25Z")
On 11/12/06, Li Chen [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > … > > 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](http://msdn2.microsoft.com/en-gb/library/microsoft.office.tools.excel.chart.linegroups(VS.80).aspx) ---
**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\ **Post date:** [November 12, 2006, 6:43pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706/3 "2006-11-12T18:43:29Z")
Jan S. wrote: > 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](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 ---
**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\ **Post date:** [November 12, 2006, 10:13pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706/4 "2006-11-12T22:13:26Z")
Christian M. wrote: > 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](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 ---
**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\ **Post date:** [November 12, 2006, 9:37pm UTC](https://www.ruby-forum.com/t/help-with-correct-syntax-in-win32ole-and-excel/75706/5 "2006-11-12T21:37:11Z")
Li Chen skrev: > Unknown name. from excel1.rb:37 Hi Li and Jan, 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](http://wiki.rubygarden.org/Ruby/page/show/ScriptingExcel) which is the best guide to automating Excel via Ruby that exists. Rgds, Christian