Excel chart question

ruby-talk
1

On Win32ole the excel chart created

I have two columns such as below:

excel.Range("A1").Value = 1
excel.Range("A2").Value = 2
excel.Range("A3").Value = 3

excel.Range("B1").Value = 15
excel.Range("B2").Value = 16
excel.Range("B3").Value = 17

excel.Range("A1:A3").Select();
excelchart1 = workbook.Charts.Add();

excel.Range("B1:B3").Select();
excelchart2 = workbook.Charts.Add();

It will show the error message.
Seems can't use the Select on the same worksheet.

How to create two charts for column A and column B at the same time?

Thanks,

···

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

2

On Win32ole the excel chart created

I have two columns such as below:

excel.Range("A1").Value = 1
excel.Range("A2").Value = 2
excel.Range("A3").Value = 3

excel.Range("B1").Value = 15
excel.Range("B2").Value = 16
excel.Range("B3").Value = 17

excel.Range("A1:A3").Select();

   excel.Range("B1:B3").Select();

excelchart1 = workbook.Charts.Add();

excel.Range("B1:B3").Select();
excelchart2 = workbook.Charts.Add();

It will show the error message.
Seems can't use the Select on the same worksheet.

How to create two charts for column A and column B at the same time?

Thanks,

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

The problem is that once you create the first chart the range.select method
is focused on the "chart sheet" and not the original sheet (with the data).
Therefore you either need to set both ranges first:

excel.Range("A1:A3").select
excel.Range("B1:B3").select
excelchart1 = workbook.Charts.add()
excelchart2 = workbook.Charts.add()

or you need to set the focus back on the original worksheet:

excel.Range("A1:A3").select
worksheet = workbook.Worksheets('Sheet1').select
excelchart1 = workbook.Charts.add()
excel.Range("B1:B3").select
excelchart2 = workbook.Charts.add()

My preference would be the first plus associating the ranges with variables
and not using select:

range_1 = excel.range("A1:A3")
range_2 = excel.range("B1:B3")
excelchart1 = workbook.charts.add(range_1)
excelchart2 = workbook.charts.add(range_2)

Timothy

···

On Mon, Aug 8, 2011 at 7:40 AM, CC Chen <dickyhide@gmail.com> wrote:

3

Timothy Barnes wrote in post #1015519:

excel.Range("B3").Value = 17

excel.Range("A1:A3").Select();

   excel.Range("B1:B3").Select();

Thanks,

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

The problem is that once you create the first chart the range.select
method
is focused on the "chart sheet" and not the original sheet (with the
data).
Therefore you either need to set both ranges first:

excel.Range("A1:A3").select
excel.Range("B1:B3").select
excelchart1 = workbook.Charts.add()
excelchart2 = workbook.Charts.add()

or you need to set the focus back on the original worksheet:

excel.Range("A1:A3").select
worksheet = workbook.Worksheets('Sheet1').select
excelchart1 = workbook.Charts.add()
excel.Range("B1:B3").select
excelchart2 = workbook.Charts.add()

My preference would be the first plus associating the ranges with
variables
and not using select:

range_1 = excel.range("A1:A3")
range_2 = excel.range("B1:B3")
excelchart1 = workbook.charts.add(range_1)
excelchart2 = workbook.charts.add(range_2)

Timothy

Sorry, I tried those methods but still had some problems.

excel.Range("A1:A3").select
excel.Range("B1:B3").select
excelchart1 = workbook.Charts.add()
excelchart2 = workbook.Charts.add()

I used the method it could show two charts but the value were "B1 to
B3".

excel.Range("A1:A3").select
worksheet = workbook.Worksheets('Sheet1').select
excelchart1 = workbook.Charts.add()
excel.Range("B1:B3").select
excelchart2 = workbook.Charts.add()

Try this method just only created one chart and the range were "A1 to
A3".

  range_1 = excel.range("A1:A3")
  range_2 = excel.range("B1:B3")
  excelchart1 = workbook.charts.add(range_1)
  excelchart2 = workbook.charts.add(range_2)

Try this way no chart was created and also showed some error messages.
"method_missing":(in OLE method 'add':)(WIN32OLERuntimeError)....

Thanks,

···

On Mon, Aug 8, 2011 at 7:40 AM, CC Chen <dickyhide@gmail.com> wrote:

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

4

What version of windows, ruby, excel are you using?

5

Timothy Barnes wrote in post #1015611:

What version of windows, ruby, excel are you using?

Windows XP, Ruby 1.9.2-p180 and office 2010

···

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

6

I apologize. I was trying to do things too fast. Below I corrected 2
different ways, both of which worked on my computer. I am using office 2007.
That could have some effect. Let me know if the below code doesn't work.

Using select: (previously I have mixed up two lines of code 2, 3)

excel.Range("A1:A3").select
excelchart1 = workbook.Charts.add()
worksheet = workbook.Worksheets('Sheet1').select #replace 'Sheet1' with you
worksheet name
excel.Range("B1:B3").select
excelchart2 = workbook.Charts.add()

Not using select: (needed to add new method call.

range_1 = excel.range("A1:A3")
range_2 = excel.range("B1:B3")
excelchart1 = workbook.charts.add()
excelchart1.setsourcedata(range_1)
excelchart2 = workbook.charts.add()
excelchart2.setsourcedata(range_2)

My full program looks like:

require 'win32ole'

excel = WIN32OLE.connect('Excel.Application')
excel.visible = true
workbook = excel.Workbooks.Open('c:\xl_chart.xlsx')

excel.Range("A1").Value = 1
excel.Range("A2").Value = 2
excel.Range("A3").Value = 3

excel.Range("B1").Value = 15
excel.Range("B2").Value = 16
excel.Range("B3").Value = 17

#Option 1
#excel.Range("A1:A3").select
#excelchart1 = workbook.Charts.add()
#worksheet = workbook.Worksheets('Sheet1').select
#excel.Range("B1:B3").select
#excelchart2 = workbook.Charts.add()

#Option 2
range_1 = excel.range("A1:A3")
range_2 = excel.range("B1:B3")
excelchart1 = workbook.charts.add()
excelchart1.setsourcedata(range_1)
excelchart2 = workbook.charts.add()
excelchart2.setsourcedata(range_2)

Timothy

7

Timothy Barnes wrote in post #1015760:

I apologize. I was trying to do things too fast. Below I corrected 2
different ways, both of which worked on my computer. I am using office
2007.
That could have some effect. Let me know if the below code doesn't work.

Using select: (previously I have mixed up two lines of code 2, 3)

I lost the line "excelchart1.setsourcedata(range_1)"

And the chart can create by different range.

Thanks a lot.

···

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