JohnAllen.us

A number of my Office automation applications are nothing more than generating status reports on a daily or weekly basis. Some of these can grow to be quite large, so its always nice to be able to highlight the new rows added to the report so folks can jump right to the new information.

Here’s how to add background highlight to an Excel row:

require 'win32ole'

file = Dir.pwd + "/" + @YourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
...
def highlightRow(ws, row)
   ws.Rows(row).Interior.ColorIndex = 20
   #ws.Rows($startrow+3).Interior.ColorIndex = 15 ## Grey
   #ws.Rows($startrow+7).Interior.ColorIndex = 19 ## Lt. Yellow
   #ws.Rows($startrow+8).Interior.ColorIndex = 20 ## Lt. Green
 end
...
highlightRow(ws, 7)

Once the values are all loaded in, its time to make the spreadsheet look pretty. Here’s how you draw a single, continuous, thin line around each cell in a range of cells. This function can be used an any number of cells…but only in one row. If you give it a range of more than one row, then it will draw a box around the rows, and add the vertical lines. As I am adding one row at a time in my applications, my function works fine for single rows. The ExcelConst:: constants were address in a previous post.

def boxRange(ws, range)
   b = range.Borders(ExcelConst::XlEdgeBottom)
   b.LineStyle = ExcelConst::XlContinuous
   b.Weight = ExcelConst::XlThin
   b.ColorIndex = 1
   t = range.Borders(ExcelConst::XlEdgeTop)
   t.LineStyle = ExcelConst::XlContinuous
   t.Weight = ExcelConst::XlThin
   t.ColorIndex = 1
   l = range.Borders(ExcelConst::XlEdgeLeft)
   l.LineStyle = ExcelConst::XlContinuous
   l.Weight = ExcelConst::XlThin
   l.ColorIndex = 1
   r = range.Borders(ExcelConst::XlEdgeRight)
   r.LineStyle = ExcelConst::XlContinuous
   r.Weight = ExcelConst::XlThin
   r.ColorIndex = 1
   iv = range.Borders(ExcelConst::XlInsideVertical)
   iv.LineStyle = ExcelConst::XlContinuous
   iv.Weight = ExcelConst::XlThin
   iv.ColorIndex = 1
end
...
boxRange(ws, ws.Range("a3:q3"))

UPDATE!: The version above would only box in one row at the time. At the time it was not a huge issue for me, since my then current project was only working on one row at a time. if you try using the xlInsideHorizontal parameter on just one row, it will error out. Projects since then required multi-row borders.My new version handles single or multi-row ranges:

def boxRange(ws, range)
   b = range.Borders(ExcelConst::XlEdgeBottom)
   b.LineStyle = ExcelConst::XlContinuous
   b.Weight = ExcelConst::XlThin
   b.ColorIndex = 1
   t = range.Borders(ExcelConst::XlEdgeTop)
   t.LineStyle = ExcelConst::XlContinuous
   t.Weight = ExcelConst::XlThin
   t.ColorIndex = 1
   l = range.Borders(ExcelConst::XlEdgeLeft)
   l.LineStyle = ExcelConst::XlContinuous
   l.Weight = ExcelConst::XlThin
   l.ColorIndex = 1
   r = range.Borders(ExcelConst::XlEdgeRight)
   r.LineStyle = ExcelConst::XlContinuous
   r.Weight = ExcelConst::XlThin
   r.ColorIndex = 1
   iv = range.Borders(ExcelConst::XlInsideVertical)
   iv.LineStyle = ExcelConst::XlContinuous
   iv.Weight = ExcelConst::XlThin
   iv.ColorIndex = 1
   ### xlInsideHorizontal will barf if the range is only one row!!
   if range.Rows.count > 1
     ih = range.Borders(ExcelConst::XlInsideHorizontal)
     ih.LineStyle = ExcelConst::XlContinuous
     ih.Weight = ExcelConst::XlThin
     ih.ColorIndex = 1
   end
end

Formatting a cell in Excel for a date format using Ruby is done like so:

def dateFormat(ws, range)
   range.NumberFormat = "[$-409]dd-mmm-yy;@"
   #range.NumberFormat = "[$-409]mmm-dd-yyyy;@"
   #range.NumberFormat = "[$-409]mmm-yy;@"
end
...
file = Dir.pwd + "/" + @YourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
...
dateFormat(ws, ws.Range("c3"))

This number format doesn’t seem to work the way some other websites seem to suggest. If I use the Excel constant ExcelConst::XlDMYFormat, all I get is a ‘4′ in the cell. So on a lark, I called the ‘NumberFormat’ method to see if it would return what the format was:

irb(main):249:0> y = ws.Range("g8")
=> #<WIN32OLE:0x3d6c9c0>
irb(main):250:0> y.NumberFormat
=> "[$-409]dd-mmm-yy;@"
irb(main):251:0>

which is how I got the correct format value. This seems to work for both Excel 2003 and Excel 2007.

There have been a couple of Ruby websites that  tell you how to import Excel Constant values into your program….but some seem to not be quite correct.   I don’t know if they are using a different version of Ruby or what, but here’s what has worked for me.

In your Ruby program, add the following lines:

require 'win32ole'

class ExcelConst
# blank method to hold all the constants
end
...

file = Dir.pwd + "/" + @YourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
# Load in Excel Constants
WIN32OLE.const_load(xl, ExcelConst)

Note that the ‘ExcelConst’ is class not a method or a def as I have seen on some other websites.  I tried those  and received this error

excel_example.rb:189: uninitialized constant ExcelConst (NameError)

I noticed in my last entry that I used a function that I didn’t describe.  When sending filenames to applications like Excel 2007 or Word 2007, they are expecting the path to use the backslash ‘\’ character, rather than the Ruby default of a forwardslash ‘/’ that is returned from the DIR class. Older versions of Excel or Word don’t seem to care as they accept either one.  But to be safe, I always use this built-in function from the Windows Scripting engine to make sure my paths are correct.

require 'win32ole'

def getAbsolutePathName(file)
   fso = WIN32OLE.new('Scripting.FileSystemObject') # VBA File System commands
   return fso.GetAbsolutePathName(file)
end

I looked high and low today for some examples of adding a Hyperlink to a cell in an Excell spreadsheet, and I never did find a thing. So with some trial and error, I came up with this:

require 'win32ole'

file = Dir.pwd + "/" + @@yourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
...
def hyperlinkAdd(ws, row, data)
   # insert into column 'c' on the row
   hl = ws.Hyperlinks.Add(ws.Range("$c$#{row}"), "")
   hl.Address = data.url
   hl.TexttoDisplay = data.name
   hl.ScreenTip = "Click to go to this URL #{data.url}"
end

In theory you should be able to specify all the parameters in the ‘Add’ method…but I never could get it work right until I did it this way.

UPDATE: A little more digging online came up with this code:

require 'win32ole'

file = Dir.pwd + "/" + @@yourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
...
def hyperlinkAdd(ws, row, data)
   # insert into column 'c' on the row
   ws.Hyperlinks.Add( 'Anchor'=> ws.Range("$c$#{row}"),
             'Address'=> data.url,
             'TexttoDisplay'=> data.name,
             'ScreenTip'=> "Click to go to this URL #{data.url}"
   )
end

Either version works, but I would have to guess the second version is a bit more efficient.

New Blog format now.  I always have lots of stuff to post, but none of my other blogs really fit the content that I wanted to put out there….so now there is this one that will cover my programming work and my radio work….and anything else I feel like posting.