[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

drawing borders in excel

Alex Ciarlillo

11/28/2006 7:53:00 PM

Could anyone give me the line of code you would use to draw a border
around a cell range in ruby? I know how to change the interior color of
a cell like so:

worksheet.Range("a#{currRow}").Interior['ColorIndex'] = 36 #yellow

I found documentation for the Borders property, BorderAround object and
a few other things but the docs are for VB and am not sure the proper
syntax for ruby.
I have gotten most of my info so far from here
http://wiki.rubygarden.org/Ruby/page/show/Scri... but still
cannot figure this one out.

Thanks

--
Posted via http://www.ruby-....
5 Answers

Alex Ciarlillo

11/28/2006 8:13:00 PM

0

Okay quick update... I figured out how to change the color of my border
and it shows up:

worksheet.Range("a#{currRow}:d#{currRow}").Borders['ColorIndex'] = 3

Buuuut when I try to change the line weight or style I get an OLE error.
Neither of these work:

worksheet.Range("a#{currRow}:d#{currRow}").Borders['LineStyle'] =
'xlContinuous'
worksheet.Range("a#{currRow}:d#{currRow}").Borders['Weight'] = 'xlThick'

The OLE error says unable to set the Weight property of the Borders
class. Same for Line Style. This makes me think that maybe the values I
am trying to set it to are incorrect since this also happens when I try
to change the color index to something like 99. So I believe I am
accessing the property correctly but not setting the correct value,
however all the examples I could find use those values.
Any ideas?

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

Paul Rogers

11/28/2006 8:31:00 PM

0

xlThick is a constant in excel, use the vb editor in Excel to find the
value ( its 4 )
then, in your ruby code use

xlThick=4
worksheet.Range("a#{currRow}:d#{currRow}").Borders['Weight'] = xlThick



Alex Ciarlillo wrote:
> Okay quick update... I figured out how to change the color of my border
> and it shows up:
>
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['ColorIndex'] = 3
>
> Buuuut when I try to change the line weight or style I get an OLE error.
> Neither of these work:
>
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['LineStyle'] =
> 'xlContinuous'
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['Weight'] = 'xlThick'
>
> The OLE error says unable to set the Weight property of the Borders
> class. Same for Line Style. This makes me think that maybe the values I
> am trying to set it to are incorrect since this also happens when I try
> to change the color index to something like 99. So I believe I am
> accessing the property correctly but not setting the correct value,
> however all the examples I could find use those values.
> Any ideas?
>
> --
> Posted via http://www.ruby-....

lrlebron@gmail.com

11/28/2006 8:35:00 PM

0

Have you tried recording a macro in Excel and taking a look at the VB
code it generates? It should point you in the right direction. Did you
try xlThin?

Here's a sample macro generated with Excel

Range("E1:H2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Hope this helps.

Luis



Alex Ciarlillo wrote:
> Okay quick update... I figured out how to change the color of my border
> and it shows up:
>
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['ColorIndex'] = 3
>
> Buuuut when I try to change the line weight or style I get an OLE error.
> Neither of these work:
>
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['LineStyle'] =
> 'xlContinuous'
> worksheet.Range("a#{currRow}:d#{currRow}").Borders['Weight'] = 'xlThick'
>
> The OLE error says unable to set the Weight property of the Borders
> class. Same for Line Style. This makes me think that maybe the values I
> am trying to set it to are incorrect since this also happens when I try
> to change the color index to something like 99. So I believe I am
> accessing the property correctly but not setting the correct value,
> however all the examples I could find use those values.
> Any ideas?
>
> --
> Posted via http://www.ruby-....

dwhite618

11/28/2006 8:54:00 PM

0


You might consider loading all the Excel constants, that way you can
reduce one layer of definition. See if this example helps:


require 'win32ole'

excel = WIN32OLE::new('excel.Application')
WIN32OLE.const_load(excel)
workbook = excel.Workbooks.Add
excel['Visible'] = true
sheet = workbook.worksheets(1)
sheet['Name'] = "One"
sheet.Range("a1:d1")['Value'] = [1,2,3,4]
sheet.Range("a1:d1").Borders['Weight'] = WIN32OLE::XlThick

Alex Ciarlillo

11/28/2006 9:19:00 PM

0

Okay that was all I had to do was load the excel constants. Thanks


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