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

▲
2
▼

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?

ruby
vbscript
win32ole
asked on Stack Overflow Jun 16, 2011 by Andrew Grimm • edited May 30, 2012 by mmmmmm

2 Answers

▲
1
▼

how 'bout

worksheet.PageSetup.CenterHeader = "&F" + 10.chr + "&A"
answered on Stack Overflow Jun 16, 2011 by davej
▲
1
▼

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"

or

"&F\n&A" #"&F\n&A"
answered on Stack Overflow Jul 6, 2014 by peter

User contributions licensed under CC BY-SA 3.0