preload
Nov 26

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)
Declaring them as a class gets around this error.
require ‘win32ole’
SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with:
Nov 25

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
SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with:
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
Tagged with: