Drawing borders in excel

Ruby
1

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/ScriptingExcel but still
cannot figure this one out.

Thanks

2

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?

3

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

4

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

5

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

6

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