preload
Mar 21

In my line of work I end up trying to move a large amount of text from web pages into things like Word and Excel. Getting the Text from the Web Browser is easy…Putting the text with formatting into an Office app could be a lot of work trying to parse through all the HTML and send the equivalent formatting commands with the text. Fortunately, Word and Excel had the ability to paste from the clipboard HTML and render it with the correct formatting! …Only problem is that for some reason, no one added HTML support in the Win32::Clipboard gem?!? (At least not in the 1.8.x version of Ruby, which I use.) So I spent a few hours looking over the existing clipboard.rb file, found a VBA example of an HTML Copy, and came up with the following code: Continue reading »

SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with:
Jan 27

With all those neat Wingdings characters available, its nice to be able to insert them into an Excel spreadsheet programatically. The easiest way to go about it is to Record a Macro of the character being inserted, then use the resulting VBA code as a reference for your Ruby code.

Working on a checklist application for a customer, I needed to insert checkmarks, and X-out characters into a column of the list. First step was to record the macro in Excel, then look at the resulting macro (To look at the macro in Excell 2003, click on Tools > Macro > Macros… then highlight the macro and click the ‘Edit’ button). For my checkmark, the code looked like this:

ActiveCell.FormulaR1C1 = "P"
    With ActiveCell.Characters(Start:=1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("I23").Select

So for my insertCheckmark function, my code looks like this:
Continue reading »

SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with:
Jan 24

Over at the Ruby on Windows blog, David posted an article about how the latest versions of Office allow for saving files as PDF. Which prompted me to ask how you can find out what the versions are, since Office 2003 apps don’t have this option. So in between watching the AFC Championship game, I figured it out and came up with the following program:

require 'win32ole'

xl = WIN32OLE.new('Excel.Application')
xl.visible = false

ver = nil
case xl.Version
   when "12.0"
     ver = "Excel 2007"
   when "11.0"
     ver = "Excel 2003"
   else
     ver = "{Unknown Version => #{xl.Version}}"
end
puts "     Excel Version: #{ver}"
xl.Quit

word = WIN32OLE.new('Word.Application')
word.visible = false
ver = nil
case word.Version
   when "12.0"
     ver = "Word 2007"
   when "11.0"
     ver = "Word 2003"
   else
     ver = "{Unknown Version => #{word.Version}}"
end
puts "      Word Version: #{ver}"
word.Quit

ppt = WIN32OLE.new('Powerpoint.Application')
#ppt.visible = false    ## Powerpoint 2007 does not allow itself to be hidden?!?
ver = nil
case ppt.Version
   when "12.0"
     ver = "Powerpoint 2007"
   when "11.0"
     ver = "Powerpoint 2003"
   else
     ver = "{Unknown Version => #{ppt.Version}}"
end
puts "Powerpoint Version: #{ver}"
ppt.Quit

ol = WIN32OLE.new('Outlook.Application')
#ol.visible = false
ver = nil
## Outlook seems to tack on a build number
tt = ol.Version.slice(0,4)
case tt
   when "12.0"
     ver = "Outlook 2007"
   when "11.0"
     ver = "Outlook 2003"
   else
     ver = "{Unknown Version => #{ol.Version}}"
end
puts "   Outlook Version: #{ver}"
ol.Quit
SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with:
Nov 29

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

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

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.

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