JohnAllen.us

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!