« Sorting Information in OpenOffice Writer Tables | Main | Microsoft Owns Your Tube Top (Repost) »

June 29, 2006


This is a very useful article, thank you. In fact, I maintain a FAQ for the OOo users where I work and this is one of the questions in it. I'm going to link to your article in the answer.

There is a related issue for which I can't find a good solution. Suppose I don't want to create a new table from scratch with the spreadsheet data, but that I already have a big table and just want to substitute the spreadsheet data for a few of the table's cells. Is there a way to do it?

Perhaps this is very specific, but I have already been asked about it twice.

Hi Gustavo

Hmm, interesting question. I've tried a few ways and the only way, kind of a hack, seems to be this:
- Go to the spreadsheet
- Copy the cells you want
- Go to the Writer document with the table and click somewhere in the normal text flow outside the table. (Or go to any Writer document.)
- Choose Edit > Paste Special and select Formatted Text.
- Copy the resulting data, now in a table
- Click in the upper left cell of the area in the table where you want to paste the cells
- Paste normally

So there's an extra step. It seems that pasting acts differently when the cells are pasted into a table rather than being pasted into plain text.

I'd like to know how one can dynamically link a OOOCalc cell's content to a OOOWriter document, and make sure that whenever the cell's content is changed in Calc, the related correspondent word in the Writer document is automatically updated ? I don't find any option to do this in OOO's "Paste Special" menu, whereas the equivalent "Paste Special" menu of M$'s WORD has an option named "Paste Special With Link" which does just what I described.

Thanks for replies.

P.S. Supressing the * in my mail adress will allow human senders to mail-contact me. This is done to prevent bots from spamming me. Cheers

Much obliged for this article, especially since the book's been delayed. I do have to cry out into the wilderness and observe that the default behavior is kind of horrible for legal users creating financial affidavits.

Legal documents can be subject to all sorts of specific margin and formatting requirements, require a text caption at top, and usually a signature or certification block at the bottom -- things best done in a word processor (go look at the top of a PDF from Groklaw, then try to lay that out in Calc fields without losing hair).

Unfortunately, as far as I can tell, getting an OLE spreadsheet object to obey page margins and break across pages just isn't happening. These workarounds are much better than nothing, but it's still an annoyance.

(Of course, the office I'm working in did just do these as text documents with a desk calculator, or within word processor tables, before I came along and decided to try to learn to use software properly.)

[e-mailers, remove the -no-spam-.]

Hi Accessdenied,

Sorry for the delay, my notification is broken. You can Edit > Paste Special from a spreadsheet into a Writer document, choose DDE Link, and then when you update the spreadsheet, the Writer doc is updated too. This is a great way of putting spreadsheets into master docs since master docs can only handle writer docs.


Hi Joe,

And let's not even start talking about pleading papers, eh? :(

You might try creating what you need with a graphic and putting the graphic in the page style, using the Background tab. Frames might work too. (Insert > Frame.)

You can link a section or a frame to a text fle so you might want to insert a frame at the top that sucks in the content from a particular file with the canned text you need.



quite often i would like to paste a range of cells from an HTML table in a browser into a spreadsheet, maintaining the tabular layout. this always fails (everything gets pasted onto one spreadsheet row).

is there a way to do this? ironically it's one of the most frequent uses i have for a spreadsheet.

Hi Mike,

That's odd; pasting from tables into a spreadsheet works fine for me. Do you click in just one cell in the spreadsheet when you paste?

There's another approach. Go to the spreadsheet, choose Insert > Link to External Data, and select the path to the HTML file. (I tried it with a URL to a web site but it didn't seem to work.) Then in the area below, select the table you want to insert. It comes into the spreadsheet nicely.

I'll have to blog on this; it definitely goes with the other info about transferring tables into text into spreadsheets, etc.


I just got back to this problem today, and ended up pasting the sheet into Writer as an HTML table since, oddly enough, that preserved the most formatting. (Or at least obeyed the formatting in the document, rather than popping everything into some Default style of Times New Roman. DDE can't handle merged cells, OLE doesn't break across pages, life is fun, etc...)

"You can link a section or a frame to a text fle so you might want to insert a frame at the top that sucks in the content from a particular file with the canned text you need."

... I might be braindead today, but in Calc I only see an "Insert -> Floating Frame" item, and that's decidedly nondeterministic in behavior, especially when Page Previewed. (This with whatever stale version of OO.o Ubuntu 6.06 LTS provides.)

In theory the image thing could work, or I could just figure out exactly how to size cells to hold and wrap the text, but it's certainly easier to preview and conform to margin requirements in Writer.

[I'm really just posting this as a record of one user's struggle, for any developers scraping across it.]

Hi, I found that there is a bug for using the DDE link method.
I am using the Chinese WinXP OS. If the source file (the spreadsheet document) is placed at the folder which is named with some Chinese characters, it can't link the spreadsheet's content to the Writer's document correctly.

Thanks for this info but I'm not able to delete a DDE linked table from my text file. How can we do that?

DDE Link doesn't apparently carry over formatting such as colors, text attributes, etc. Any solution for this?

I read your stuff quite a bit by the way - good stuff.

Hi. To delete a DDE linked table go to "edit -> links". Then select the link that is used on your table and break it. Then I used "table -> delete" from the menu.

Good luck with your writings!

how can set margins while print preview in openoffice
spreadsheet ???? plsssssssssss help me ?????

how can set margins while print preview in openoffice
spreadsheet ???? plsssssssssss help me ?????

Hi Sagar,

In Print Preview, there's a Format Page button. Click that, set margins in the Page tab, then click OK and you'll be back in Preview with the changes you made having taken effect.


Hi, I have a question about DDE links.

I am trying to link some cells from a calc spreadsheet to a writer document, both in the same directory. These documents are templates which I'd like to use in many projects, so it would be great that when I change the location of the files (keeping them in the same directory) the links keep on working. I'm trying to use DDE links with relative paths, but I don't know how. Is there any way to achieve this?.


Sorry, not that I know of. Maybe have the calc doc stationary and the writer doc move around?


Thanks Solveig for your quick answer. In the meantime I've found that this is filed as an issue (issue 49362), and that there seems to be a workaround. I copy from openoffice.org:
"First of all, relative paths do still work, but the documentation doesn't cover
it well. I hope it will be updated soon!

If you want to use relative paths, in the DDE link field write this:
"file:linkedfile.ods" if the 'linkedfile.ods' is in the same directory
"file:subdir/linkedfile.ods" if the 'linkedfile.ods' is in a subdir called 'subdir'
"file:../linkedfile.ods" if the 'linkedfile.ods' is in the root directory

Please note: this works on both, writer (in the Links window) and calc (as the
=DDE() argument). I've tested this on Win XP, but the fact it uses slashes
instead of backslashes makes me think this works on linux too. But I can't
confirm it.

But be careful! If you update the links with the "file:" path (Modify->Links),
when you try to "Modify->Paste Special->DDE Link", writer will crash! You can
still add DDE links using "Ctrl+F2"."

Nice! Thanks, pofufo.


Thansk for all your articles, Solveig.

Here's a question. How would you split a spreadsheet into multiple tables for presentation in Writer?

I like the simple Paste method because it preserves the Calc formatting. But maybe a live link isn't possible that way. What I'd like is a live link with the ability to show selections from the spreadsheet in multiple split tables in Writer.


I was hoping for some help me with creating links in Writer from Calc as the source.

I am trying to transition from MS Office and with the work that I do I tend to have a lot of links from an excel spread sheet into a word document. Mainly the links are for "charts" (multiple cells in excel) that would retain their formatting, as well as for "fields" (single cell) that get incorporated into the word document as text as part of say a sentence.

I have been trying to do the same with OpenOffice by using the insert as DDE function, however, the formating for a "chart" is not retained from the spread sheet - any hidden rows or columns appear in the text document, no shading, etc.. I can keep the fomatting by using other paste methods, however, then the link to the spread sheet is lost, so any changes to the spread sheet do not update to the text document.

The second problem that I was hoping you might be able to shed some light on is pasting a single cell as text - with the DDE function, the link shows up as a single celled table in the Writer document and not just as text. Again, I was hoping to also retain the ability to update the text document as changes are made in the spread sheet.

Any help would be greatly appreciated.

Thank you,

When I do the bottom option -- Paste Special as RTF -- I don't get a Writer table. Instead, it pastes all the spreadsheet contents as one column of text

Is there also a possibility for a DDE link with a graph, directly?
I know it is possible to put the spreadsheet, like you said above and then made a graph from it. But I don't want all the extra spreadsheet in my document. Could you help me?


The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog