Class: RobustExcelOle::Excel

Inherits:
VbaObjects show all
Defined in:
lib/robust_excel_ole/excel.rb

Overview

This class essentially wraps a Win32Ole Application object. You can apply all VBA methods (starting with a capital letter) that you would apply for an Application object. See docs.microsoft.com/en-us/office/vba/api/excel.application(object)#methods

Constant Summary collapse

PROPERTIES = 
[:visible, :displayalerts, :calculation, :screenupdating]
@@hwnd2excel = 
{}

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(options = {}) ⇒ Excel 



152
 
# File 'lib/robust_excel_ole/excel.rb', line 152

def initialize(options = {}) end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object (private)

Raises:

  • (ObjectNotAlive) 


789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
 
# File 'lib/robust_excel_ole/excel.rb', line 789

def method_missing(name, *args) 
  super unless name.to_s[0,1] =~ /[A-Z]/
  raise ObjectNotAlive, 'method missing: Excel not alive' unless alive?
  if ::ERRORMESSAGE_JRUBY_BUG
    begin
      @ole_excel.send(name, *args)
    rescue Java::OrgRacobCom::ComFailException
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  else
    begin
      @ole_excel.send(name, *args)
    rescue NoMethodError
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  end
end

Instance Attribute Details

#hwnd ⇒ Object (readonly)

Returns the value of attribute hwnd



75
76
77
 
# File 'lib/robust_excel_ole/excel.rb', line 75

def hwnd
  @hwnd
end

#ole_excel ⇒ Object (readonly)

Returns the value of attribute ole_excel



72
73
74
 
# File 'lib/robust_excel_ole/excel.rb', line 72

def ole_excel
  @ole_excel
end

#properties ⇒ Object (readonly)

Returns the value of attribute properties



73
74
75
 
# File 'lib/robust_excel_ole/excel.rb', line 73

def properties
  @properties
end

Class Method Details

.close_all(options = { if_unsaved: :raise }, &blk) ⇒ Integer

closes all Excel instances remark: the returned number of closed Excel instances is valid only for known Excel instances if there are unknown Excel instances (opened not via this class), then they are counted as 1 options:

:if_unsaved    if unsaved workbooks are open in an Excel instance
                    :raise (default) -> raises an exception
                    :save            -> saves the workbooks before closing
                    :forget          -> closes the excel instance without saving the workbooks
                    :alert           -> give control to Excel

Options Hash (options):

  • :if_unsaved (Symbol) —

    :raise, :save, :forget, or :alert

  • block (Proc) 


283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
 
# File 'lib/robust_excel_ole/excel.rb', line 283

def self.close_all(options = { if_unsaved: :raise }, &blk)
  options[:if_unsaved] = blk if blk
  finished_number = error_number = overall_number = 0
  first_error = nil
  finishing_action = proc do |excel|
    if excel
      begin
        overall_number += 1
        finished_number += excel.close(if_unsaved: options[:if_unsaved])
      rescue
        first_error = $!
        error_number += 1
      end
    end
  end

  # known Excel-instances
  @@hwnd2excel.each do |hwnd, wr_excel|
    if wr_excel.weakref_alive?
      excel = wr_excel.__getobj__
      if excel.alive?
        excel.displayalerts = false
        finishing_action.call(excel)
      end
    else
      @@hwnd2excel.delete(hwnd)
    end
  end

  # unknown Excel-instances
  old_error_number = error_number
  9.times do |_index|
    sleep 0.1
    excel = new(WIN32OLE.connect('Excel.Application')) rescue nil
    finishing_action.call(excel) if excel
    free_all_ole_objects unless (error_number > 0) && (options[:if_unsaved] == :raise)
    break unless excel
    break if error_number > old_error_number # + 3
  end
  raise first_error if ((options[:if_unsaved] == :raise) && first_error) || (first_error.class == OptionInvalid)
  [finished_number, error_number]
end

.create(options = {}) ⇒ Excel

creates a new Excel instance

Options Hash (options):

  • :displayalerts (Variant)
  • :visible (Boolean)
  • :calculation (Symbol)
  • :screenupdating (Boolean) 


90
91
92
 
# File 'lib/robust_excel_ole/excel.rb', line 90

def self.create(options = {})
  new(options.merge(reuse: false))
end

.current(options = {}) ⇒ Excel

connects to the current (first opened) Excel instance, if such a running Excel instance exists returns a new Excel instance, otherwise

Options Hash (options):

  • :displayalerts (Variant)
  • :visible (Boolean)
  • :calculation (Symbol)
  • :screenupdating (Boolean) 


101
102
103
 
# File 'lib/robust_excel_ole/excel.rb', line 101

def self.current(options = {})
  new(options.merge(reuse: true))
end

.init ⇒ Object 



384
385
386
 
# File 'lib/robust_excel_ole/excel.rb', line 384

def self.init
  @@hwnd2excel = {}
end

.instance_count ⇒ Object 



406
407
408
 
# File 'lib/robust_excel_ole/excel.rb', line 406

def self.instance_count
  WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process').select { |p| p.Name == 'EXCEL.EXE' }.size
end

.kill_all ⇒ Integer

kill all Excel instances



390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
 
# File 'lib/robust_excel_ole/excel.rb', line 390

def self.kill_all
  number = 0
  WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process').each do |p|
    begin
      if p.Name == 'EXCEL.EXE'
        Process.kill('KILL', p.processid)
        number += 1
      end
    rescue
      # trace "kill error: #{$!}"
    end
  end
  init
  number
end

.known_instances_count ⇒ Object 



410
411
412
 
# File 'lib/robust_excel_ole/excel.rb', line 410

def self.known_instances_count
  @@hwnd2excel.size
end

.known_running_instance ⇒ Object

returns a running Excel instance opened with RobustExcelOle



455
456
457
 
# File 'lib/robust_excel_ole/excel.rb', line 455

def self.known_running_instance     
  self.known_running_instances.first
end

.known_running_instances ⇒ Enumerator 



460
461
462
463
464
465
466
467
468
469
470
471
472
 
# File 'lib/robust_excel_ole/excel.rb', line 460

def self.known_running_instances
  pid2excel = {}
  @@hwnd2excel.each do |hwnd,wr_excel|
    next unless wr_excel.weakref_alive?
    excel = wr_excel.__getobj__
    pid_puffer = ' ' * 32
    User32::GetWindowThreadProcessId(hwnd, pid_puffer)
    pid = pid_puffer.unpack('L')[0]
    pid2excel[pid] = excel
  end
  processes = WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process')     
  processes.map{ |p| pid2excel[p.ProcessId] if p.Name == 'EXCEL.EXE'}.compact.lazy.each
end

.new(win32ole_excel = nil, options = {}) ⇒ Excel

returns an Excel instance options:

:reuse            connects to an already running Excel instance (true) or
                  creates a new Excel instance (false)  (default: true)
:visible          makes the Excel visible               (default: false)
:displayalerts    enables or disables DisplayAlerts     (true, false, :if_visible (default))
:calculation      calculation mode is being forced to be manual (:manual) or automatic (:automtic)
                  or is not being forced (default: nil)
:screenupdating  turns on or off screen updating (default: true)

Options Hash (options):

  • :reuse (Boolean)
  • :visible (Boolean)
  • :displayalerts (Variant)
  • :screenupdating (Boolean)
  • :calculation (Symbol) 


122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
# File 'lib/robust_excel_ole/excel.rb', line 122

def self.new(win32ole_excel = nil, options = {})
  if win32ole_excel.is_a? Hash
    options = win32ole_excel
    win32ole_excel = nil
  end
  options = { reuse: true }.merge(options)
  ole_xl = if !win32ole_excel.nil? 
    win32ole_excel
  elsif options[:reuse] == true
    current_ole_excel
  end
  connected = (not ole_xl.nil?) && win32ole_excel.nil?
  ole_xl ||= WIN32OLE.new('Excel.Application')
  hwnd_xl = ole_xl.Hwnd
  stored = hwnd2excel(hwnd_xl)
  if stored && stored.alive?
    result = stored
  else
    result = super(options)
    result.instance_variable_set(:@ole_excel, ole_xl)
    result.instance_variable_set(:@hwnd, hwnd_xl)
    WIN32OLE.const_load(ole_xl, RobustExcelOle) unless RobustExcelOle.const_defined?(:CONSTANTS)
    @@hwnd2excel[hwnd_xl] = WeakRef.new(result)
  end
  reused = options[:reuse] && stored && stored.alive? 
  options = { displayalerts: :if_visible, visible: false, screenupdating: true }.merge(options) unless reused || connected
  result.set_options(options)        
  result
end

.running_excel_instances ⇒ Object

returns running Excel instances !!! This is work in progress the approach is currently restricted to visible Excel instances with at least one workbook



417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
 
# File 'lib/robust_excel_ole/excel.rb', line 417

def self.running_excel_instances
  win32ole_excel_instances = []
  hwnd = 0
  loop do
    hwnd = User32::FindWindowExA(0, hwnd, "XLMAIN", nil).to_i
    break if hwnd == 0
    hwnd2 = User32::FindWindowExA(hwnd, 0, "XLDESK", nil).to_i
    hwnd3 = User32::FindWindowExA(hwnd2, 0, "EXCEL7", nil).to_i
    interface_address_buffer = ' ' * 8
    guid = Oleacc::Guid.malloc
    guid.data1 = 0x20400
    guid.data2 = 0x0
    guid.data3 = 0x0
    guid.data4 = [0xc0,0x0,0x0,0x0,0x0,0x0,0x0,0x46]
    status = Oleacc::AccessibleObjectFromWindow(hwnd3, 0xFFFFFFF0, guid, interface_address_buffer)
    interface_address = nil
    if status == 0 
      interface_address = interface_address_buffer.unpack('L')[0]
    else
      raise ExcelREOError, "could not determine the addresss of the specified interface of the Excel object"
    end
    accessed_object_buffer = ' ' * 8
    # open issue: is there a dll containing QueryInterface?
    status = Ole32::QueryInterface(interface_address, guid, accessed_object_buffer)
    if status == 0
      accessed_object = accessed_object_buffer.unpack('L')[0]
      # open issue: a method, similar to create_win32ole in Win32ole creating a win32ole object 
      # we could use pr-win32ole (seems to be an old ruby gem, needing C to be installed)
      ole_excel = create_win32ole(accessed_object)
      win32ole_excel_instances << ole_excel.Application 
    else
      raise ExcelREOError, "could not determine the Excel object from window"
    end
  end
  win32ole_excel_instances.map{|w| w.to_reo}
end

Instance Method Details

#==(other_excel) ⇒ Object

returns true, if the Excel instances are alive and identical, false otherwise



551
552
553
 
# File 'lib/robust_excel_ole/excel.rb', line 551

def == other_excel
  self.Hwnd == other_excel.Hwnd if other_excel.is_a?(Excel) && alive? && other_excel.alive?
end

#alive? ⇒ Boolean

returns true, if the Excel instances responds to VBA methods, false otherwise



556
557
558
559
560
561
562
563
564
565
566
 
# File 'lib/robust_excel_ole/excel.rb', line 556

def alive?
  msg = 0x2008 
  wparam = 0
  lparam = 0
  flags = 0x0000 # 0x0002
  duration = 5000
  lpdw_result_puffer = ' ' * 32
  status = User32::SendMessageTimeoutA(hwnd, msg, wparam, lparam, flags, duration, lpdw_result_puffer)
  result = lpdw_result_puffer.unpack('L')[0]
  status != 0
end

#calculation=(calculation_mode) ⇒ Object

sets calculation mode retains the saved-status of the workbooks when set to manual



628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
 
# File 'lib/robust_excel_ole/excel.rb', line 628

def calculation= calculation_mode
  return if calculation_mode.nil?
  @properties[:calculation] = calculation_mode
  calc_mode_changable = @ole_excel.Workbooks.Count > 0 && @ole_excel.Calculation.is_a?(Integer)
  return unless calc_mode_changable
  retain_saved_workbooks do
    begin
      best_wb_to_make_visible = @ole_excel.Workbooks.sort_by {|wb|
        score =
          (wb.Saved    ? 0 : 40) +  # an unsaved workbooks is most likely the main workbook
          (wb.ReadOnly ? 0 : 20) +  # the main wb is usually writable
          case wb.Name.split(".").last.downcase
            when "xlsm" then 10  # the main workbook is more likely to have macros
            when "xls"  then  8
            when "xlsx" then  4
            when "xlam" then -2  # libraries are not normally the main workbook
            else 0
          end
        score
      }.last
      best_wb_to_make_visible.Windows(1).Visible = true
    rescue => e
      trace "error setting calculation=#{calculation_mode} msg: " + e.message
      trace e.backtrace
      # continue on errors here, failing would usually disrupt too much
    end
    @ole_excel.CalculateBeforeSave = false
    @ole_excel.Calculation = calculation_mode == :automatic ? XlCalculationAutomatic : XlCalculationManual
  end
end

#Calculation=(calculation_vba_mode) ⇒ Object

VBA method overwritten



660
661
662
663
664
665
666
667
668
 
# File 'lib/robust_excel_ole/excel.rb', line 660

def Calculation= calculation_vba_mode
  case calculation_vba_mode
  when XlCalculationManual
    @properties[:calculation] = :manual
  when XlCalculationAutomatic
    @properties[:calculation] = :automatic
  end
  @ole_excel.Calculation = calculation_vba_mode
end

#close(options = { if_unsaved: :raise }) ⇒ Object

closes the Excel

Options Hash (options):

  • :if_unsaved (Symbol) —

    :raise, :save, :forget, :alert

  • :hard (Boolean) —

    :if_unsaved if unsaved workbooks are open in an Excel instance

    :raise (default) -> raises an exception
:save            -> saves the workbooks before closing
:forget          -> closes the Excel instance without saving the workbooks
:alert           -> Excel takes over



335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
 
# File 'lib/robust_excel_ole/excel.rb', line 335

def close(options = { if_unsaved: :raise })
  finishing_living_excel = alive?
  if finishing_living_excel
    hwnd = @ole_excel.Hwnd rescue nil
    close_workbooks(if_unsaved: options[:if_unsaved])
    @ole_excel.Quit
    weak_wkbks.ole_free if false && defined?(weak_wkbks) && weak_wkbks.weakref_alive?
    weak_xl = WeakRef.new(@ole_excel)
  else
    weak_xl = nil
  end
  @ole_excel = nil
  GC.start
  sleep 0.1
  if finishing_living_excel
    if hwnd
      pid_puffer = ' ' * 32
      User32::GetWindowThreadProcessId(hwnd, pid_puffer)         
      pid = pid_puffer.unpack('L')[0]
      Process.kill('KILL', pid) rescue nil
    end
    @@hwnd2excel.delete(hwnd)
    weak_xl.ole_free if weak_xl.weakref_alive?
  end
  weak_xl ? 1 : 0
end

#displayalerts ⇒ Object

returns, wheter DisplayAlerts is enabled



609
610
611
 
# File 'lib/robust_excel_ole/excel.rb', line 609

def displayalerts
  @ole_excel.DisplayAlerts
end

#displayalerts=(displayalerts_value) ⇒ Object

enables DisplayAlerts in the current Excel instance



614
615
616
617
618
 
# File 'lib/robust_excel_ole/excel.rb', line 614

def displayalerts= displayalerts_value
  return if displayalerts_value.nil?
  @properties[:displayalerts] = displayalerts_value
  @ole_excel.DisplayAlerts = @properties[:displayalerts] == :if_visible ? @ole_excel.Visible : displayalerts_value
end

#each ⇒ Enumerator 



694
695
696
697
698
699
700
701
702
 
# File 'lib/robust_excel_ole/excel.rb', line 694

def each
  if block_given?
    ole_workbooks.lazy.each do |ole_workbook|
      yield workbook_class.new(ole_workbook)
    end
  else
    to_enum(:each).lazy
  end
end

#each_workbook(opts = { }) ⇒ Object

traverses all workbooks and sets options if provided



710
711
712
713
714
715
 
# File 'lib/robust_excel_ole/excel.rb', line 710

def each_workbook(opts = { })
  ole_workbooks.lazy.each do |ow|
    wb = workbook_class.new(ow, opts)
    block_given? ? (yield wb) : wb
  end
end

#each_workbook_with_index(opts = { }, offset = 0) ⇒ Object 



717
718
719
720
721
722
723
 
# File 'lib/robust_excel_ole/excel.rb', line 717

def each_workbook_with_index(opts = { }, offset = 0)
  i = offset
  ole_workbooks.each do |ow| 
    yield workbook_class.new(ow, opts), i 
    i += 1
  end
end

#focus ⇒ Object

Raises:

  • (ExcelREOError) 


727
728
729
730
731
 
# File 'lib/robust_excel_ole/excel.rb', line 727

def focus
  self.visible = true
  status = User32::SetForegroundWindow(@ole_excel.Hwnd)
  raise ExcelREOError, "could not set Excel window as foreground" if status == 0     
end

#recreate(opts = {}) ⇒ Excel

reopens a closed Excel instance options: reopen_workbooks (default: false): reopen the workbooks in the Excel instances :visible (default: false), :displayalerts (default: :if_visible), :calculation (default: false)

Options Hash (opts):

  • :reopen_workbooks (Boolean)
  • :displayalerts (Boolean)
  • :visible (Boolean)
  • :calculation (Boolean) 


163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/robust_excel_ole/excel.rb', line 163

def recreate(opts = {})
  unless alive?
    opts = {visible: false, displayalerts: :if_visible}.merge(
           {visible: @properties[:visible], displayalerts: @properties[:displayalerts]}).merge(opts)        
    @ole_excel = WIN32OLE.new('Excel.Application')
    @hwnd = @ole_excel.Hwnd
    set_options(opts)
    if opts[:reopen_workbooks]
      workbook_class.books.each{ |book| book.open if !book.alive? && book.excel.alive? && book.excel == self }
    end
  end
  self
end

#screenupdating=(screenupdating_value) ⇒ Object

sets ScreenUpdating



621
622
623
624
 
# File 'lib/robust_excel_ole/excel.rb', line 621

def screenupdating= screenupdating_value
  return if screenupdating_value.nil?
  @ole_excel.ScreenUpdating = @properties[:screenupdating] = screenupdating_value
end

#set_options(options) ⇒ Object

set options in this Excel instance



683
684
685
686
687
688
689
 
# File 'lib/robust_excel_ole/excel.rb', line 683

def set_options(options)      
  @properties ||= { }
  PROPERTIES.each do |property|
    method = (property.to_s + '=').to_sym
    send(method, options[property]) 
  end
end

#visible ⇒ Object

returns, whether the current Excel instance is visible



597
598
599
 
# File 'lib/robust_excel_ole/excel.rb', line 597

def visible
  @ole_excel.Visible
end

#visible=(visible_value) ⇒ Object

makes the current Excel instance visible or invisible



602
603
604
605
606
 
# File 'lib/robust_excel_ole/excel.rb', line 602

def visible= visible_value
  return if visible_value.nil?
  @ole_excel.Visible = @properties[:visible] = visible_value
  @ole_excel.DisplayAlerts = @properties[:visible] if @properties[:displayalerts] == :if_visible
end

#with_calculation(calculation_mode) ⇒ Object

sets calculation mode in a block



671
672
673
674
675
676
677
678
679
680
 
# File 'lib/robust_excel_ole/excel.rb', line 671

def with_calculation(calculation_mode)
  return unless calculation_mode
  old_calculation_mode = @ole_excel.Calculation
  begin
    self.calculation = calculation_mode
    yield self
  ensure
    @ole_excel.Calculation = old_calculation_mode if @ole_excel.Calculation.is_a?(Integer)
  end
end

#with_displayalerts(displayalerts_value) ⇒ Object

sets DisplayAlerts in a block



586
587
588
589
590
591
592
593
594
 
# File 'lib/robust_excel_ole/excel.rb', line 586

def with_displayalerts displayalerts_value
  old_displayalerts = @properties[:displayalerts]
  self.displayalerts = displayalerts_value
  begin
    yield self
  ensure
    self.displayalerts = old_displayalerts if alive?
  end
end

#workbooks ⇒ Array 



705
706
707
 
# File 'lib/robust_excel_ole/excel.rb', line 705

def workbooks
  to_a
end