linking a calc chart into a writer document

users
#1

Hi Folks,

Firstly, congrats on Libreoffice - its pretty darn good and working very
well for everything else I've tried doing with it.

I'm having weird problems trying to link a chart from calc spreadsheet into
a writer document. Despite trying several approaches I can't get it to link
- it just seems to be hard coding the data values.

The calc spreadsheet has many worksheets, and the chart exists on a
worksheet with various other items of data.

Selecting "copy" in calc then "paste special" in writer gives the options
to insert the chart as a jstar XML object or GDI metafile - this clearly
seems to just insert a graphical representation generated at the point of
paste so the values never update.

Selecting "copy" in calc, then "paste" in writer seems to *almost* insert
it as a link, however the data values in writer don't update when changed
in the calc file. Furthermore trying to edit the chart in writer is
possible, but the dialog to access the data ranges is missing - this I'm
guessing its somehow hard coded the data values.

I've setup the tools/options/general settings so that links are always
updated, and I have successfully connected calc and writer with OLE objects
for various data tables so its not completely uncooperative.

Any ideas as to how I might get the link to work appreciated!

Cheers,

#2

Hi David,

David Edwards wrote (04-01-12 21:07)

Firstly, congrats on Libreoffice - its pretty darn good and working very
well for everything else I've tried doing with it.

Glad to read that - thanks for sharing :slight_smile:

I'm having weird problems trying to link a chart from calc spreadsheet into
a writer document. Despite trying several approaches I can't get it to link
- it just seems to be hard coding the data values.

IIRC, the only way to do that is paste as DDE link.

Does that help sufficient?

Cheers,

#3

Hi Cor,

Thanks for the info.

I've still not managed to work out how to do this!

I had used OLE linking succesfully to insert other spreadsheets into writer
and they work fine (but doesn't OLE obsolete DDE?).

When I've selected the chart and paste special there is no DDE link option.

Thus I've tried anchoring the chart to a cell and copying that cell. Paste
special does then have a DDE option, but it just pastes a link to the empty
cell and doesn't include the chart.

I wonder if using DDE linking to link the actual cells into writer,
generate the table locally in writer would work - though I'd need a method
to "hide" the actual cells in writer. Is there a method to do this?

Thanks in advance!

#4

Hi David,

David Edwards wrote (05-01-12 00:56)

I've still not managed to work out how to do this!

Hmm, again I got in trouble because of reading to fast/only half.

When I've selected the chart and paste special there is no DDE link option.

My suggestion helps for linking data - not for charts.

Thus I've tried anchoring the chart to a cell and copying that cell. Paste
special does then have a DDE option, but it just pastes a link to the empty
cell and doesn't include the chart.

Ah, you use your creativity :slight_smile:

I wonder if using DDE linking to link the actual cells into writer,
generate the table locally in writer would work - though I'd need a method
to "hide" the actual cells in writer. Is there a method to do this?

Yes, menu Insert > Section (after you selected the area with the table) and set that to hidden.

Make sure there are at least two paragraphs between table and graph.
I hope that leads to usable and stable solution :wink:

Cheers,

#5

Hi :slight_smile:
In Writer

Insert - Object - Chart

seems to do something and then right-click on the chart and have a look at data-range or something.  In the Draw guide there is a chapter about working with Objects and maybe that might help?
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Draw_Guide

Someone else asked a similar question to this a few weeks ago and i don't think we were able to help him much either :frowning:
Apols and regards from
Tom :slight_smile:

#6

If your data layout comprises a normalized table (a flat list like a database
table) then Base can help.

Database...

[X] Connect to existing database
Type: Spreadsheet
Specify the spreadsheet file.
Save the database file which is just a configuration to read data from the
spreadsheet as if the spreadsheet were a database.
Go to the report section of the database file and create a report with a
chart. A report is a sophisticated Writer document linked to database data.

#7

To insert:
- Menu/Insert/Ole Object - Create from file, mark Link to file.
   Select the file.

To update:
- Menu/Edit/Link - update.

Or another way:
- Menu/Insert/Ole Object - Create new.
- Open the new object (double click), and paste the area from the spreadsheet including the data and the graph.
- Paste.
- Copy the data in original spreadsheet.
- Paste special - link.

The graph is from inserted object.
The data are linked to the original spreadsheet.

Miguel Ángel.

#8

Hi Cor,

Thanks - I've tried more things and have an idea of the problem now, but
its still not working.

Migel's suggestion of DDE was exactly what I'd said I'd tried and it didn't
work.

I've tried Cor's idea of DDE linking to the table and generating the chart
in writer instead.

This *almost* worked but doesn't update. At first it didn't work at all as
when creating the chart the table contents were interpreted as text (rather
than the underlying current format used in the calc document). I've turned
on number recognition and managed to change the format in the linked table
*before* creating the chart and it now understands the numbers so actually
draws something.

However, it appears that this translation from imported numbers to text
back to numbers is a 1 off process - the underlying table does not update
thru the link, I'm guessing because the text conversion has been done ;-(

It seems there is something weird going on - any other ideas for sorting
out this number link/formatting problem might sort it out!

Thanks in advance.

#9

Just managed to *almost* make this work.

I've formated the calc cells as text, linked these cells into writer via
DDE and formatted them as text.

I then inserted a chart using the writer cells. I have the link/auto-update
option set in tools/options and on the DDE links and I can see that the
table itself updates correctly.

However, the chart still *does not* update automatically. I can force an
update by manually selecting the chart and use "edit". At this point it
updates correctly.

Thus I have a workable solution - thanks for your help!

If anyone has an idea of how to remove the "edit" stage to force the update
it would be great.

#10

Hi David, Migel,

David Edwards wrote (05-01-12 13:43)

Migel's suggestion of DDE was exactly what I'd said I'd tried and it didn't
work.

I swiftly saw Migel's mail yesterday and that looked as a proper/clean solution to me.
If it does not work, would it be just to file a bug-report?

#11

Hi David,

David Edwards wrote (05-01-12 15:12)

Just managed to *almost* make this work.

Thanks for keeping us up to date :slight_smile:

However, the chart still *does not* update automatically. I can force an
update by manually selecting the chart and use "edit". At this point it
updates correctly.

Is the menu Edit > Links active and if so, does that help?

Regards,

#12

Maybe I didn't explain well the second option.

- (A) Menu/Insert/Ole Object - Create new **(spreadsheet object)**.
- Open the new object (double click), and paste the area from the original spreadsheet **including the data and the graph**.
- Paste in (A).
- Copy the data **area** in the external spreadsheet.
- Paste special - link, overwritten the data area in (A).

This link the graph to new spreadsheet object (A) in writer.
and the data in (A) to the external spreadsheet.

Regards
Miguel Ángel.

  * Inglés - detectado
  * Inglés
  * Español

  * Inglés
  * Español

  <javascript:void(0);>

#13

Hi Chaps,

Thanks for the support again!

Re. Cor's query about edit/link - yes it is active, and all the linked
cells used in *tables* appear and are shown as auto update.

The linked cells used in the hidden table *DO NOT APPEAR* in this dialog at
all ....

Re. Migel's more detailed suggestion:

*(A) Menu/Insert/Ole Object - Create new **(spreadsheet object)**.*

Yes

*- Open the new object (double click), and paste the area from the *
*original spreadsheet **including the data and the graph**. *

This *has* to be paste-special as the cells refer to the other spreadsheet
and convert to REF.
The Graph pastes in fine.

*- Paste in (A). *

Not sure what you mean here, I presume you mean select the chart within the
"new" OLE spreadsheet into the writer document.

This fails to draw the graph - it appears that the problem I suspected
earlier whereby the cells get converted into text and thus the values
aren't parsable as graph values is the problem.

When googling for details on anchoring a chart to a cell I found this
https://www.libreoffice.org/bugzilla/show_bug.cgi?id=39118

i.e. charts anchored to cells don't update problem, evidently fixed in 4.5.

Thus it looks like a solution will occur later in the year.

Thanks for all your help, I really appreciate it and will make a donation.

Cheers!

#14

Hi Chaps,

Thanks for the support again!

Re. Cor's query about edit/link - yes it is active, and all the linked
cells used in *tables* appear and are shown as auto update.

The linked cells used in the hidden table *DO NOT APPEAR* in this dialog at
all ....

Re. Migel's more detailed suggestion:

*(A) Menu/Insert/Ole Object - Create new **(spreadsheet object)**.*

Yes

*- Open the new object (double click), and paste the area from the *
*original spreadsheet **including the data and the graph**. *

This *has* to be paste-special as the cells refer to the other spreadsheet
and convert to REF.
The Graph pastes in fine.

*- Paste in (A). *

Sorry, this is a duplicate instruction not needed.

Not sure what you mean here, I presume you mean select the chart within the
"new" OLE spreadsheet into the writer document.

This fails to draw the graph - it appears that the problem I suspected
earlier whereby the cells get converted into text and thus the values
aren't parsable as graph values is the problem.

When googling for details on anchoring a chart to a cell I found this
https://www.libreoffice.org/bugzilla/show_bug.cgi?id=39118

i.e. charts anchored to cells don't update problem, evidently fixed in 4.5.

Thus it looks like a solution will occur later in the year.

Thanks for all your help, I really appreciate it and will make a donation.

Cheers!

- In writer Menu/Insert/Ole Object-Create new (spreadsheet object) (A).
- Copy the area from the original spreadsheet (B) including the data and the graph.
- Open the new object (A) (double click), and paste.
- Copy only the data area in (B).
- In (A) Paste special-link, overwritten the data area.

The graph in (A) is linked to data in (A)
and the data in (A) is linked to the external spreadsheet.
At least save once the external spreadsheet (B) is mandatory to get the link working.
The data are update when a double click in (A).

To get an updated data in (A) without save (B) before, introduce with Ctrl+Shift+Enter a DDE link overwritten the data area.
=DDE("soffice";"C:\YourPath\Untitled 2.ods";"Sheet1.A1:B4")
Always to get (A) updated, double click.

Regards
Miguel Ángel.

  * Inglés - detectado
  * Inglés
  * Español

  * Inglés
  * Español

  <javascript:void(0);>