[SOLVED] Is it practical to call vbscript functions from Ruby using win32ole?

Is it practical to call vbscript functions from Ruby using win32ole?

Is it practical to call a VBScript function, such as VBScript's Chr(charcode), from Ruby using win32ole?

Background: While working out how to add some nicely formatted headers to an excel worksheet, I followed my standard operating procedure: record an excel macro and copy and paste the code.

VBScript:

ActiveWindow.View = xlPageLayoutView
With ActiveSheet.PageSetup
    ' Irrelevant options snipped
    .CenterHeader = "&F" & Chr(10) & "&A"
    ' More irrelevant options snipped
End With

The following Ruby code

# workbook is an existing workbook object
worksheet = workbook.Worksheets.Add
worksheet.PageSetup.CenterHeader = "&F \n &A"

works, but I had to look up the Chr(charcode) documentation to check it was the exact same thing. I tried doing

worksheet.PageSetup.CenterHeader = "&F" + workbook.Chr(10) + "&A"

but got

WIN32OLERuntimeError: unknown property or method: `Chr'
    HRESULT error code:0x80020006
      Unknown name.
        from (irb):6:in `method_missing'
        from (irb):6
        from c:/Ruby19/bin/irb:12:in `<main>'

Is there any practical way to do the latter approach?

Solution

I wouldn't call a vbscript script for something that is easier done in Ruby but it is possible.

How you requested it:

require 'win32ole'

sc = WIN32OLE.new("ScriptControl")
sc.language="VBScript"
center_header = sc.eval('"&F" & Chr(10) & "&A"') #"&F\n&A"

and how you could do it in Ruby itself

"&F" + 10.chr + "&A" #"&F\n&A"

"&F\n&A" #"&F\n&A"

EDIT: For 64bit windows that wouldn't work any longer out of the box, you need to install a 64 bit dll that you can find at https://tablacus.github.io/scriptcontrol.html (The site is in japanese so translate the page in your browser)