July 28, 2005

Sharing files between OpenOffice.org and Microsoft Office

Author: Bruce Byfield

Even if you're the most dedicated OpenOffice.org (OOo) user in the world, sooner or later you'll be asked to share files with someone using Microsoft Office. Some free software advocates refuse outright, or suggest outputting to HTML, PDF, or RTF formats, but these aren't always options -- especially if your boss is the one doing the asking. However, with a few preparations and a sense of what works and what doesn't, you can usually share files with Microsoft Office users with a minimum of headaches on both sides. Here's how.

In OpenOffice.org, you can use File > Wizards > Document Converter to convert all the Microsoft Office files in a folder to OOo format. If your main concern is content and you can ignore minor formatting errors, this wizard may be all you need. However, you can get better results if you plan your file sharing.

In OpenOffice.org version 2.0, importing and exporting Microsoft Office files is much easier than in earlier versions. However, many features of OOo are completely or partly unsupported in Microsoft Office. Many of these unsupported features are simply dropped when a file is converted. Your success in exchanging files depends largely on knowing which features are supported.

Preparing for sharing files

The first step in sharing files is to standardize on an export filter. OpenOffice.org includes export filters for Microsoft Word 6.0, 95, 97/2000/XP, and 2003 XML. If possible, standardize on the 97/2000/XP filter. While not perfect, it seems the most trouble-free filter, despite not being the most current format.

Next, if formatting matters, check that the fonts in your file are available to both OpenOffice.org and Microsoft Word. If they're not, your conversion target program will reformat the document, and you can expect repagination and the destruction of complex designs. Microsoft Word, which reformats using Times Roman of the same size, can be especially destructive of format. These problems are aggravated by the fact that GNU/Linux systems typically have free fonts that are not installed on Windows by default. However, they occur whenever you share files, no matter what operating system or programs are involved.

You can sidestep this issue by using only standard fonts such as Times Roman or Helvetica. If OpenOffice.org is installed on all machines, you can also use the Bitstream Vera fonts that are installed with it. The only drawback to these choices is that they are used so often that they may create a bland impression. If graphic design is important to your work, then the only solutions are either to install the fonts everywhere necessary -- first checking the font licenses -- or to share files in PDF format.

If you use non-standard fonts, remember that just because font files have the same name doesn't mean that they are identical. For example, there are literally dozens of fonts named Garamond, all allegedly based on the designs of the Renaissance typographer of that name, few of which have much in common except the name.

Now adjust the settings within OpenOffice.org itself. First, select all the options in Tools > Options > Load/Save > Microsoft Office. These options improve the handling of OLE Objects -- but only if you are using Writer in a operating system that supports OLE Objects, such as Windows. Second, check that all the options in Load/Save > VBA Properties are selected. OpenOffice.org doesn't run Visual Basic scripts, but, when these options are selected, they are preserved for later use when a file is opened in OpenOffice.org. If you neglect these, the people with whom you exchange files will be justifiably peeved to find their macros have disappeared after you have opened their files.

Paying attention to these matters should take a lot of the misery out of sharing files. Even so, if you want formatting perfection, you'll need to tweak your files. That tweaking will be infinitely easier if, as a final preparation, you format in both OpenOffice.org and Microsoft Office using character and paragraph styles instead of manually. Styles can be saved in a template, so you'll only need to set them up once. More to the point, character and paragraph styles also transfer successfully between Writer and Word, so you can use the styles to speed up your tweaking of the results.

Preparing to share files between Writer and Word

>Before sharing text documents, open a blank Writer document and check the options available in OpenOffice.org at Tools > Options > OpenOffice.org Writer > Compatibility. Some of the options in this window are for compatibility with recent versions of StarOffice, the proprietary version of OpenOffice.org, but about half are for Microsoft Word compatibility. Several set OpenOffice.org to add spacing between lines and around objects as Microsoft Word does:

  • Add spacing between paragraphs and tables (in current document).
  • Do not add leading (extra space) between lines of text.
  • Add paragraph and table spacing at bottom of table cells.
  • Consider wrapping style when positioning objects.

If you are importing Microsoft Word files, then select "Add paragraph and table spacing at tops of pages (in current document)" as well. Whether importing or exporting, you should also select "Use printer metrics for document formatting" if all machines have access to the same model of printer or to any PostScript printer. The option provides a common standard independent of the software.

When you have set the options, click the Use as Default button to save your choices as the default settings. Some of these settings are only for the current document, so save the current document using File > Template > Save so that you don't have to adjust the settings each time you start a document that will be opened in Microsoft Office.

Sharing files between Writer and Word

With the options for file-sharing enabled, you can generally exchange text documents with few problems. The text itself usually translates with only minor problems. The difficulties are likely to be caused by inserted objects or page-level formatting.

Character and paragraph formatting, whether set manually or with styles, usually transfers easily. Provided the fonts used are available for each program, characteristics such as font size, effects, and positioning are trouble-free. Even rotation of characters and changes in font width transfer without difficulty. The same is true of basic paragraph characteristics, such as line spacing and tabs. OpenOffice.org's list styles are not supported in Microsoft Word, but paragraphs associated with a list style in Writer will use the same type of list in Word. The only difficulties with paragraphs arise with features unsupported by Microsoft Word, such as custom hyphenation, page breaks, and last lines of justified paragraphs. These items are dropped in Word in favor of its default settings. The result may be minor differences in line and page breaks -- usually amounting to no more than a single line, if that.

In earlier versions of OpenOffice.org, numbered and bulleted lists were a problem in conversion, mainly because Writer and Word used different default fonts for bullet lists. In version 2.0, these problems are greatly alleviated. Special characters used as bullets still cause problems, since OpenOffice.org uses Unicode characters while Windows uses ANSI characters, but even advanced options such as text before or after a bullet or number are now transferred cleanly. The greatest problem with lists seems to be the use of Tools > Outline Numbering, which prevents altogether your saving a file in Microsoft Word format.

Many fields are also trouble-free, including page numbers, cross-references, and tables of contents. In fact, even custom table of content entries, such as ones with the page number first, open successfully in Microsoft Word. Other fields, such as those based upon file attributes, such as word count, are converted to text, which might cause problems if the file is being passed back and forth for revision. Fields that do not work at all include all of those for conditional text, such as hidden text, hidden paragraphs, and input lists. Hidden text simply disappears in Microsoft Word, while hidden paragraphs are revealed and input lists are frozen on the current selection.

Inserted objects have mixed results when you share files. Many common objects transfer without problems. Hyperlinks transfer successfully, unless attached to a frame, and so do notes and revision changes.

Other objects have mixed results. The settings for graphics, including size, anchor, and alignment jump the gap smoothly except when Align as Character is set in Writer -- in which case, the graphic simply does not appear in Word. Complex table and border settings generally transfer smoothly, but a nested table does not survive when the file is opened in Word. Similarly, simple shapes made with the drawing tools carry over, but callouts or complicated diagrams may not. Footnotes are transferred, but generally cause repagination, so end notes seem a better alternative for a shared document.

A handful of objects do not convert at all. They include animations and embedded OpenOffice.org files. On GNU/Linux, OLE objects in an Microsoft Word document do not convert either, since -- despite the listing for OLE Objects under Insert > Object -- the operating system does not support them.

Other items that do not transfer successfully are mostly concerned with page layout. Since Microsoft Word has no sense of the typographic page, this failure is only to be expected. Margins for page styles are maintained, but the page styles themselves are lost. So are all except the first header and footer, and any borders or shadows given to them. Similarly, text frames are transferred, but without most of their settings. Sections fail even more seriously, being treated as page break markers, and dropping password protection and hidden settings. Both frames and sections with multiple columns often display only the first few lines.

As for master documents, they can be neither imported nor exported.

Sharing files between Calc and Excel

The main compatibility problem between Calc and Excel spreadsheets are functions. Basic functions, such as SUM, AVERAGE, and MAX generally cause no problems. However,with more advanced functions, check the online help before using them. Although Calc is designed to be highly compatible with Excel, both spreadsheets have functions that the other lacks. In a few cases, Calc may have arguments that Excel lacks. In other cases, Calc may have two similar functions: one for general use, and a second for Excel compatibility. For example, WEEKNUM calculates the week number of a given data based on the ISO 6801 standard, while WEEKNUM_ADD does the same calculation in an Excel-compatible format.

As in Writer, objects in Calc have a mixed record. Charts in particular may not survive translation. Page and cell styles don't survive, although the formatting of cells that use the styles generally translates faithfully.

Sharing files between Impress and PowerPoint

The PowerPoint filter seems far more reliable in OpenOffice.org 2.0 than in earlier versions. In particular, the frequent inability to transfer a background graphic in a master slide has been eliminated. Slide transitions are also exported more reliably, although you should experiment with them to check which ones don't work. However, animated GIFs created in Impress still do not open in PowerPoint. Similarly, instead of creating a complicated diagram directly in Impress, you will probably have more success doing the diagram in Draw, then exporting it into a common graphic format.

When importing from PowerPoint, you need to be aware that many of the options for sound cannot be used in Impress. Although version 2.0 supports a much vaster array of sound formats than earlier versions, Impress still lacks tools for recording sounds and narration, and for playing CD tracks with a slide. Sound in Impress remains limited to individual slides. Nor can Impress handle PowerPoint's Pack and Go or Package for CD, which bundles up a slide show and all its associated graphics and fonts for easy transportation between machines

Sharing Draw files

>Draw's native formats, .odg and .sxd, are not supported by Microsoft Office. Fortunately, Draw files export to most common graphic formats, including .png, .gif, and .jpeg. All these formats can be viewed in a Web browser or inserted in a blank Microsoft Office document. Keep the original, and export as needed.

Conclusion

This has not been an exhaustive list of what works and what doesn't when exchanging files with Microsoft Office. No such list exists. The online help does highlight potential problem areas, but the current Office does not take the changes in OpenOffice.org version 2.0 into account, and is unnecessarily paranoid.

Still, if there's a secret to sharing files between the two office applications, it comes down to three words: Keep it simple. The more basic the formatting, the more likely that the exchange of files will be trouble-free. Objects, page design, and any styles except paragraph and character styles are especially likely to cause problems.

Understand, too, that, except in the simplest cases, the process is unlikely to perfect. Sharing files has always been a weak side of office programs. Making import and export filters is too expensive for commercial companies, and often too unglamorous for free software developers. The situation isn't improved, either, by the fact that Microsoft Office formats are proprietary, often altered between versions, and require reverse engineering.

Under the circumstances, the fact that sharing files raises problems isn't surprising. Often, what's surprising is that you can share files at all.

Click Here!