preload
New Blog Format Correct file paths for WIN32OLE applications in Ruby
Nov 24

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.

SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon

One Response to “Adding a Hyperlink to an Excel spreadsheet from Ruby”

  1. Correct file paths for WIN32OLE applications in Ruby Says:

    [...] Adding a Hyperlink to an Excel spreadsheet from Ruby Nov 25 [...]

Leave a Reply