gistfile1.txt

require "win32ole"

class ExcelOutputter
  def initialize(workbook_filename)
    @workbook_filename = workbook_filename
    # Create an instance of the Excel application object
    @excel = WIN32OLE.new('Excel.Application')
    # Make Excel visible
    @excel.Visible = 1
    # Add a new Workbook object
    @workbook = @excel.Workbooks.Add
  end

  def create_chart(title)
    # Select a worksheet for source data
    worksheet = @workbook.Worksheets("Sheet1")
    # http://rubyonwindows.blogspot.com/2008/06/automating-excel-creating-charts.html
    chart = worksheet.Shapes.AddChart.Chart
    # Excel insists on having source data, even if it's empty. Picky, isn't it?
    chart.SetSourceData('Source' => worksheet.Range("$A$1:$B$6"))
    chart.HasTitle = true
    STDERR.puts "#{__method__}: Before renaming the freshly created #{chart.ole_obj_help.name}, the title is #{chart.ChartTitle.Characters.Text.inspect}"
    chart.ChartTitle.Characters.Text = title
    STDERR.puts "#{__method__}: The chart has been created, and is still a #{chart.ole_obj_help.name} and now has a title of #{chart.ChartTitle.Characters.Text.inspect}"
    chart
  end

  def change_chart_title(chart, new_title)
    STDERR.puts "#{__method__}: Apparently the chart object is still a #{chart.ole_obj_help.name}"
    old_title = chart.ChartTitle.Characters.Text
    chart.ChartTitle.Characters.Text = new_title
    STDERR.puts "#{__method__}: The chart object is still a #{chart.ole_obj_help.name} and has been renamed from #{old_title.inspect} to #{chart.ChartTitle.Characters.Text.inspect}"
  end

  def move_chart(chart, target_worksheet_name)
    xlLocationAsObject = 2
    chart.Location("Where" => xlLocationAsObject, "Name" => target_worksheet_name)
    STDERR.puts "#{__method__}: The chart object is still a #{chart.ole_obj_help.name} and has been moved to #{target_worksheet_name.inspect}"
  end

  def write_to_file
    # Save the workbook
    @workbook.SaveAs(@workbook_filename)
    # Close the workbook
    @workbook.Close
    # Quit Excel
    @excel.Quit
  end

  def self.show_everything_works_if_you_do_not_change_a_moved_chart
    STDERR.puts "#{__method__}: Starting"
    excel_outputter = ExcelOutputter.new("chart_location_confusion.xlsx")
    chart = excel_outputter.create_chart("If you saw this it would mean change_chart_title never worked")
    excel_outputter.change_chart_title(chart, "Show that change_chart_title works")
    excel_outputter.move_chart(chart,  "Sheet2")
    # Don't change the chart title after changing its location
    # excel_outputter.change_chart_title(chart, "If you saw this it would mean change_chart_title works after you called move_chart")

    another_chart = excel_outputter.create_chart("If you saw this it would mean change_chart_title never worked")
    excel_outputter.change_chart_title(another_chart, "Check that change_chart_title or move_chart isn't broken permanently")
    excel_outputter.move_chart(another_chart, "Sheet3")
    excel_outputter.write_to_file
    STDERR.puts "#{__method__}: Finishing"
    STDERR.puts("\n\n")
  end

  def self.try_renaming_after_moving_the_same_chart
    STDERR.puts "#{__method__}: Starting"
    excel_outputter = ExcelOutputter.new("chart_location_confusion.xlsx")
    chart = excel_outputter.create_chart("If you saw this it would mean change_chart_title never worked")
    excel_outputter.change_chart_title(chart, "change_chart_title works before you call move_chart")
    excel_outputter.move_chart(chart,  "Sheet2")
    begin
      # This will no longer raise an exception, but it won't work
      excel_outputter.change_chart_title(chart, "Will not get here")
    rescue
      STDERR.puts "#{__method__}: It didn't work"
      raise
    else
      STDERR.puts "#{__method__}: It worked after all!"
    end
    excel_outputter.write_to_file
  end
end

if __FILE__ == $0
  ExcelOutputter.show_everything_works_if_you_do_not_change_a_moved_chart
  ExcelOutputter.try_renaming_after_moving_the_same_chart
end

produces

    show_everything_works_if_you_do_not_change_a_moved_chart: Starting
    create_chart: Before renaming the freshly created _Chart, the title is ""
    create_chart: The chart has been created, and is still a _Chart and now has a title of "If you saw this it would mean change_chart_title never worked"
    change_chart_title: Apparently the chart object is still a _Chart
    change_chart_title: The chart object is still a _Chart and has been renamed from "If you saw this it would mean change_chart_title never worked" to "Show that change_chart_title works"
    move_chart: The chart object is still a _Chart and has been moved to "Sheet2"
    create_chart: Before renaming the freshly created _Chart, the title is "Chart Title"
    create_chart: The chart has been created, and is still a _Chart and now has a title of "If you saw this it would mean change_chart_title never worked"
    change_chart_title: Apparently the chart object is still a _Chart
    change_chart_title: The chart object is still a _Chart and has been renamed from "If you saw this it would mean change_chart_title never worked" to "Check that change_chart_title or move_chart isn't broken permanently"
    move_chart: The chart object is still a _Chart and has been moved to "Sheet3"
    show_everything_works_if_you_do_not_change_a_moved_chart: Finishing
    
    
    try_renaming_after_moving_the_same_chart: Starting
    create_chart: Before renaming the freshly created _Chart, the title is ""
    create_chart: The chart has been created, and is still a _Chart and now has a title of "If you saw this it would mean change_chart_title never worked"
    change_chart_title: Apparently the chart object is still a _Chart
    change_chart_title: The chart object is still a _Chart and has been renamed from "If you saw this it would mean change_chart_title never worked" to "change_chart_title works before you call move_chart"
    move_chart: The chart object is still a _Chart and has been moved to "Sheet2"
    change_chart_title: Apparently the chart object is still a _Chart
    change_chart_title: The chart object is still a _Chart and has been renamed from "change_chart_title works before you call move_chart" to "Will not get here"
    try_renaming_after_moving_the_same_chart: It worked after all!