Changing a chart's properties breaks after changing its location

Question

In the following code, it's possible to change a chart title or its location by themselves, but changing its title after changing its location doesn't work, as indicated by the standard error logging.

According to What is error code 0x800A01A8 coming out of Excel ActiveX call?, 0x800a01a8 means "Object Required.", but I assume #{chart.ole_obj_help.name} indicates that an object exists.

What's going wrong?

Code is followed by logging.

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)
    # http://rubyonwindows.blogspot.com/2008/06/automating-excel-creating-charts.html
    chart = @workbook.Charts.Add
    # Select a worksheet for source data
    worksheet = @workbook.Worksheets("Sheet1")
    # 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 raise an exception
      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
  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 ""
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
try_renaming_after_moving_the_same_chart: It didn't work
chart_location_confusion_replication.rb:30:in `method_missing': ChartTitle (WIN32OLERuntimeError)
    OLE error code:0 in <Unknown>
      <No Description>
    HRESULT error code:0x800a01a8

        from chart_location_confusion_replication.rb:30:in `change_chart_title'
        from chart_location_confusion_replication.rb:75:in `try_renaming_after_moving_the_same_chart'
        from chart_location_confusion_replication.rb:87

Edit: If I change the chart creation to the following:

  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

and add excel_outputter.write_to_file to the end of try_renaming_after_moving_the_same_chart and turn off show_everything_works_if_you_do_not_change_a_moved_chart, then I get

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!

but when I view it in Excel, the chart has the title change_chart_title works before you call move_chart, rather than Will not get here. However, the following VBA works:

Sub Tester3()
    Dim cht As Object

    Debug.Print "Start"
    Set cht = Sheet2.Shapes.AddChart.Chart
    Debug.Print TypeName(cht)       'Chart
    cht.SetSourceData Sheet1.Range("B4:C15")
    Debug.Print TypeName(cht)       'Chart
    cht.ChartTitle.Characters.Text = "Second title"
    cht.Location Where:=xlLocationAsObject, Name:="Sheet2"
    cht.ChartTitle.Characters.Text = "Third title"
    Debug.Print TypeName(cht)       'Chart
    Debug.Print cht.Name            'Sheet2 Chart 7
End Sub

Answer 1

If I recall correctly there are some differences between chart sheets and charts embedded on worksheets. It may be that those differences are breaking your "chart" reference such that it no longer points to the "same" object after the move. A bit of VBA to show the same thing:

Sub Tester()

    Dim cht As Object

    Set cht = ThisWorkbook.Charts.Add()
    cht.SetSourceData Sheet1.Range("B4:C15")
    Debug.Print TypeName(cht)       'Chart
    cht.Location Where:=xlLocationAsObject, Name:="Sheet1"
    Debug.Print TypeName(cht)       'Object
    Debug.Print cht.Name            'Error: object required
End Sub

Edit: moving an embedded chart to another sheet also doesn't work:

Sub Tester2()

    Dim cht As Object

    Set cht = Sheet1.Shapes.AddChart.Chart
    cht.SetSourceData Sheet1.Range("B4:C15")
    Debug.Print TypeName(cht)       'Chart

    cht.Location Where:=xlLocationAsObject, Name:="Sheet2"
    Debug.Print TypeName(cht)  'Chart
    Debug.Print cht.Name       'Error: Method 'Name' of object _chart failed

End Sub

Is there some reason why you need to create the chart as a chart sheet and don't create it directly on the worksheet?