Databases
StackOverflow2013
Postsdbo
POChanging a chart's properties breaks after changing its location
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChanging a chart's properties breaks after changing its location
    primarykey
    data
    text
    <p>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.</p> <p>According to <a href="https://stackoverflow.com/questions/1563406/what-is-error-code-0x800a01a8-coming-out-of-excel-activex-call">What is error code 0x800A01A8 coming out of Excel ActiveX call?</a>, 0x800a01a8 means "Object Required.", but I assume <code>#{chart.ole_obj_help.name}</code> indicates that an object exists.</p> <p>What's going wrong?</p> <p>Code is followed by logging.</p> <pre><code>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' =&gt; 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" =&gt; xlLocationAsObject, "Name" =&gt; 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 </code></pre> <p>produces</p> <pre><code>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 &lt;Unknown&gt; &lt;No Description&gt; 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 </code></pre> <p><strong>Edit:</strong> If I change the chart creation to the following:</p> <pre><code> 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' =&gt; 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 </code></pre> <p>and add <code>excel_outputter.write_to_file</code> to the end of <code>try_renaming_after_moving_the_same_chart</code> and turn off <code>show_everything_works_if_you_do_not_change_a_moved_chart</code>, then I get</p> <pre><code>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! </code></pre> <p>but when I view it in Excel, the chart has the title <code>change_chart_title works before you call move_chart</code>, rather than <code>Will not get here</code>. However, the following VBA works:</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAndrew Grimm
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI think your last VBA example works because it's not really moving the chart: you add it to Sheet2 then "move" it to the same sheet. If you try moving it to a different sheet it will error on the last line.
      singulars
      1. POChanging a chart's properties breaks after changing its location
      1. USTim Williams
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload