Ask Your Question
0

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

asked 2018-11-22 05:49:21 +0100

JimM gravatar image

updated 2018-12-03 17:31:19 +0100

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.

edit retag flag offensive close merge delete

Comments

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

Lupp gravatar imageLupp ( 2018-11-22 11:27:08 +0100 )edit

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"...

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-22 12:26:32 +0100 )edit

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 gravatar imageJimM ( 2018-12-03 15:31:02 +0100 )edit

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.

JimM gravatar imageJimM ( 2018-12-03 16:05:12 +0100 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2018-12-05 08:38:05 +0100

JimM gravatar image

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.

edit flag offensive delete link more
0

answered 2018-12-05 04:04:21 +0100

Ratslinger gravatar image

updated 2018-12-05 05:05:33 +0100

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.

edit flag offensive delete link more
0

answered 2018-11-22 12:03:14 +0100

Lupp gravatar image

updated 2018-11-22 12:41:08 +0100

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.

edit flag offensive delete link more
0

answered 2018-11-22 10:56:56 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-22 05:49:21 +0100

Seen: 82 times

Last updated: Dec 05