ruby win32old on excel, replace VBA

rbexcel.rb

#!/usr/bin/env ruby

=begin
   Use ruby/win32ole on excel application, replace VBA
=end

require 'pry'
require 'tk'
if Gem.win_platform?
  require 'win32ole'
end


# version check
def undo_support
  if ((Tk::TK_VERSION.split('.').collect{|n| n.to_i} <=> [8,4]) < 0)
    false
  else
    true
  end
end

$default_scripts = [<<~'EOSS', <<~'EOSS', <<~'EOSS', <<~'EOSS', <<~'EOSS']
  # win32ole Usage
  require 'win32ole'

  excel = WIN32OLE.new('Excel.Application')
  excel.visible = true
  workbook = excel.Workbooks.Add()
  worksheet = workbook.Worksheets(1)
  worksheet.Range("A1:D1").value = ["North","South","East","West"]
  worksheet.Range("A2:B2").value = [5.2, 10]
  worksheet.Range("C2").value = 8
  worksheet.Range("D2").value = 20

  range = worksheet.Range("A1:D2")
  range.select
  chart = workbook.Charts.Add

  workbook.saved = true

  #excel.ActiveWorkbook.Close(0)
  #excel.Quit()
EOSS
  # show cell , range
  row = 1
  col = 'A'
  while true
    v1 = worksheet.Cells(row, col).value
    p [v1.class, v1]
    break unless v1
    row += 1
  end
  puts

  v1 = worksheet.UsedRange()
  v2 = v1.value
  p [v1.row, v1.column]
  p [v2.class, v2.size, v2]
  puts

  v1 = worksheet.Range("A1:D3").value
  p [v1.class, v1]
  puts
EOSS
  # show name
  puts AB().name, AS().name
EOSS
  # copy activesheet
  AS().copy
  AB().saved=true
EOSS
  # show old object methods
  puts " ====  #{AS().class}  ole_methods ===="
  puts AS().ole_methods.map { |e| e.name }.sort
EOSS


class MyOut
  def write *args
    if $txtLog
      sb = StringIO.new
      args.each { |e| sb.write( e.to_s + ' ') }
      sb.rewind
      $txtLog.value += sb.read
    end
  end
end


def get_curr
  excel = WIN32OLE.connect("excel.application")
  workbook = excel.ActiveWorkbook
  return unless workbook
  worksheet = workbook.ActiveSheet
  
  [worksheet, workbook, excel]
end

def AB()
  get_curr()[1]
end

def AS()
  get_curr()[0]
end


def btn1
  worksheet, workbook, _ = get_curr()
  if !worksheet
    $statbar.text = "no open ActiveSheet"
    return
  end
  $statbar.text = "#{workbook.name}->#{worksheet.name}"
end

def btn2
  worksheet, workbook, excel = get_curr()
  if !worksheet
    $txtLog.value += "\nnot ActiveSheet\n"
    return
  end

  begin
    $stdout_ori, $stdout = $stdout, MyOut.new

    puts "\n--------- #{Time.now.strftime '%H:%M:%S.%3N'} ---------"
    eval $nb.selected.value
  rescue Exception => erx
    $txtLog.value += %{----------------->>\n#{erx}\n#{erx.backtrace.join("\n")}\n}

  ensure
    $txtLog.yview_pickplace("end")
    $stdout = $stdout_ori
  end
end

lambda {
  i = 0
  define_method :i_no do
    i+=1
    i
  end
}.call

def addnbtxt(nb, value)
  tx1 = Tk::Text.new(nb) { |t|
    t.width = 3
    t.height = 2
    t.value = value
    if undo_support
      undo true
      autoseparators true
    end
    TkScrollbar.new(t) { |s|
      pack('side'=>'right', 'fill'=>'y')
      command proc{|*args| t.yview(*args)}
      t.yscrollcommand proc{|first,last| s.set first,last}
    }
  }.pack('expand'=>true, 'fill'=>'both')

  nb.add(tx1, :text=> "Script #{i_no}")
  nb.select(nb.tabs.size - 1)  # select last
end

def delnbtxt(nb)
  cur = nb.selected
  tabs = nb.tabs
  return unless tabs.size > 1

  if cur.value.strip != ''
    res = Tk.messageBox(
      parent: nb,
      type: 'yesno',
      icon: 'question',
      default: 'no' ,
      title: 'WARR',
      message: 'Delete Current Tab?'
      )
    return unless res == 'yes'    
  end

  i = tabs.index(cur)
  nb.forget(i)

  i = i-1
  i = 0 if i<0
  nb.select(i)  # select deleted left tab
end


def main
  $root = Tk::Root.new
  $root.geometry '1024x522'

  mmf = Tk::Frame.new($root)

  Tk::Frame.new(mmf) { |bf|
    Tk::Button.new(bf) { |t|
      t.command = method :btn1
      t.text = 'get Active Sheel'
    }.pack('side'=>'left')

    Tk::Button.new(bf) { |t|
      t.command = method :btn2
      t.text = 'run code'
    }.pack('side'=>'left')

    Tk::Button.new(bf) { |t|
      t.command = lambda {
        Thread.new { binding.pry }
      }
      t.text = '-> binding.pry'
    }.pack('side'=>'right')

    Tk::Button.new(bf) { |t|
      t.command = lambda { addnbtxt($nb, <<~"EOSS")
        puts "#{Time.now}"
        EOSS
      }
      t.text = 'add Note'
    }.pack('side'=>'right')
    Tk::Button.new(bf) { |t|
      t.command = lambda { delnbtxt($nb) }
      t.text = 'remove Note'
    }.pack('side'=>'right')
  }.pack('fill'=>'x')  #, 'expand'=>true

  Tk::Panedwindow.new(mmf, :orient=>:horizontal) { |pw|
    $pw=pw
    pack('expand'=>true, 'fill'=>'both')

    $txtLog = Tk::Text.new(pw) { |t|
      t.width = 80
      t.height = 2
      t.value = Time.now.to_s
      if undo_support
        undo true
        autoseparators true
      end
      TkScrollbar.new(t) { |s|
        pack('side'=>'right', 'fill'=>'y')
        command proc{|*args| t.yview(*args)}
        t.yscrollcommand proc{|first,last| s.set first,last}
      }
    }.pack('expand'=>true, 'fill'=>'both') #, 'side'=>'left'

    $nb = Ttk::Notebook.new(pw).pack(:fill=>:both, :expand=>true) { |nb|
      pack('expand'=>true, 'fill'=>'both') #, 'side'=>'right'
      enable_traversal()
    }
    $default_scripts.each { |scr_txt| addnbtxt($nb, scr_txt) }
    $nb.select(0)

    add($txtLog, width: 400)
    add($nb)
  }

  mmf.pack('expand'=>true, 'fill'=>'both')
  $statbar = Tk::Label.new(mmf).pack('fill'=>'x', 'side'=>'bottom')    #, 'expand'=>true

  Tk.mainloop

end



if __FILE__ == $0
  Thread.new { binding.pry }
  main()
end