Create an updatable link from within Writer to a Calc Chart?

[JimM]

Because my original question seems to have been unclear to experienced users, I am once again asking it, but with revisions rendering it more clear. Thank you to the people who responded the first time.

Context:

1. I have a Writer document;
2. I have a Calc document (spreadsheet);
3. Within the Calc document is a chart;
4. The chart is created from data in that same spreadsheet.

Desired outcome:

1. Insert into the Writer document the chart in the Calc document;
2. The inserted chart is to possess two qualities:
   a. It is to be linked to the Calc document;
   b. Updates made in the Calc document appear also in the Writer document.

Process outlined in LibreOffice user guides:

1. Perform these steps in Writer:
   Insert->Object->OLE object->Create from file->Link to file->Search->(select target file)->Open->OK

Discussion:
At no step in this process is a Calc chart defined as an entity. At no point is such an entity identified as an OLE candidate to be inserted, linked, and possessed of the quality of updatablility in a Writer document. The Writer Insert ->…->…->…-> OK process selects a file. At no point in the Writer Insert process are there instuctions or options allowing selection of an entity (the Calc chart) in the Calc document.

Question:
What do I need to do so that the Writer Insert → link to file → Search → OK series of commands results in:

1. A Calc chart in a Writer document that
   a. Is identifiable from within Writer using the Insert Object…OK command series as an OLE candidate;
   b. Is selectable using the Writer Insert Object…OK command series;
   c. Is inked, once inserted in the Writer documenrt, to the source Calc document; and
   d. Can be updated in Calc so that the updates also appear in Writer.

In other words, how do I insert a defined Calc entity such as a chart into a Writer document as a linked, updatable OLE object?

I hope the question is now clear.

Thanks for your insights and instructions.

[Lupp]

Please don’t create 5-line-monster-subjects.
Concise clear subject, then thoroughly explaining content.

[mikekaganski]

The question seems to be as simple as:

“Is it possible to create a link from a presentation slide to a chart in Calc, so that it would update dynamically as Calc data gets modified”…

[JimM]

Lupp is right. First time users such as I was ought to be more careful to differentiate between the question title area and the question text area. A 5-line-monster-subject is sure to irritate. My apologies for the error.

[JimM]

Mr. Kaganski is correct in all but one detail, although it may be unimportant. Writer, not Impress, is the module I want to use to create the updatable link to the object, which here I define as an existing Calc chart in an existing Calc spreadsheet. Because my description of the issue seems not to have been clear, I shall restate the question and, of course, avoid a 5-line-monster-subject. Thank you for responding, Mr. Kaganski.

[Lupp]

As far as I can understand you are not talking of named ranges but of named objects.
What do you expect a “linked OLE object” to be or to be able of?

Anyway it will only be an O​bject L​inked to a software component of LibO and E​mbedded into a document.

Any chart gets a name like “Object 1” automatically when it is created. It is accessible under this name e.g. inside the Calc document by the API via the .Charts property. Manipulating this name is useless.

Each chart is also inserted into the respective DrawPage for rendering/viewing purposes using a special kind of shape. It’s only this shape you can set and edit the name of via the UI.

If you copy the chart from a Calc sheet and paste it into a slide (or elsewhere) the linkage to its data ranges inside the Calc sheets is broken. The chart object then contains the data itself in special tables. When a chart is pasted also probable naming conflicts must automatically be resolved. I don’t think there is still any rudiment then of a name you gave in advance of the Copy/Paste.

I do not know a way to retain or to re-establish links to the now external data. If you find one, please report here.
(I wouldn’t actually expect there is one.)

To be able to update a chart shown in a slide (or a Writer document) I would need to link to the Calc document, and to adjust the OLE object then to only show the chart. I can update then using the ‘Edit Links to External Data’ tool. Automatic update “every … seconds” should also be possible, but I would dissuade of.

[JimM]

After some months without a need to re-visit the original subject, I now find Lupp’s response from 11/22/18.

The critical fact Lupp mentions is: “If you copy the chart from a Calc sheet and paste it into a slide (or elsewhere) the linkage to its data ranges inside the Calc sheets is broken.”

Links to original data are what make creating an automatically updatable OLE object in an application other than Calc possible. Links, once broken, are no longer links. Having no live links between the data in the original and the OLE object in the destination means automatic updates cannot happen. (Very simple and reasonable.)

Perhaps people who can do such things would consider coding a solution? I have no idea whether this is a simple or complex task.

Thank you, Lupp,for the information.

[Lupp]

(Please don’t use very regional date formats in international forums.)
What I mentioned as “broken link” in fact is no link at all. As anything needing a graphiocal representation, viewed from th UI a chart is accessible via a Shape object “containing” (in the sense of giving access to again) everything else. Everything? No. There are no properties like DataRanges. The data always are contained as a table on this level. There simply is no link, and this fact is what allows for copy/paste goint to a different document model.
Only for charts inserted into a spreadsheet there is created (if needed) an additional Chart object (without a visible representation and directly accessible neither from the UI nor from the Shape object) managing the connection of the DataTable to associated cell ranges.
Only very recent versions of LibO Calc offer the re-establishing of such a connection if a chart shape coming from a different model was pasted in.

[Lupp]

The above is not “knowledge” based on any specification or “textbook” I would know, but the result of some experimental research I did in pursuit of your request and also for personal old interest. The results were recently also exemplified, and by that superficially documented in a demo made with Calc. It’s very raw, but if you notify me of your interest, I will post it.
(By the way: Someone having posted a question, and by that probably caused real work done by others always has a reason to visit the thread when he (f/m) is notified of a contribution.)

Lupp, 2019-03-25 09:55 UTC

[JimM]

I thank Lupp for the clarification regarding a broken “link” between a Calc chart and its copy in a Writer document. Contrary to my reading of the clarification, no link is established, ever. Therefore, what never existed cannot be broken. The object copied to the Writer document is independent of its source, and cannot be updated by linking from within the originating source. Whether a link is broken is immaterial.

I am unsure what Lupp means about “11/22/18” as a regional date format. The last line of Lupp’s response displays: 1. an icon, 2. the word “Lupp”, and 3. “( Mar 25 '19 )”.

Is this date format a preferred one for international audiences?

In the line immediately above the sequence described is: “Lupp, 2019-03-25 09:55 UTC”. I am willing to be incorrect, but this looks like an auto-generated, author/date/time stamp, not the typing of a person.

Thank you again for the further clarification in your latest response, Lupp. The actual circumstance is more clear.

[Lupp]

Just about date formats once again:
“Mar 25 '19” is an absurd format invented by someone for use on this askbot side. Just a joke, probably.
“11/22/18” is supposed to mean “2018-11-22” only in a few regions following US custom. It’s bad for many reasons.
The globally understandable, unambiguous date format YYYY-MM-DD is specified by ISO in norm 8601. It is the only format recognized by Calc under any locale, and the only well known one even functional (and sortable) if communicated as text.

Concerning Chart in Writer docment, I think it is clear now that there cannot be established a link to data from a Calc sheet. Even in spreadsheets the link never is part of the Chart’s OLE object, but established by a specific invisible interface object only existing in Calc and creating a visible representation only as long as the wizard or an editing dialog is running.

[catbill]

Lupp, I would be interested in your demo. Have you posted it somewhere?

[Lupp]

Coming back to the thread with a few days delay:
Unfortunately I can’t find the mentioned example any longer.
Concerning the generalized question how to create a chart based on data from a spreadsheet document and to make that chart availabel and updatable in Writer-/Draw-/Impress-documents, there shoud probably be a tutorial regarding the most recent versions.

[Ratslinger]

Hello,

Having worked a bit with charts in the past (Charts in Base forms and Inserting Chart using pyuno), about a year ago and in conjunction with the Python chart in Writer task, I worked some on charts in Writer in general.

I can start off be saying, in agreement with others, that there is no method I have seen to ‘click, click, click, OK’ or copy/paste anything to get what you are looking for.

I did dig out the tasks I had put together but remembered I had stopped on the documentation as it would easily have taken 20-50 pages just to put down some of my initial thoughts. I also stopped any further development since it appeared there is not a big calling for this.

The process in some ways is simple - place (or copy) a chart onto document. For the data, place an empty OLE spreadsheet. Create a link (Sheet->Link to External Data...) for updates. The somewhat difficult part is explaining the necessary macro code. It’s not a lot but can vary depending upon what is being done.

Have pieced together a bit of what I had previously done and posted it below. This particular sample uses a listener (macro) to know when data has been changed in the Calc file. As was already mentioned, automatic updates are NOT the way to go. They will cause havoc with trying to work with the document. I have another method which does not use a listener but still requires macro coding. The code is mainly to move the data from the updated OLE object to the chart itself. The sample is a simple one.

Diagrams in the document are not identical to sample as the sample includes some minor enhancements.

Instructions ------ LinkedChart.odt

Calc file ----------- DataForWriter.ods

Writer file --------- TestLinkedChart.odt

The Write macro UpdateChart is where the data is moved and has a few comments as to where this is being done.

The other sample I had mentioned is more elaborate allowing different data to be charted based upon radio button selection. It also includes a data backup OLE object so the document can be given out without the chart and it will still operate with the saved data.

One final note, the OLE sheet object can be hidden behind the chart so it is out of view. It is currently not hidden for accessibility.

(Still have reservations about posting - has not been thoroughly tested recently)

Edit:

Decided if going this far… well here is the other sample: TestLinkedChartExtra.odt

This sample has two OLE sheets. You can see one near the chart top & the other at the bottom. Only the top sheet needs a link and it is not necessary to have this ‘updated every x…’. Use the same sample Calc file.

[mariosv]

To give a name:

• Right click out the chart, right click on the chart, select name.

To save as chart:

• Double click the chart like to edit.
• Menu/File/Save as/ chart odc type it’s selected for save.
• Data are save with the chart file not to the spreadsheet cells.

[JimM]

Thanks to Ratslinger for a most complete and thoughtful answer to the question. Given the opportunity of time to do so, I will attempt to follow the instructions and see what happens.

I can add here that the type of OLE linking that I hoped to achieve is a process that I followed for years when writing journal articles, providing budget summaries, and preparing annual proposals in a public health setting that required analyzing and subsequently explaining large data sets. Parenthetically, these set were more than 1,100,000 records, each one of which contained more than 1,500 data items. Whether such sets qualify as large ones in other kinds of work I do not know. Although sometimes cumbersome, the process in general was relatively straightforward to accomplish using Word/Excel/PowerPoint. I had anticipated that LibreOffice, which in many respects outshines the MS suite, would have a similar capability. I suggest that people who can code this as a utility would do a service to people who cannot or that the subject receive attention in future editions of LO documentation.

Thank you again, Ratslinger.

[2001herne]

Technically more of a hacky workaround, but you could always put the chart on a second sheet of the calc spreadsheet.
Then, when you insert the OLE link to the spreadsheet, just switch over to the sheet that has the chart and croll around until it looks how you want.