preload
Finding Office Application Version numbers using Ruby Cisco Catalyst 4500 Series Console Information
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:

def insertCheckmark(range)
   range.FormulaR1C1 = "P"
   range.Font.Name = "Wingdings 2"
   range.Font.Bold = true
   range.HorizontalAlignment = ExcelConst::XlCenter
end

Notice that it has to be the .FormulaR1C1 method, and not just a normal .Value method. I also made it Bold and Centered. Here’s my code for the X-out mark:

def insertXout(range)
   range.FormulaR1C1 = "O"
   range.Font.Name = "Wingdings 2"
   range.Font.Bold = true
   range.HorizontalAlignment = ExcelConst::XlCenter
end

There are tons of fun characters you can insert this way!

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

Leave a Reply