Jano Svitok
11/30/2006 10:09:00 AM
On 11/30/06, Li Chen <chen_li3@yahoo.com> wrote:
> 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
Now, see for yourself: you use ExcelConst::XlWhatever in the
beginning, and only xlY later. So add ExcelConst:: and make the x X
and you'll be done.