From: Li Chen <chen_li3@...>
Date: 2006-11-30T18:47:14+09:00
Subject: Re: VBA to Ruby code again

Jan Svitok wrote:
> Li:
> Could you post the whole code as it seems you've got the constants
> wrong - notice the error appers at the first constant. Where are you
> putting them? Kernel? Or did you include the constant module?
> 

> Moral of the story:  in ruby, all constants start with a capital letter.


Hi Jan,

I post my the whole codes here. Here are some information about my 
script:
1) read the raw data  from a text file and extract the columns I need 
into an array 2) calculate the mean and SD 3)transform  results of mean 
and standard error into a 2D array( each row is a group of exp), 
respectively 4) draw the plot using win32ole. (In my current example I 
use two arrays, one for each group)

Thanks,

Li




####
require 'win32ole'

module ExcelConst
end

#create an excel object and make it visible
    excel=WIN32OLE.new('Excel.Application')

#load excel constant
    WIN32OLE.const_load(excel, ExcelConst)

#create an excel workbook and make it visible
    excel.Visible=TRUE
    workbook1=excel.Workbooks.Add
    worksheet1=workbook1.Worksheets(1)

 #bring worksheet1 to the front
    worksheet1.Select

#process data  into an array(in my future script)

#fill the data into worksheet1

    worksheet1.Range('B1:F1').Value=[1,2,3,4,5] # time points

    # mean data
    worksheet1.Range('A2:F2').Value=['Group A',100,200,150,200,  200] 
#Group A
    worksheet1.Range('A3:F3').Value=['Group B',  150,100,80,60,50] 
#Group B

    # SD data/standard deviation
 worksheet1.Range('A5:F5').Value=['Group A',  10,15,20,10,30] # SD for 
Group A
 worksheet1.Range('A6:F6').Value=['Group B',20,13,40,20,10] # SD for 
Group B


#add a chart
worksheet1.Range('A8').Select
chart1=excel.Charts.Add
#chart1.Type=ExcelConst::XlLine
chart1.ChartType=ExcelConst::XlLineMarkers
chart1.SetSourceData Source=worksheet1.Range("A1:F3")

#chart specifications
chart1.PlotBy=ExcelConst::XlRows #plot by rows
#chart1.PlotBy=1         #plot by rows
#chart1.PlotBy=0        #plot by columns
#chart1.Location Where=ExcelConst::XlLocationAsObject

chart1.HasTitle ="True"
chart1.ChartTitle.Characters.Text = "Chart1"

chart1.Axes(ExcelConst::XlCategory, ExcelConst::XlPrimary).HasTitle = 
"True"
chart1.Axes(ExcelConst::XlCategory, 
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "Day"

chart1.Axes(ExcelConst::XlValue, ExcelConst::XlPrimary).HasTitle = 
"True"
chart1.Axes(ExcelConst::XlValue, 
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "cpm"


# add SD error bars

chart1.SeriesCollection(1).Select
chart1.SeriesCollection(1).HasErrorBars = "True"

chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R5C2:R5C6"
chart1.MinusValues="=Sheet1!R5C2:R5C6"

chart1.SeriesCollection(2).Select
chart1.SeriesCollection(2).HasErrorBars = "True"

chart1.SeriesCollection(2).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R6C2:R6C6"
chart1.MinusValues="=Sheet1!R6C2:R6C6"

#save workbook
path='C:\Ruby\self\win32\macro-1.xls'
workbook1.Saveas(path)

#close workbook
workbook1.Close()

#ending session
excel.Quit
excel=nil
GC.start


>ruby macro1.rb
macro1.rb:61: undefined local variable or method `xlY' for main:Object 
(NameError)
>Exit code: 1

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