OpenOffice.org Training, Tips, and Ideas

How to take a spreadsheet that has firstname, lastname, and initial all in the same column, and turn it into two columns

Sometimes your data isn't as broken down, as granular, as you'd like.

Name

Hanson,John B

Herman,Jill

Jenson,Jim J

What you typically want, for the most power over your data, is this.

Lastname	Firstname
Hanson	John B
Herman	Jill
Jenson	Jim J

Here's how to split it automatically, not manually.

First, you need to take out that space between the comma and the first name. Select all the cells in that name column.

Choose Edit > Find and Replace. Type a comma immediately followed by a space in the Search For field and just type a comma in the Replace With field. Click More and select Current Selection Only.

Then click Replace All.

And you'll see the data looks like this.

 

Now you're ready to split it at the comma. First, be sure that you have an empty column to the right of the Name column, to put the first name and initial into. Then click on the column header to select the whole column.

Choose Data > Text to Columns.

Select the comma, but nothing else. You'll see a preview of how it will be split.

Then click OK and you'll see the actual data split.

Doing an email mail merge without most of the overhead of the OpenOffice Mail Merge wizard

I'm afraid the Tools > Mail Merge Wizard has never been one of my favorites. Too complicated.

I always train people to just "roll their own" when making a mail merge document.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

Everything you can do in the mail merge wizard, you can pretty much do in the roll-your-own approach, especially since you can now print all documents to a single file and then open that and customize it as you like, before printing on paper.

Printing

and viewing/editing the file output. (Click to see the bigger version; you'll see different values in the two letters.)

BUT

One thing you can't do in the roll-your-own approach is do an email mail merge.

Do you really want to go through the complexity and muscle aches of using the Mail Merge Wizard? No. And you don't have to. You're going to mix and match.

Step 1. Create your email the way you want it with the roll-your-own approach.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

Save it. Keep it open.

Step 2: Set up email configuration. In Writer, choose Tools > Options > OpenOffice.org Writer > Email. This setup worked for me. Key settings are smtp.comcast.net and port 587. You just need to do this once. If you have security on your email, like requiring a password to send, you'll have to click the Server Authentication button and enter additional information.

Step 3: Choose Tools > Mail Merge Wizard. Choose Current Document, or else browse to your document, and click Next.

Choose Email and click Next.

Select the database you're using and the table. Click OK and click Next.

Keep clicking next til you're here. Fill it in by selecting the field from the database that has the emails in it, and anything else you want.  Click Send Documents.

You'll see the progress window (in this test I only sent two emails).

 

And you're done!

The received email looks like this. Note that I sent it in email format, but with the extra carriage returns I put between the lines in Writer, it looks a little spacey here. You'll want to experiment with and adjust how you format the original Writer documents and what format you send in.

Sorting mixed 5- and 9-digit zip codes in OpenOffice.org Calc or Microsoft Excel

Every advance leads to benefits and to problems.

The 9-digit zip code is great for delivery accuracy. But they aren't required. So you have mixed 5-digits and 9-digits.

Now just try sorting your address info by zip code when there's mixed 5s and 9s. Here's what you get when you sort a standard address list by zip code.  It's like cattle and sheep, they don't mix.

What do you do?

There are three things you can do:
1 - Enforce a 9-digit zip. Everyone without four digits gets -0000 whether they want it or not.
2 - Put a ' in front of every zip code. It doesn't print but it forces the zip code to think of itself as text. (This also helps with not losing leading zeroes.)
3 - Split the column into two, so you have the zip in one column and the four-digit extension in the other. This is kind of like the forcing-9-digit solution.

The explanations follow but if you want to root around in an example spreadsheet, here's a spreadsheet with options 2 and 3.

Solution 1
Solution 1 is self-explanatory.

Solution 2
Just type a '   a regular apostrophe on the keyboard, to the left of the first character of every zip code. It doesn't show but it forces text format.

See? The ' is there in the entry field but it doesn't show in the spreadsheet cell.

Typing ' into thousands of cells takes a while. So you can search and replace. There might be a better way to do this but this at least doesn't suck.

Click in the Zip Code heading in your spreadsheet, then  choose Edit > Find and Replace. Click More Options and fill out the window as shown, searching for ^0, caret zero, and replacing with '0, apostrophe zero.

You'll need to do this once for 0, then for 1, then for 2, and so on. (I've tried to figure out a faster way, plus submit any suggestions.) 

Either replace one at a time if you're cautious, or go nuts and replace all. You might want to select the whole column of zip codes, too, and select the Current Selection Only checkbox.

When you're done, and when you sort that data, the zip codes sort correctly.

Solution 3

You can split your data into two cells with the LEFT and RIGHT functions. I'm throwing in IF too because sometimes you'll want the right-hand four digits (the extension) and sometimes you'll want 0000. (Or just leave it blank, whatever you want to do.)

This is what I want to achieve.

And this is how I get it. The formula for the first column, where I extract the first five digits, is simple.

The right-hand side is a little more complicated since you're dealing with variable-length zip codes. But basically you're saying if the zip code is just five digits, then create a new 4-digit extension, "-0000" (or just "0000" depending on how you want to deal with the dash). And then if it isn't just five digits, then you want to see the right-hand five digits of the zip code (including the dash) or the right-hand four digits (if you want to leave out the dash and put it in manually somehow).

Then you just drag down those formulas to all the zip code cells.

If you want to turn those columns into normal text, just copy them, choose Edit > Paste Special, choose to NOT paste formulas, and click OK.

The pasted results are nothing but numbers.

Now when you sort, you just need to be sure to do it by two levels, first by the main zip code, then by the extension. BE SURE that you set the Ascending or Descending the same for both.

And you get your results, sorted correctly.

Here's a spreadsheet with options 2 and 3.

The OpenOffice.org Web Wizard, for mass-converting docs to HTML or PDF

I wrote this article  for TechTarget about the fabulous Web Wizard and its uses for mass PDF conversion and quick web publishing of existing documents. This is a "classic" post but it's a great feature that bears re-posting about.

Expanded List of Tips for Tweaking Word Documents or WP Documents That Don't Look Perfect in OpenOffice Writer

I realized that I don't really have all the tips for compatibility between OpenOffice.org Writer and Microsoft Word in one spot. Here we go. These aren't all of the things you could ever try but they're my classics.

• Before converting the document, see what you can do about creating it well in a way that will convert well. If you create documents well, they'll convert well.
• If people are having problems with your document, find out if they really need to edit the document at all. If they just need to print it or have it on hand, then use File > Send > Document as PDF and all formatting problems are moot. Ditto if the problem is on your end -- if you don't need to edit the document you've received, then ask for a PDF version.
• Don't overlook the idea, with legacy documents, of A) keeping a couple licenses of Word around so you don't have to bother converting 0r B) contracting out the conversion work.
• Check the page formatting. Choose Format > Page and adjust the page size, margins, and page orientation.  Make the page margins smaller  if you're having trouble fitting everything onto the right number of pages.
• If text formatting is decidedly odd, select and choose Format > Default to remove formatting and start over.
• Change the font and/or font size. Select the text and choose a different font and font size from the dropdown lists at the top left of the toolbar.
• If graphics are behaving oddly, right-click on each and choose Anchor > To Page. Then reposition the graphic. You might also want to slightly shrink the graphic so it will fit better, or increase the size.
• For lists, select the list and click the numbering or list icon to turn off all numbering or bullets. Then reapply the list or bullets by clicking the same icon again.

• Adjust the default tabs. Choose Tools > Options > OpenOffice.org Writer > General. Set the tabs slightly smaller or slightly larger and check the effect.
• Adjust the default fonts. Choose Tools > Options > OpenOffice.org Writer > Basic Fonts (Western). Specify the fonts and font sizes that fit best in your documents.

• Check the fonts that are used with your printer and operating system. You might want to set up font substitution. Choose Tools > Options > General > Fonts, and use the online help to apply the replacement table. Replace fonts you can’t use with fonts you can.
• Choose Tools > Options > OpenOffice.org Writer > Compatibility. If the printer metrics option at the top isn’t on, select it. Try changing the other settings in the window and see if those items help.

If you find that you have a set of formatting changes that works well going to Word and back again, make that your default template. Then every time you create a new Writer document, it will have those attributes.

• Create a document with the correct formatting attributes.
• Choose File > Templates > Save.
• Save the template in My Templates and call it Conversion.
• Click OK.
• Choose File > Templates > Organizer.
• In the left-hand pane, expand My Templates and select the Conversion template.
• Right-click on that template and choose  Default Template.

• If you want to go back to the normal default template later, open the Organizer again, right click, and choose Reset Default Template > Text Document.

Checking for broken links when you're using Edit > Paste Special, Link, in OpenOffice

Paste Special is a great feature. Copy something, then go to a different spreadsheet sheet, or a different OpenOffice.org, and choose Edit > Paste Special. (Or Ctrl Shift V.) You get these options.

If you're pasting a spreadsheet to a spreadsheet

If you're pasting a spreadsheet to a text doc

If you're pasting a text doc to a text doc, or a text doc to a spreadsheet

 

If you select the Link option in spreadsheet-to-spreadsheet, or the DDE Link option when pasting any other time, you get a connection between the pasted from doc and the pasted into doc. When what you originally copied changes, then the pasted version of it changes, too.

This continues to work unless somebody deletes, renames, or otherwise messes with the original file you copied.

Unfortunately, if this happens, there's no big red flag waving to tell you. The data is still there; the link is just lost.

Here's how to check to see if the link is still working. Choose Edit > Links.

If you see links listed there, they're working and nobody has messed up the name or location of the original file.

If the the Links option is dimmed, or if a link to the file you're checking on isn't listed, then the link is broken and you need to do Paste Special, Link again to make the pasted data update when the original data does.

TechTarget Article: Numbered and Bulleted Lists in OpenOffice Writer

I've written a fairly comprehensive article for techtarget on lists.

See also this post on how to do numbering with fields. Fields are a more reliable but more complex approach.

---

---

How to make sure that your graphics are embedded (not linked) in your documents, especially for templates

When you add a graphic to a document, you can either plop it straight in so it's stored in the document, or you can link the graphic so that the document just points to where the graphic is stored.

It looks the same either way, but here's the thing. When you email your document to someone, or post your template in a network directory, what happens to that link pointing to the graphic?

The link points back to your directory at home\documentdrafts\2008\graphics\teamphoto.gif  or whatever the path is. And your cousin in Phoenix or the other people on your team can't get to that graphic.

So what you want to do, typically, when you're sending documents or templates to other people that those people need to work with, is to make sure that your graphics are embedded in your document.

NOTE: If you're doing large books or other documents where there are significant benefits to just linking to graphics, or if you have really big graphics of a few hundred KB or more, think hard before doing only embedded graphics. You'll have some issues, including really really big documents. Consider working with the documents only on the network so that the graphics are there on the network too and you don't have path issues. You might want to link as you work with the document, then if necessary break the links (see the last section here) or even better, make a PDF, before distributing the document.

How to Insert Graphics in Documents so They're Not Linked

When you drag a graphic from the Gallery (Tools > Gallery) into your document, it's automatically embedded. But when you choose Insert > Picture > From File, then you can choose to link or to not. If you want the graphic embedded, then don't select Link.

How to Add Graphics to the Background of Headers, Footers, or Pages So They're Not Linked

You can just click in a header or footer and choose Insert > Picture > From File. But you can also set up headers, footers, and pages with a graphic in the background.

Choose Format > Page.

Click the Header, Footer, or Background tab.

For Headers or Footers click the More.

Then you'll see this window. Select Graphic then click Browse. Find the graphic. Again, just be sure you don't click the Link checkbox which in this case is next to the Browse button.

 

How to Un-Link (Embed) Graphics When They're Already In Your Document

Let's say you've got a document chock full of linked documents and you reallllly don't want to re-insert them. It's easy to fix; just break the link and the graphics will be embedded.

Under the Edit menu, look at Links. If it's dimmed as shown, then you don't have any linked graphics and you're good.

If it's not dimmed, then choose Edit > Links. In the Edit Links window, just select the graphics listed and choose Break Link. The graphics stay, but now they're embedded and you can mail the document wherever you want or store it in another location.

(You could also select a graphic link and choose Modify to change where it's pointing to.)

---

---

My article on transitioning to OpenOffice.org from Microsoft Office

I really thought that I had posted this article, but I have not seen it in a quick page through.

http://www.fanaticattack.com/2008/switching-office-suites-from-microsoft-office-to-openofficeorg.html

It is a big, big article with most of what I recommend about setting up and switching, with the primary focus on individual users. But it applies to transitioning groups, as well.

See also this blog post, which has some really specific info about how to distribute clip art to many users on a network.

---

---

Grammar checker addon for OpenOffice Writer

There isn't a built-in grammar checker. If you want to add one, here's a grammar checker extension, one of the many on the extensions page for OpenOffice.org.

Once you download it, choose Tools > Extension Manager to install it.

---

---

Fun with OpenOffice Draw: Creating Text Boxes, Converting Text to 3D, and Formatting 3D Text

It's been a while since I sat down with some big sheets of paper and my crayons, and just colored. (I had that 64-color set of crayons with the extra-glamorous sharpener on the back....got it for Christmas my sixth birthday, I think, and I LOVED it. Sky Blue was my favorite.)

What I do these days, though, although the old set of crayons is actually on my shelf of cute collectibles along with my Star Trek popup book and a picture my parents the day they met, is to waste many fun hours with Draw.

One of the hidden but dramatic and fun features in Draw is the ability to create and twiddle around and format text in 3D.

Creating the text box

Find the text tool on the Drawing toolbar.

Click it, then move your mouse to the work area and draw a text box.

Immediately type in the text box

Converting the text box to 3D

Click on the text box where the border is, so that you get the green handles as shown. Then right-click on the border between or on the green handles and choose Convert to 3D.

The text will be converted.

Apply a lighter color if it's black so that you can see it better.

Modify the text by dragging one of its handles, so that the proportions are better.

To rotate the text in 3D, click in the middle of the text once; you'll see round red handles as shown and the mouse will change when you position it over a handle.

Drag a handle to move the text in 3D.

Click off the text, then once on again if you want to get the green handles back so that you can resize or re-proportion the text.

Applying standard fills

You can apply colors, but also gradients, hatchings, and bitmaps to the text. Think of it as a shape now, not text (you can't retype it at this point).

Here's a gradient.

Here's a bitmap.

Applying official 3D formatting

To apply 3D formatting, generally you should have a plain color applied, though there's no real cut-and-dried rule. Right-click on the text and choose 3D Effects.

Select from the many options, then click the green Apply checkmark to apply effects.

To export your text so you can make it into a GIF or similar graphic, see this post.

---

---

Issues with Insert External Data

Some people are having problems inserting data from a CSV or TXT file into a spreadheet, using Insert > Link to External Data.

http://www.openoffice.org/issues/show_bug.cgi?id=1834

If this doesn't work for you, try Insert > Sheet From File instead.

---

---

Help Digg the Great Story about OpenOffice.org 2.3!

There's a great story about OpenOffice 2.3 in EWeek. Give it a read, and Digg it to help draw attention!

---

---

Using regular expressions in OpenOffice Calc filters -- phrases like "contains" rather than equals, less than, etc.

Life just isn't cut and dried. Sometimes when you're filtering you don't want to just say "give me all the people whose last name is Hanson." You want Hanson, Hansen, and Hansengaaardennn (those Dutch really go for the jawbreaker names).

You'd like to filter out everyone except those whose names contain "Hans".

Here's how to do that. Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field. Click More, and select Regular Expressions, then click OK.

Example of what you want	What to enter in the Condition field	Syntax for what to enter in the Value field	Example of what to enter in the Value field
Begins with Hans	=	^x.*	^Hans.* (you can also skip the ^, I've found)
Does not begin with Hans	<>	^x.*	^Hans.*
Ends with Hans	=	.*x$	.*Hans$
Contains Hans	=	.*x.*	.*Hans.*
Does not contain Hans	<>	.*x.*	.*Hans.*

Here are some examples. Let's say you want all names that start with Hans, but not all names that simply contain Hans.

Here's the data.

Select all  the data, or just click in the headings, and choose Data > Filter > Standard Filter. Make the window look like this.

Click OK and you get this; Bob Montrahans is not included. (It's not because of the case.)

Here's a different example. I want names that DON'T CONTAIN the series of letters Hans.

Data

The window with the restrictions:

And the results.

Here's some information from the OOo wiki about regular expressions.

---

---

Using the Advanced Filter

In the Standard Filter you only have three slots for info.

That's a bit limiting. So the Advanced Filter lets you enter up to 8 criteria.

Using the Advanced Filter

Here's your data. Click the image to see it larger.

Now, here's how you enter your critera. Copy your headings and paste them somewhere else in the spreadsheet. Then type the values you want. Click this image to see it larger. I've entered Fargo for the city, ND for the state, and =>5 for the Years of Service. Note that they are all on the same row.  This means they are ANDed together.

Click in the data (not the criteria but the main data) and choose Data > Filter > Advanced Filter. In the window, click in the right-hand field and draw a box around the area where you typed the criteria. Click OK.

You'll see the results. Click the image to see it larger.  ( Simon being the first name for both is just a coincidence.)

Now, if you want OR logic, just enter the values in your critera section on different rows, like this. Click to see a larger version of the image.

These are the corresponding results. Because of the OR, you get a lot more results. Click the image to see a bigger version.

Removing the Filter
To turn off the filter, it's the same as with the standard filter. Click in the filter results, and choose Data > Filter > Remove Filter.

---

---

Using the Standard Filter

The Autofilter is a quick way to restrict what you're looking for. If you need some more flexibility, though, you need to move on to the Standard filter or the Advanced filter.

Maybe you have people from 12 states and you want to see the ones from Ohio OR Montana OR New Jersey. Or you want to see people with five or more years of service. Or you want to see anyone with more than three overdue library books who is also from Denver, because you're traveling to Denver and you want to drop by their houses and scare the heck out of them in person.

These are a challenge for the AutoFilter, so you move on.

Here's how to use the Standard Filter. Let's say you've got this data.

Click somewhere in the data and choose Data > Filter > Standard Filter.

In the window, enter your data. Note that any ORs will open up the results more than you might expect. Here's a filter.  Either from MT or OR, and with 5 or more years of service.

Here are the corresponding results. Note the person from MT with only 1 year of service, but there's no one from OR with fewer than 5 years of service. The logic is
"anyone from Montana"
or
"anyone from Oregon who also has 5 or more years of service."

As with the AutoFilter, you need to click in the filter results to take away the filter. If you don't click in the filter results, as shown, the Remove Filter option is dimmed.

Click in the filter results, as shown, and choose Data > Filter > Remove Filter to get rid of the standard filter.

---

---

Using the AutoFilter: A useful if slightly twitchy tool

Sooner or later, you're going to get a huge spreadsheet with way too much data to scan visually.

How do you, ahem, filter out what you don't want to see?

One way is to use the AutoFilter.

AutoFilter Basics

Let's say you've got this spreadsheet of employees.

You'd like to just take a look at those from Montana, or those with a particular number of years of service. Something like that.

Click somewhere in the data, and choose Data > Filter > AutoFilter.

You see arrows by all the headings.

Click on one  of the arrows, and choose to view all records containing one of the values, or all records containing the top 10, i.e. the ten most frequently occurring values in that column.

Here are the results for selecting one value for one column.

If you choose another value in another column, then you get rows that have the selected value for BOTH columns.

Here, I get rows for people who are in Montana, AND in Kalispell. Which works out fine since Kalispell is a city in Montana.

 

However, if I choose to view records for people from Montana, and from Portland (a city in Maine and in Oregon but NOT in Montana), I get nothing.

To go back to viewing all the values, select All from the list.

Then you get to view all the records again, once you've selected All for any columns you restricted.

When you're done and want to get rid of the little arrows, click somewhere in the data, and choose Data > Filter > AutoFilter again. There'll be a checkmark and when you select AutoFilter, it will go away.

There's the data the way it was before you started.

Issues With AutoFilter

Here's where things get a little twitchy. What if you try to turn off the AutoFilter and you have not selected a cell somewhere within the AutoFilter results?

You get this.

When you get this, click OK and click somewhere in the data.

Then choose Data > Filter > AutoFilter again. You won't see the checkmark, but that's OK.

Then choose Data > Filter > AutoFilter yet again. This time you'll see the checkmark.

And then the arrows will disappear and you're back to normal.

I also recommend liberal use of the Undo feature, Ctrl Z or click the Undo icon. You can undo at least 20 and possibly more depending on how your system is set up.

Can You Delete Rows When in the AutoFilter Without Deleting the Intervening Data?
I'm glad you asked. Yes, you can. Here's a demo.  Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. They're both from Montana.  In between you've got Jon, Marcus, and Kyle.

I'm going to view only people from Montana, which includes Dan and Beth but excludes the three rows between.

Now I'm going to delete Dan and Beth.

And they go away. However, Jon, Marcus, and Kyle are still there.

 

---

---

Things that don't work when you save Writer documents in Word's .doc format

OpenOffice.org has a feature that lets you automatically save documents in Microsoft Office format. Save Writer as Word, save Calc as Excel, etc. This lets your users send out documents to the outside world without having to remember to save in MS format first.

Choose Tools > Options > OpenOffice.org > Load/Save and use the lists in the bottom half of the window.

Likewise, you can save an OOo document in MS format by choosing File > Save As, or by choosing File > Send > Document as [microsoft format for that document type].

However. There's a but. It's not a huge but, but it's significant.

In some versions of OpenOffice.org, the following features don't work when you save a Writer document in Word format. It's not just the automatic saving, it's saving in Word format through File > Save As, as well. So the only way to get around it is to save as PDF (click the PDF icon on the toolbar or choose File > Export as PDF).

This isn't a complete list; please add your own through comments. I just tested all of these on the standard 2.3 release.

Mail merge prints field names, not content. If you save your mail merge document in Word format, then print, all you'll get is the names of the fields, like Firstname, rather than the data, like Bob.

Background graphics disappear. If you choose to put a graphic in the background of a header footer or page, under Format > Page, the graphics will disappear when you save in Word format.

Custom frame, page, and list styles get screwed up This one is an issue because page styles are the basis for doing so much really good, powerful stuff. (I hope this is on the list of things to fix, and/or not a problem in other builds.) I just tested, in 2.3, a file I'd created with custom page styles to automatically switch from a page style with no page number on the first page footer to a page number on subsequent page footers.

If you insert page breaks between page styles with Insert > Manual Break, and the  Break With feature in the Text Flow tab of a paragraph style, do preserve more formatting than the Next Style feature in the Organizer tab, which preserves nothing.  The headers and footers are preserved in the first two approaches, as are page borders and jumps from page 2 to page 66.  Landscape versus portrait is also preserved.

However, no background formatting is preserved, and the page style names are changed to Default for the first one, Convert1 for the second, and so on.

If these are issues for you, please vote to have the following bugs prioritized as things to work on. (I'm actually having trouble bringing up the OOo issue tracker right now; I think these are correct but I will check them later.)

http://www.openoffice.org/issues/show_bug.cgi?id=78723

http://www.openoffice.org/issues/show_bug.cgi?id=73533

http://www.openoffice.org/issues/show_bug.cgi?id=11522

http://www.openoffice.org/issues/show_bug.cgi?id=22635

---

---

Fixing problem OpenOffice files by unzipping, mixing, and matching

One of the great things about OpenOffice.org is that you can open corrupted Word files with it. Or Word files that are just too big to open in Word, open fine in OpenOffice Writer.

However, every so often you will get a rogue OpenOffice file that just won't behave. It crashes constantly, or behaves in other ways that just don't make sense.

In that case, the best approach is surgery.

OpenOffice file formats can be unzipped to reveal their components. Once you see their components, you can take copy different components from a different uncorrupted file and replace the corrupted ones in your problem file. Zip it all up together again, and whammo, your file works.

I'll show an example in Writer but the same principles apply to Calc, Draw, and Impress.

So you have your file. Let's say this is the file you're having problems with. It's got a couple styles, a picture, and of course content.

I like to make a copy of the problem file, just to make sure I can always get back to the original version. So I create a copy, give it a different name, and change the extension to .zip. (Or gzip, or whatever works for you on your operating system.)

Then unzip the .zip file, and you'll get a directory of component files and directories.

Here's what's inside that new directory.

I'm not going to go into painful detail about all of the content. But content.xml contains the content, styles.xml contains the style definitions, Pictures contains the graphics, and so on.

Here's a snap of part of the content.xml.

And of the styles.xml.

And the Pictures directory. Note that the file name is different than the inserted picture.

So here's what I do. If I have content but don't care about the styles, pictures, whatever else in the problem document, I:

- create a new totally empty OpenOffice document of the same type (Writer, Calc, etc.)

- change the extension to .zip and unzip it

- copy the content.xml file from the problem doc directory into the new empty doc directory, replacing the empty doc's content.xml file

- zip up the new empty doc directory

- change the .zip extension to .odt, .ods, or whatever

- and open it up again, using this as the new version of the problem document

If you have pictures and styles in the problem document that you need, then just copy the Pictures and Thumbnails directories, and the content.xml and styles.xml files, into the new empty doc directory, replacing the corresponding directories and files.

It's a techy but quite effective way to redo a document.

Would it be better to just copy and paste the content of the problem document to another new empty document? Not always--nasties have a nasty way of accompanying the content. But sure, try that first, and if that doesn't work, then do this.

---

---

Google Docs Extension for OpenOffice

There's a nice little extension for OpenOffice.org that lets you quickly upload your document to your googledocs account. Click here to get it.

Installing the extension gives you this toolbar, as well as a Google Docs menu.

 

Click it to get this window; just enter the appropriate information.

 

Your document will be automatically uploaded to your account in Google. It works pretty nicely.

Installation

Installing extensions is pretty easy. Download the extension. Then choose Tools > Extension Manager. Select My Extensions and click Add.

 

Find the extension file you downloaded, an installation process runs, and you’ll see Enabled next to the extension. For some extensions, you’ll need to restart. Look for a new menu, new menu items, new toolbars, or all three.

---

---

Another way to tweak labels, and save the format

In this entry I talked about a fairly straightforward but manual way of giving your labels a little room to breathe.

In this entry, I'll go through how to use the Format tab to tweak a particular layout, then save it for re-use.

When you create labels, you of course choose File > New > Labels. You select your type and layout here, then add your content, and then click New Document.

You get something that looks like this.

Now, what if you then print and everything is too high, too low, too much to the left, etc.?

Well, you just adjust it, then save that adjustment as a specific format you can select next time.

When you're in the Labels window, click the Format tab.

Here's what all the measurements mean. I suggest starting by changing the left and top margin, then get into changing the pitch if necessary.

Horizontal pitch

The distance from the left of one label to the left of the label to the right of it. If you want to actually increase the distance between columns of labels, i.e. if labels get increasingly (or decreasingly) cut off as you go across the sheet, change this.

Vertical pitch

The distance from the top of one label to the top of the label below it. If you want to actually increase the distance between rows of labels, i.e. if labels get increasingly (or decreasingly) cut off as you go down the sheet, change this.

Width

Just the width of the actual space for the label content.

Height

Just the height of the actual space for the label content.

Left margin

The distance from the left side of your sheet of labels to where content begins. If all your labels are getting cut off on the left, adjust this.

Upper margin

The distance from the top of your sheet of labels to where content begins. If all your labels are getting cut off on the top adjust this.

Columns

The number of columns. You don't need to adjust this.

Rows

The number of rows. You don't need to adjust this.

Examples

Here's a normal sheet, next to one where I increased just the top margin. Click the image to see it larger.

Here's an example where I increased the vertical pitch by a half inch. You wouldn't want to increase it that much, but I made it big to make sure you could see the effect. Click the image to see it bigger.  Note that on the right, you only are at the 5th row while at the same place on the left, you're at the 7th row.

Once you've got the label adjustments where you want them, click Save in the Format tab. Name the label in the window that appears, and click OK.

Then when you create labels again, that saved format will be in the list.

---

---

Sun Report Builder Extension

Anyone who's worked with the OpenOffice.org Base report writer knows that it's....a first generation product. It works but it doesn't have huge features. So I’m particularly glad to see some work being done with reports, in the new Report Builder extension from Sun.

http://extensions.services.openoffice.org/    for all extensions

http://extensions.services.openoffice.org/project/reportdesign   for the Sun Report Builder

The Report Builder extension looks like it has a lot of powerful features, though not exactly easy to see how to use. I’ve spent a few hours with it and one thing that bugs me a bit is that the tab for selecting the data source for the report disappears if you click on something else first. Ease of use aside, though, it does have quite a feature set, including grouped records, sorting of records, different alignment of text fields, and calculations.

I'm going to have to spend a lot more time with this to really figure it out and give some procedures, but here's a short tour of the basics.

To use the Sun Report Writer extension, download and install it first. (Tools > Extension Manager). Then open the .odb database file for the database you want to create a report for. Choose Insert > Report, and you’ll see the report writer interface.

 

This is the tab that disappears too quickly. Select Table or another type of data, then select the actual source. Once you make that selection, the Add Field palette appears; use it to drag fields onto the appropriate section of the report.
   

Click the Sorting and Grouping icon on the toolbar to get this window where you have a lot of control over how the fields and the report behave.

 

When you’ve dragged fields onto the report, set options, inserted page numbers, and done other formatting, save the report. The report will show up in the Reports area of the main editing windows of the .odb database file.

 

---

---

Apple adds support for ODF

Mats wrote to let me know that Apple has added OpenDocument support to TextEdit, the text editor shipping with OS X. When the latest version of Apple´s operating system, "OS X Leopard", is released today, any Leopard user will be able to read and write Open Office-compatible files.

http://www.apple.com/macosx/features/300.html#textedit

Whoohoo!

---

---

Things that control how content is displayed in OpenOffice.org Calc spreadsheets

If you spend all day in spreadsheets, sooner or later you want something to help you spot what's important or different. The motion study expert Frank Gilbreth told factories to paint parts different colors to help factory workers spot the right pieces more quickly; Calc has roughly equivalent features to help point out the different types of data you're working with.

Many of the settings are controlled here. Choose Tools > Options > OpenOffice.org Calc > General. Click the image to see it bigger.

Here's a sample spreadsheet, shown the usual way.

Here's what it looks like when, in the Display section of the Options window, you mark the Formulas option.

Here's what it looks like  when you mark Shown References in Color. It means if you double-click a formula, the referenced cells are shown color coded.

And if you mark the Value Highlighting option, then formulas are shown in a different color than formulas.

And in the same options window on the left side, you can change the color of the borders between the cells from light gray to whatever you want. Here's what they look like in magenta.

There are also some options that help you see the relationships among your data; the Tools > Detective menu item.

Tracing precedents means, for the selected cell or cells, show other cells that are a step up in the calculation. For instance, the tax rate is used in the selected cell to figure out after tax monthly income, so it's a precedent.

Tracing dependents is the same, but the other way around. Monthly Income depends on the selected cell, total income.

And, if you've got some errors, the Trace Errors feature will show what other cells are involved in the error cell.

---

---

Summary of new features in OpenOffice.org 2.3

Here’s a summary of the features from the 2.3 new features list that I considered the most useful or important to write about. This page  http://wiki.services.openoffice.org/wiki/New_Features_2.3 about the new features is an excellent guide, as well.

General

• This is convenient for anyone who prints to multiple printers, all over the world. You can load or ignore the printer settings for your documents. This means you don’t end up accidentally printing to the printer in building 4 which is on the opposite side of the country, just because you were on a business trip there last week and that’s where you last printed your document.
  

• If your document isn’t wider than the OpenOffice.org window, then it will be centered in the window, not left-aligned.
  

• Lots of locale information was added, for locations such as Tagalog, Frisian, and Hausa.
  

Writer and Web

• The HTML editor now has a preview feature. Choose File > Preview in Web Browser and the document opens in the default browser.

• I love this feature. You know how when you get a hyperlink but then want to retype it or reformat it, but clicking on it takes you to the target of the link? No more. You can select hyperlinked text all you want; you now have to Ctrl Click to open a link. This is very nice.
  

• The notes say that there is a new compatibility option on Tools > Options > OpenOffice.org Writer > Compatibility: Do Not Justify Alignment in Lines Ending With Manual Line Break. However, I’m mentioning this because I couldn’t see it. The illustration shows the compatibility options that are there.
  

• When you open the Styles and Formatting window (Format > Styles and Formatting), you can set what kinds of styles you wanted to see: Applied, Custom, Automatic, etc. Previously, you had to reset this every time you opened a new document or re-opened OpenOffice.org. Now, thankfully, that category will stick. The setting is saved per application. However, the choice you make for Paragraph, Character, Frame, List, or Page doesn’t stick.
  
  

• When you right-click on text, you used to see Default as one of the options. Now you see Default Formatting, which is clearer. (Default Formatting is a great way to just clear out any extraneous formatting and apply the default style to the selected item.) This is a very nice feature regardless of the text; for one thing, it’s the best way to remove the hotlink from a URL.
  

• A new export filter lets you export to MediaWiki format. Choose File > Export and select MediaWiki in the file format list.

Calc

• This is a very, very smart change. By default, the print options for Calc are now set to Print Only Selected Sheets and Suppress Output of Empty Pages. If the Print Only Selected Sheets option is enabled, the Calc page preview shows only the displayed sheet and the message “There is nothing to print.” To change these options, choose Tools > Options > OpenOffice.org Calc > Print, or choose File > Print and click the Options button.
  

• Here’s another very smart change that will screw up all my documentation. :) The SUM icon on the main Calc toolbar has changed. Now you can select the range of numbers to add, click the SUM icon, and get the total in the first cell below the selected range. Phew. But if you liked it the old way, it still works that way, too.
  

• Graphics can be linked to macros. This should help with Excel compatibility.
  

• The Excel export filter now handles the cotangent functions COT, ACOT, COTH, and ACOTH.

• Calc now supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE) or a literal string. See this link for more detail. http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=230

• You can now use dynamic ranges, rather than absolute ranges defined with $, in lists in Data Validity. Choose Data > Validity, and under the Criteria tab select Cell Range from the list.
  
  

• The GETPIVOTDATA function returns a result value from a DataPilot table, so it can be used in a cell formula.
  

Mail Merge, Databases, and Forms

• The infamous checkbox on the print message when you print a mail merge document, Do Not Show Warning Again, is gone. Phew! See this blog http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html for why that caused problems.
  
  

• This is nice. When you choose File > Print with a mail merge document, in the Mail Merge window, you can choose to save the document as separate documents or as one document.
  
  

• Unfortunately, in Base there is still no File > Export or File > Import feature. File > Export does appear, but it’s dimmed.

---

---

Welcome back, user-defined motion paths in OpenOffice Impress 2.3

Back in the good old days of 1.x, you could draw a line, then draw an object, and make the object move along whatever line that was. It was great.

Then the lovely redesign of Impress came, and that user-defined motion path feature got lost along the way.  It was a sad time.

However, now in 2.3 it's back! It works slightly differently, but it's great. In addition, you can edit the existing motion paths like the stars, etc. Here's how it works.

Draw an object, then under Custom Animation’s Motion Paths tab, select any one of the first three effects.

Your cursor will change so that it will draw the kind of line you selected. Draw the path that you want the object to follow. Then run the presentation to see the effect.



To edit existing motion paths, just apply a standard motion path like Eight-Point Star or Diamond. The path will appear in the slide. Click on the path and expand it; you’ll get another path (there’ll be two diamonds, for instance). Delete the old path, and you’re good.

---

---

Question: Novell and Excel Macros?

I have heard various statements or rumors about the Novell version of OpenOffice.org being able to convert Excel macros to OpenOffice.org format. Can anyone comment on this, with personal experience? It is of course a fabulous thing to be able to do, if it's possible.

Solveig

Sorting: Sorting by months or days of the week, in date order instead of alphabetical order

All right. It's time to do some really advanced sorting.

Let's say you have a schedule and you want to sort it by day of the week. What's the first day of the week? Monday. But alphabetically it's not first. Friday, for instance, comes before Monday. So here's how to sort by days of the week, months, etc.

You've got your data.

Select it and choose Data > Sort.

Click the Options tab and make the appropriate selection for Range Contains Column Labels.

Now select Custom Sort Order and select the one you want.

Click the Sort Criteria tab and select the column to sort by that contains the corresponding kind of data.

Click OK.

And you get your results.

Wondering "Where did that sort order come from?"

Wondering "Could I perhaps make my own sort order, like President Vice-President Secretary Treasurer or High Card, Pair, Two Pair, Trips, and so on?"

You will have your questios answered in the next installment of Sorting.

---

---

A podcast: Solveig Talks to Barton George from Sun

I ran into Barton George at LinuxWorld in August. Barton develops relationships with the various GNU/Linux communities as well as Sun's relationship with the FSF.

Barton and I chatted about various things; read about them here  and/or listen here.

---

---

Sorting: More powerful sorting using the Sort window

You want to do some good, hard sorting. Not just sorting by the first column, but by perhaps the third column. Maybe you want to sort first by state, then by city, then by last name. For that, you need the Sort window under Data > Sort.

You have your data.  Select all the data to sort, and either select the headings or not. You're good either way.

Choose Data > Sort.

Click the Options tab. You need to tell Calc whether you've got headings selected or not. If you've got headings selected, you want the option shown, "Range Contains Column Labels," to be selected. If you didn't, unmark it.

Now click the Sort Criteria tab. If you selected headings you'll see the headings themselves; if you didn't then you'll just see Column A, etc. Select the column to sort by, and Ascending or Descending.

Click OK.

You get your results. Here, the amounts under the column for the year 2000 are sorted in ascending order.

Now, let's look at a different set of data. You have a lot of people from the same state, and several cities per state. In this case you might want to just sort by last name, but you could also group by location. So you'd sort by state (the broadest category), then city, then alphabetically by last name. Click the image to see more detail.

Select the data, with or without headings, and choose Data > Sort. In the Options tab, be sure to select the Range Contains Column Labels option if you selected headings.

In the Sort Criteria tab, select first State, then City, then Last Name.

Click OK.

You get your results. Here's a closeup of one section, followed by the complete data. Click either to see them closeup.

This window that I've covered in this blog entry is pretty much what you need. If you want to go a little farther and sort by something else, like days of the week in the order they come, not alphabetical order, tune in for the next sorting blog.

IBM's already making a difference: publicity about new office suites

Here's the article, on CNet.

"After years of watching Microsoft rake in billions of dollars from its desktop software franchise, its competitors are pouncing.

IBM on Tuesday announced the release of Lotus Symphony, a suite of free desktop applications based on the OpenOffice.org open-source product...." and so on.

Now, this article is about seven years too late since Sun had StarOffice out there a while ago. But Sun didn't....really....go gangbusters marketing StarOffice. There were the tshirts and bus ads, yes, a year or so ago, but not much else that I saw.

Let's hope the publicity, and recognition of OpenOffice.org and its variants, continues!

---

---

OpenOffice.org 2.3 has been released!

http://www.openoffice.org/

This is the version with the new charting tool. See blogs here and here.

Also, take a look at this. I will be writing about it soon.
It's a fancy new report writer tool extension.

http://extensions.services.openoffice.org/project/reportdesign

 

And it seems like that would be enough, but today is also a day to, well, note (certainly not celebrate, that would be unsportsmanlike ;> ) that the EU has upheld its judgment against Microsoft, and the big ol' fine.

---

---

Sorting: Your basic everyday sorting

In the mood of fall cleaning and to celebrate having gotten my office organized, I'm going to blog this week about sorting. Today, it's just basic run-of-the-mill sorting using the sorting icons on the toolbar.

Let's say you've just got this list, and you want to sort it alphabetically.

Select it, without selecting any data you don't want sorted.

Click either the A-Z or Z-A icon.

You get your results.

Be sure not to select any headings, i.e. the word Employee in this case, or you get this result, which you don't want.

What if you have something like this, though? Something with multiple columns.

Here's the thing. Selecting, say, the 2000 column and clicking a sort icon will NOT give you good results. It will sort just the data in the 2000 column and leave all the other data behind. So all of a sudden your data is wrong.

You cannot specify the column to sort by, using the simple sort icons. That's covered in my next blog entry on sorting.

Here's what you can do. You can sort by the first column in the data set. You select ALL the data, again without the headings.

You click the sort icon you want.

And you get your results; the data is sorted by the first column. That's your only choice.

If you had selected just one column, the data would be goofed up.

For more control over sorting, tune in for the next sorting blog.

---

---

Educators, this is for you! The first K-12 Open Minds (open source software) conference is October 9-11, 2007 in Indianapolis, Indiana.

The first K-12 Open Minds conference is going to be held October 9 - 11, 2007, in Indianapolis, Indiana. The kick-off reception is Tuesday, the 9th, with the regular conference sessions on Wednesday and Thursday, the 10th & 11th.

The Open Minds Conference is the first national K-12 gathering for teachers, technicians and educational leaders to share and explore the benefits of open source in education. Virtual Learning Environments that provide 24X7 access to teaching and learning resources, cutting-edge and easy-to-use desktop applications, coupled with powerful management tools and low-cost computer strategies make the classroom of tomorrow available today!

Schools around the United States and the world are discovering the the benefits of open source software. In Indiana alone, over 100,000 students use open source software every day. Not only does open source save money, it allows schools to extend the benefits of technology more broadly, affording a better education to students.

I think this is the must-attend event of the year relating to Free and Open Source Software in K-12 education.  There are currently over 55 planned conference sessions, covering a the use of Linux and Open Source use in classroom, teaching, technical, and leadership aspects.

The individual registration fee is $100, or $89 each for groups of three or more. Register on the website or call Anthony Yanez, Registration Coordinator, at 800.940.6039, extension 1348.  Compared to other conferences I've been to, this is a huge bargain.

This is a great opportunity to really find out how much you can benefit, and how much money you can save, by using open source software. And of course to learn enormous amounts about using the software.

---

---

Getting a Picture from a Document Into the Gallery

I've already written about how to get a graphic from a file on your computer into the Gallery. But how do you get a graphic you like, that's just sitting in some OpenOffice document, into the Gallery? You could just copy it and paste it from that document to others, but putting it in the Gallery is more convenient.

1. Open the document containing the graphic.

2. If the gallery isn't showing, open the gallery by choosing Tools > Gallery or clicking on the Gallery icon.

3. Select the theme, the category you want to put the graphic in, in the gallery.

4. If you want to make a new one, click New Theme. Type the new name and click OK. Then select that theme in the Gallery.

5. Select the graphic. Release the mouse.

6. Click and hold down, and keep holding down on the graphic, for at least two seconds. Don't release the mouse, and drag the graphic into the gallery.

7. The graphic will appear in the gallery, at whatever location is set up as your gallery location, in a subdirectory for draganddrop.

You can set or change your gallery paths here. Tools > Options > OpenOffice.org > Paths.

---

---

OpenOffice Template for Writer: Suppressing the Page Number on the First Page

Suppressing the page number on the first page of a document is, unfortunately, not available simply by selecting the Suppress First Page Number checkbox.

You can set up your own documents, using page styles, so that you nave no page number on the first page, but a page number on the second and subsequent pages.
http://openoffice.blogs.com/openoffice/2006/12/merry_christmas.html

http://openoffice.blogs.com/openoffice/2006/09/starting_page_n.html

But if you want a template already made, here are two.
No page number on first page, second page is numbered page 1

No page number on first page, second page is numbered page 2

Here's how to use them.

1. Right-click on each item to download it. Save each file to a location on your computer or a network drive you can access.

Note: You might want to just save the templates to the location already listed under Tools > Options > OpenOffice.org > Paths, Templates item. Then in step 3 choose Commands > Update instead of Commands > Import.

2. Then in OpenOffice.org Writer choose File > Templates > Organize.

3. If you saved the template to one of the existing template paths, choose Commands > Update instead and continue to step 4.

If you saved the template to another location, then select a category on the left side,  click and hold down on the Commands button and choose Import as shown.

Find the files where you downloaded them. To select both, select the first, then hold down Ctrl and select the second. Click Open.

4. The templates will appear. Click Close.

5.  If you want one of the templates to be what comes up when you choose File > New > Text document, then right-click on it and choose Set as Default Template. I strongly recommend customizing the default template, whether you use this one or another one.

6.  Click Close.

7. To access the templates, choose File > New > Templates and Documents. If necessary, click the Templates icon at the left, double-click the name of the category you put the templates in in step 3, and you'll see the templates. Select the one you want.

---

50 Open Source success stories in Business, Education, and Government

Success stories! If you're considering switching to any open source program, read on.

http://www.crmchump.org/2006/10/in_a_world_wher.html

Here's one story.

Golden also mentioned the implementation of SugarCRM Oregon Department of Human Services. The service provider needed a CRM put into place quickly, due to new HIPAA regulations in electronic billing. Believe it or not, Oregon DHS was sold on the idea when an IT engineer surfed the internet, found open-source SugarCRM, downloaded it onto a laptop and showed it around the department the next day. After “a couple of days” configuring, Oregon DHS went live with the system via the same laptop.

And get this, especially the end.

CRMBlog recently reported the story of the implementation over at Learning.com, a developer of online technology integration and assessment tools for public schools. Within six months of installing NetSuite, Learning.com had achieved results in acceleration of its business processes by a crazy 1500 percent and ultimately doubled business in 2005. And best of all, Learning.com reportedly required no extra personnel or personnel costs in making the switch.

Click here to read more.

---

Support ODF; Support Democracy in Texas!

Texas is discussing open document format, the format used by OpenOffice.org and not so much by Microsoft.

Read more on Sam Hiser's blog.

http://fussnotes.typepad.com/plexnex/2007/03/support_odf_sup.html

---

OpenOffice.org 2.1 Changes

I eagerly searched the openoffice.org web site to find the list of changes in 2.1.

Mmm, this will tell me everything I need to know, I thought, and smiled saucily to myself. (Or something like that.)

http://development.openoffice.org/releases/2.1.0.html

<>

Well...it's a little dry

. A little developer-oriented. Not that I mind that they seemed to have fixed a bunch of bugs. Love the bug-fixin'.

One very nice feature is the security in PDF. File > Export as PDF, name the file, and you get the new security tab.

Here's something a little more feature-oriented.
http://www.cheeplinux.com/product_info.php?products_id=413

"There are a number of important new features for users in this release. The presentations application, Impress, now supports multiple monitors, with the presenter choosing where to display the presentation. The Calc spreadsheet has an improved HTML export capability, using styles to better recreate in a browser the appearance of the original spreadsheet. The database application, Base, has a number of enhancements, including improved support for Microsoft's Access product. The popular Quickstarter is now available for GNU/Linux users as a GTK application. OpenOffice.org's impressive language support is enhanced with five more localisations.

Version 2.1 also provides new support for developers, extending version control to extensions, simplifying the management of packages for those developing extensions. And, for those wanting to take advantage of new features as they become available between releases, 2.1 includes 'an improved on-line notifier, which checks regularly and informs users if a new version is available (users may choose to disable this option at any time).'"

Regarding that HTML export for spreadsheets, I clearly need to do some research into that since I don't see the point yet.  I exported a spreadsheet by choosing File > Export and chose the XHTML format. A browser didn't open the file that was exported. Changing the extension to .html gave me a blank file (with lots of markup under View > Source). And when I opened the .xhtml file in OpenOffice, I just got a bunch of markup.

By way of contrast, copying a spreadsheet into a Web document in Writer works pretty nicely.

Spreadsheet

Spreadsheet copied and pasted into a new Web document. (I added the borders, which weren't there when pasted, but then the original spreadsheet had no borders, so that's the correct way to paste it.)

So I'll gather up what I can about the point for the user of XHTML, or feel free to comment here, but so far I am unclear on the benefit.

Has anyone else found anything cool and new or different in 2.1? Or a more clear/detailed/UI-oriented guide to the changes?

---

TechTarget Article: Getting Data Back Out of OpenOffice.org Base Databases

I've written an article for TechTarget.com on how to export data from a Base database to a spreadsheet or text file.

http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1226457,00.html

See this blog for how to import data from a spreadsheet into a Base database. (This creates a true Base database, as opposed to a database file that points to a spreadsheet.)

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

 

---

Migrating to OpenOffice.org ODF Format

I like to talk periodically about how you actually do the work of migrating your systems, and your users, to OpenOffice.org. Even better, I like to find other people's advice on how to do this. Bob Sutor has an excellent list of questions to ask. Once you have the answers, you can start in on the plan.   His list, 10 possible elements of your ODF migration strategy, has a lot in common with my own recommendations, including identifying gurus to start working on the migration.

Based on my own experience, I want to strongly recommend among other things that if you have complex or badly formatted  documents that are causing problems when you convert, just pay to get them converted. Your users will be far happier.

My own blogs on the topic are here  and here.

 

---

Making OpenOffice.org Work the Way You Want It (Repost)

 

(Originally posted in October 2005.)

Note: Here's a related article I wrote for TechTarget.com.

What's better, software that does what it thinks you want, or software that does what you tell it to?

If it's rocket-launching software, and I don't know much about rocket-launching, I guess I would let the software do what it wants. However, I know what I darn well want my office suite software to do. I know how I want it to behave. I know what features I want and I know, for instance, that I do not want it suggesting to me what word I am typing and offering to help me finish the arduous task of typing it.

OpenOffice.org has its default behavior and default settings just like any other software but is very cooperative in letting you customize those behavior and settings the way you want them. Which is refreshing. You just have to tell it how to behave.

So I've put together a list of the top  customizations I think are the most helpful and/or powerful. I make sure that everyone in my classes learns these by lunchtime, and review them afterwards. When the software behaves the way you want, that makes everything better.

(By the way, this is a little bit off topic, but I wanted to mention that the scroll graphic at the top of this blog is a drawing shape in OOo 2.0.  I'm a bit of a giggly schoolgirl when it comes to the OOo drawing tools, especially the new 2.0 features.)

1. Turn off the word completion.

I hate word completion, and it’s really easy to turn off. Choose Tools > AutoCorrect and click on the Word Completion tab. Make sure the Enable Word Completion option is unmarked, and click OK.

2. Turn off any automatic formatting that you don’t want.

Would you let people live in your house who you didn’t know? Then you don’t want automatic formatting going on that you don’t understand. Choose Tools > AutoCorrect and click on the Options tab. Unmark everything except the top option, Use Replacement Table. Then go back through and see if you really want anything.

(You can unmark the Use Replacement Table option too....but that table is handy, as you'll see in the next item.)

3. Use the automatic formatting to create handy shortcuts.

The same tab where you turned off word completion has a really great feature for creating shortcuts. Let’s say you type the word supercalfragilisticexpealidocious a zillion times a day, or your name and title, or anything kinda long. You can set up a shortcut for it. It's a much more reliable approach than word completion.

To do this: Choose Tools > AutoCorrect and click on the Replace tab.

A. In the left-hand field type your shortcut like sig and in the right-hand field, type the word you’re tired of typing all the time.

B. Click New, then click OK.

C. Click the Options tab and be sure that both checkboxes for the top item, Use Replacement Table, are marked. That just means "use the stuff in the Replace tab."

D.  In your document, type the shortcut, followed by a space, and your word will appear.

Note: You can also delete anything in the Replace tab that you don't want.

4. Display the icons that you want.

There are a zillion icons in OOo as with any software and you probably don’t use all of them. There’s also that dandy little result of having to click on the black arrow to get to the icons you want, while the ones you do want sit there taking up space and, quite frankly, smiling a bit smugly. So take off the ones you don’t want, leave room for the ones you do want, and add some other ones.

First step is to take off the ones you don’t want. Click on the dropdown arrow and choose Visible Buttons. Find the icons you don’t want, like double spacing, and select them. That’ll remove the checkbox by them, and that removes them from the toolbar.

Now add the icons you want. The first thing to try is to click the dropdown arrow again and choose Visible Buttons. If the icon you want is there, select it and it’ll appear.

If the icon you want isn’t there, click on the dropdown arrow again and instead of Visible Buttons, select Customize Toolbar. Find the toolbar you want to add icons to. Click Add, and in the window that appears just keep looking through the categories on the left til you find the feature you want in the list on the right. Select it and click Add.

 

Back in the customization window, you can leave the icon as is and just click OK, or change the icon by clicking and holding down on the Modify button and choosing Icon.

5. Get to know the choices under Tools > Options.

Choose Tools > Options, and you’ll see the big fat configuration window. Just as the items under Tools > Autocorrect were about default behavior, Tools > Options is about default settings, default values. Anything about the program, from icon size to language settings to where the program looks when you choose File > Open, is set here.

I suggest that you open the OpenOffice.org (or StarOffice as in this illutration) item at the top, then select Paths, and change the values for any paths you use a lot. Change the My Documents item, for instance, to change the default for where OOo tries to save documents. You’ll save a lot of time scrolling around in your Save windows.

To change the path, select it in the window, click Edit, and just point to the new location.

You might also want to expand the StarOffice Write item, select Default Fonts, and choose the ones you prefer.

When you’re done, just click OK.

You can turn off the Save for Autorecovery feature if you want--or increase the save interval
I find this feature entirely un-annoying, but you can turn it off easily if you want. Autorecovery means if OpenOffice.org crashes or you have to coldboot your machine, at least you'll have something recent to return to. If you're hypervigilant, increase the interval to every minute or 5; if you don't care about it, set it to every hour or just turn it off.

Choose Tools > Options > OpenOffice.org > Load/Save > General. You're looking for the Save AutoRecovery Information Every  option. Unmark it, or change the interval.

Click the thumbnail below to see a bigger image of the window.

Now OOo is more like a well-behaved pet and less likely to jump up at you, licking and biting inappropriately.

Those are not all the configuration steps you can do, by a long shot. But I like them, and students seem to like them. And they're an important set of steps in the general process of showing that OpenOffice.org does what you tell it to do.

---

The Microsoft Office 2007 Upgrade Is Going to Have a Big Learning Curve--and a Big Price. Now's the Time to Consider OpenOffice.org Instead.

(Originally published February 2006)

Office 2007 Is Just Another Upgrade...Right?
In a word. No.

This is going to be huge. Painful. Expensive. And that's before you get to the retraining costs.

Here's why.

Microsoft Office 2007 Is a Radical Redesign of the User Interface, and Will Require Plenty of Retraining

Here's the current Microsoft Word toolbar.

Courtesy of LInux Watch  and linked articles, here's a screen shot of MS Word 2003. Click to see a larger version.

MS Office 12 looks entirely different, and changes constantly as you move in the document. Click this image to see a larger version.

And again courtesy of Linux Watch, here's a screen shot of MS Office 2007. Click to see a larger version.

You can't just install this on all 500 computers at your organization and tell people there's a new version.

The Buzz on Microsoft Office 2007 Is the Retraining

Experts around the planet are leary of the radically changed new interface.

Here's an excerpt from an article on eWeek.

Heading the list of challenges facing Microsoft is the fact that Office 2007 has a new user interface, which could require extensive staff retraining at a significant cost, as well as a new file format, which has the potential to create compatibility issues, analysts such as Joe Wilcox of Jupiter Research, told eWEEK.

"When you introduce something new, it disrupts, and this increases things like help desk costs and employee downtime," Wilcox said. "So, to get to the benefits that come with this, they have to get past whatever retraining will be needed around the new user interface and any hardships around the new file format, which are always disruptive. These are two big hurdles Microsoft has to get around."

Enterprise customers such as Robert Rosen, CIO for the National Institute of Arthritis and Musculoskeletal and Skin Diseases and an eWEEK Corporate Partner, agree. The new user interface and file formats pose "major concerns and will slow up adoption significantly," Rosen said. "Since we don't know enough about the benefits of Office 2007, we have not yet developed any plans to move forward."

And another quote:

Jupiter Research's Wilcox told eWEEK that if there were ever an opportunity for StarOffice and OpenOffice.org, "this might be it, going head-to-head against Office 2007, because we have a new file format and a new user interface, which means a lot of extra cost," and which could torpedo many enterprises from upgrading.

In addition, a lot of Software Assurance contracts are expiring between now and the end of July, and Microsoft will be aggressively beating the sales drum. Those businesses might well be looking at their alternatives and options before signing a new contract, he said.

Here's Paul Thurott:

For the first time ever, Microsoft has dramatically changed the Office interface, replacing the standard menu-and-toolbar interface we've known since the earliest Windows applications with a new UI paradigm based on context-sensitive ribbons and tabs."

Here are quotes from Paul's article on the new office software, from Jacobe Jaffe, Group Product Manager on the Office team.

As a personal anecdote, I have a variety of PCs, and on one of those machines for a variety of reasons, I still have Office 2003 installed. I use Office 12 essentially full time, and for me to go back into 2003 is not so good. It's pretty painful, actually."

And Paul's follow-up comment--keeping in mind that he's an expert user.

But because this requires a different skill set to accomplish, I had to relearn how to do this. Long story short, most things are easier, but some power user features will require some more work....I'm nitpicking here, of course. The truth is, the Office 12 interface is so much dramatically better than previous versions, it's hard to find fault with it. On the other hand, I am a power user who uses Office all day long, and I slightly resent having to relearn certain skills. I'll get over it.

Mark Shuttleworth (founder of the Ubuntu project, second space tourist and freedom toaster guy), makes this point, posted by Justin here.

This might not be a direct quote but it's Justin's restatement of Mark's point.

"Office 12 has had substantial UI changes, since Microsoft is trying to distance themselves from the Open Office project. End result, users will require re-training. So which is easier? Re-train users in new Office 12? Or simply, start using Open Office which quite frankly looks just like MS Office today."

Here's another blog along the same lines, Ted's Radio Blog, with a similar conclusion at the end.

"Seven different versions. Dozens of applications, with various features disabled. Nightmarish new licenses. New servers. What a mess! All this to print documents, calculate spreadsheets and do other routine office work? I think Microsoft is overreaching here. They may sell to their captive audience, but new computer users whose machines come with Corel Office or OpenOffice are going to be hard-pressed to find a reason to switch. If you haven't tried OpenOffice.org, there's no better time than the present!"

Think about all those users out there.  Switching, if they all do, is not going to be easy. Take a look at just one part of it. Think about how the people who call you, the IT support folks, are going to react. Think about Laura in accounting or Bob down in contracts.

It All Comes Back to Economics 101

You're happy now with Microsoft Office.

But things change, and the cost can grow to outweigh the benefit.

What if this:


changed to this?

Think About the Cost and Benefit of the Upgrade to Microsoft 2007. Really Think About It.
If you feel that the new UI in this version could change your world and your users' worlds,  there might be a wonderful promised land of fabulous easy of use waiting on the other side of the River of Retraining. There might be. But ya gotta cross the River of Retraining first, and some of your users are going to rock the boat.

I'm not saying you shouldn't use Microsoft Office just because they're changing the interface. I'm saying, you're going to have to retrain people. As long as you have to retrain people, why not consider all your options?

Switching from your current Microsoft software to Office 2007 will require:

• A lot of money for the software
• Training and documentation
• Time and effort to install and convert documents

Switching from your current Microsoft software to OpenOffice.org will require:

• A lot of money for the software
• Training and documentation
• Time and effort to install and convert documents

 

<>

The Microsoft Office 2007 Upgrade is more expensive in money, time, and effort than switching to OpenOffice.org.

This Too: OpenOffice.org and Microsoft Office Currently Look More Similar Than Office 2003 and Office 2007
Training your users to go from Office 2003, or before, to OpenOffice.org, might be easier than teaching them the whole new Office 2007 system.

Here's the current Microsoft Word toolbar, and the toolbar for OpenOffice.org Writer. Right now, they're really similar.  Click each to see a bigger image. See how long it takes to tell which is which.

And here's an OpenOffice.org screen shot. Not that different.

Retraining Aside: Do You Need the 2007 Features?

As a commenter on this blog, George Wenger, states, "The vast majority of users in a so-called "average" business setting already have no use for 90% of the existing features of Word, let alone a whole set of new features."

Your job is to make everyone else's job easier. When you walk by the software users in the hall, you want them to say, "Hey, Jim! I can do that mail merge now!" and maybe offer a high five. 

Does Marsha in Accounting or Bob in Contracts need ribbon toolbars and a new UI paradigm?

How many of the new features does your organization actually need? Have the support staff been begging you for this one?

"We're also enabling a new mobile scenario with OneNote Mobile," Jaffe told me. "So literally you'll be able to have a OneNote notebook available to you on a mobile device, like a Windows Powered Smartphone. You can take notes on your Smartphone, or read your [PC-based notes on the Smartphone. The pages in that Smartphone notebook align to the information you have in your PC version of OneNote. They sync up through ActiveSync."

And think about these questions:
- How many complaints will you get about how everything's different?
- What will the overall attitude be like when users come in Monday morning and their desktops are different?
- How much will you spend on training and documentation to get people up and running on Office 2007.

Here's a blog on this topic: Dave Rosenberg states that Vista gives you an opportunity to really compare the actual cost and the actual benefits, and he quotes Jon Oltsik from Enterprise Strategy Group.

"

Later this year, Microsoft will throw a $500 million PR and advertising party aimed at convincing users to upgrade their PCs to Vista. This provides a perfect opportunity for the Linux crowd to persuade CIOs to evaluate Linux and compare pricing. In this way, Microsoft will likely open the door to some unintended Linux desktop momentum.

I have every expectation that Vista will be a much better OS than XP, but do users really need it? Perhaps. Then again, many CIOs may conclude that the more prudent choice would be a Linux desktop and Open Office migration offering good enough functionality, at 10 percent of Microsoft's price."

Switching to OpenOffice.org Means You Can Have Your IT Cake and Eat at Least Part of It, Too
With OpenOffice.org, you can be the kick-butt IT guy who gives your users software that might even be easier to learn than Office 2007, and saves the company money. The VP's assistant Chris loves you, and the VP loves you. Doesn't get much better than that.

If after careful evaluation of 2007, the alternatives (OpenOffice.org among others), and what is involved in upgrading, you still think MS Office 2007 is the best solution for your users' needs, go ahead and upgrade. (Keep in mind that users do not need to be "cool."  ;>  They do need health insurance.)

But it might not be, and it's important to think about your choices. If your job is to deliver the best product for the best value and have your users be able to do their jobs well, please think about the choices.

References for switching to OpenOffice.org: See this post on the process of migrating a group of users who might be resisting the process, and this post on top ten reasons to switch to OpenOffice.org (besides the one covered in this post).

---

Who Knew "Desperate Housewives" and "Alias" Were Linux Fans?

Karim sent me links to these dandy screen shots. Yes, the glamorous soap opera "Desperate Housewives" uses Linux and OpenOffice.org when they shoot computer screens.

Alias episode 212 with OpenOffice Writer
Click to see it bigger.

Desperate Housewives episode 220 with OpenOffice Calc
Click each to see it bigger.

See also the post on "House", Linux, and OpenOffice.org

---

Go Ben! Energetic coverage of the goal to advertise OpenOffice.org

Ben Horst continues to hit the virtual bricks and get coverage of his effort to make sure everyone knows about OpenOffice.org. Every time I look around, someone's asking me "did you know about this?" Including people not in the OpenOffice world.

Here's the gist.

A group of grassroots activists in the OpenOffice.org community have just announced they are going to undertake a similar media campaign to Spread Firefox, starting with a free (as in beer) New York City daily newspaper called "The Metro," published by Metro International. Most penguinistas know what a huge success the grassroots Spread Firefox ad campaign was. Through ads in the New York Times and the Frankfurt General Newspaper, and the hilarious Firefox videos on the FunnyFox website (video link here), Firefox has probably gained greater popular name brand recognition in the general public than any other free open source software (FOSS) project. 

Read more.  Or click here to contribute.

It's not just sending out emails to everyone he knows, though--other efforts have failed. As the Factory City blog notes, "It’s interesting to see this effort emerge organically — especially after the initial thrust to create a SpreadOpenOffice project fashioned after Spread Firefox died on the vine owing to internal struggles over branding control. A similar project SpreadKDE made it out of the gate, but it’s unclear whether it ever took off."

Ben's currently about 75% of the way to his goal.

I'm amazed at how well Ben's spreading the news. We met at Comdex 2003  and I was impressed then with his technical knowledge, his ability to learn the Las Vegas bus system. I was impressed at how such a clean-cut, nice guy could look so  scary in an Army Surplus outfit, which was extremely effective in keeping people from bugging us on our way through some of the more alarming Las Vegas neighborhoods. ;>  But I didn't know he could get people's attention like this.

Ben, I'm tellin' ya, when you get done with this ad, do the same thing in Chicago. That's all it'll take to get Oprah to do that show I'm always fantasizing about where she gives away a free  Linux laptop to everyone in the audience.

---

Microsoft makes concession to rival Office format

Not a phrase you hear all that often.

I yipped "Yes!" extremely loudly, scaring all those around me, when I read this.

http://blog.seattlepi.nwsource.com/microsoft/archives/104761.asp

From Todd Bishop's Microsoft blog.

Microsoft says it will sponsor an open-source project to create tools for translating files between the OpenDocument Format, which arose from the OpenOffice.org program, and the Office Open XML format to be used in Microsoft Office.

Read more.

Also see coverage from CNet.

Of course, we all know what happened when Microsoft decided to support Java. But it's a noteworthy move, nonetheless.

---

OpenOffice 2.03 Is Ready! Get Your Fresh Hot Open Source Office Suite Right Here!

Download OpenOffice.org 2.0.3 here.

---

Microsoft Owns Your Tube Top (Repost)

(Originally posted March 2006)

Remember that commercial for Compass bank, that shows the woman at an ATM in her closet? The ATM told her, "I'm sorry, there will be $2 fee to access your tube top."

(Honest, there is a commercial like that. I'm not just using blatant sexy illustrations for shock value to draw attention to the benefits of ODF.)

That's what the Open Document Format, or ODF, is all about. Lots of people and organizations want all documents to be in .odf rather than .doc or .xls format.

Most software you use creates documents  in a propriety format. That means that the way the software creates the files is exclusive to the people who wrote that software. You can't use another program to open your own files, or at least your choices are very narrow.  That means you need to buy software to continue to open files with those formats--if you want to be able to continue to access your own documents.  Your thoughts, your meeting minutes, your personal budget spreadsheets, etc.--all need to be rented from the people who wrote the software.

Doesn't it seem kind of odd to have to keep paying for the right to get at documents you created?. It's like paying to rent a house you own. It's like having to pay a fee to get into your own closet for your own tube top.

OpenOffice.org, Sun, and other folks think everyone, including Microsoft, should write programs that output documents in  Open Document Format. The instructions for creating programs that make ODF documents are available for anybody to use. That way, when all programs create documents in the same format, then you don't have to pay to open your documents. You can pick the application you want to use, there will be zillions of them, and some of them are free. You aren't dependent on one software program to get at your documents. OpenOffice.org uses ODF format.

Here's an article on what's going on with a group that was formed to promote ODF, and Microsoft.
http://www.hunterstrat.com/news/2006/03/07/eu-watchdogs-have-their-eye-on-microsoft-office/

Here's the web site of the fine folks of the Open Document Format.
http://www.odfalliance.org/

And here's the web site of Scott Johnson who just sums things up quite nicely regarding open source, and open document format. See the item at the end of his list.
http://fuzzyblog.com/archives/2006/03/08/the-trip-home-open-source-in-minnesota/

---

Pasting OpenOffice Calc Spreadsheets Into Writer, as Tables

Alexander Kjerulf (http://www.positivesharing.com ) wrote to me with an excellent question.

How do you get a spreadsheet into a Writer document?

The question is interesting, since it's not really that it's hard—there are just so many ways and many possible results. It's kind of like asking “How do you make a really great vinaigrette?” or “What is the best episode of Buffy the Vampire Slayer?” There is no one right answer

Here are some answers, then. My birthday is coming up, so let's say that I'm planning my ideal birthday weekend and I did it in a spreadsheet first.

However, let's say that I've had the brilliant thought that I should leave it lying around where my boyfriend is sure to see it, so that he can help facilitate some of these items. In this case, I would like it to be in a regular document, with a nice heading across the top.

In short, I want to take some content from a spreadsheet, and turn it into a table.  I'm going to paste.  But which of the many pasting approaches is best?

Just Pasting the Spreadsheet Normally

Copy the cells from the spreadsheet that you want, switch to a Writer document, and paste.

You get a thingamajig that is kind of a table. Note the table icons in the toolbar. However, you can't really use them except to do things like wrap text around the table or apply other properties to the table object.

Pasting normally gives you kind of a table object with a table look

Double-click in any of the cells so that you can select the text and the cells and basically do normal things to it. But when you do this, iit magiliciously turns into a spreadsheet, with spreadsheet icons. You don't have the table icons anymore.

Double-clicking a pasted spreadsheet gives you spreadsheet tools and a temporary spreadsheet look

This could be quite useful if the spreadsheet were chock full of numbers. However, since it's just full of Wonderful Birthday Ideas, I don't really need it to act like a spreadsheet.

The quest for the perfect way to paste this into a Writer document continues.

Exploring the Paste Special Options

When you choose Edit > Paste Special, or click and hold down on the Paste icon in the main toolbar, you get extra pasting options.

You probably won't use the metafile or bitmap that much, since they create graphics. And the calc8 option just does exactly the same thing as normal pasting. However, the rest are quite useful.

Using the HTML (HyperText Markup Language) Paste Special Option

As you might guess, using this option pastes an HTML table version of your spreadsheet. This is pretty slick if you are, for instance, creating a web page. And it's a table, so of course you get the table tools. Not just when you click on the table but when you click in the cells or select the text.

Using the DDE Link Paste Special Option

This is a very slick pasting option—it's one of the two I recommend. You get a Writer table version of the spreadsheet content, but it's linked to the original spreadsheet. When the original spreadsheet changes, this copy of it changes, too. Of course, you have full use of the table icons.

Consider this option even if you don't want the link because you retain any border formatting or background shading.

This is also nice since, as you might have noticed, the alignment was corrected. In the spreadsheet the alignment by default was at the bottom of the cell. Now, it's at the top. If this isn't what you want then it's a disadvantage, but it's what I want in this instance.

Using the Unformatted Text Paste Special Option

When it says unformatted, it means unformatted.

However, what you can do with this, if you want, is to re-convert it to a table using the instructions here.

http://openoffice.blogs.com/openoffice/2006/06/converting_text.html

It's a bit of extra work since you can also use the DDE Link or Formatted Text options to get a regular table.

Using the Formatted Text (RTF) Paste Special Option

This is the other one I recommend. You get a plain old table, in normal Writer format, that's not linked to the original spreadsheet. With the table icons. However, you do lose any formatting associated with the borders or background shading that you might have had in the spreadsheet.

---

Test Drive Microsoft Office 2007 and OpenOffice

 

From jarche.com , why.openoffice.org, ichacked, Tech Smores, and other sites:

Microsoft today announced the opening of a “test drive” so that people can see what Microsoft Office 2007 might look like when it finally goes on sale.

The OpenOffice.org Community invites potential upgraders to go one better - download the full OpenOffice.org 2 office suite today for a test drive, and if you like it, use it free for as long as you like. It’s the ultimate no-strings-attached test drive - if you enjoy the test drive, keep the car!

As office software becomes a commodity product, Microsoft has been forced to make significant changes to the ‘look and feel’ of MS-Office 2007. Because of this, analysts now agree that migrating to Microsoft Office 2007 will be a major upheaval with a significant cost impact.

Unlike changing to Microsoft Office 2007, changing to OpenOffice.org 2 does not require learning how to use office software all over again. Indeed, reports have shown migration to OpenOffice.org 2 is 90% cheaper than migrating to Microsoft Office 2007.

For more information and references to the reports, please see http://why.openoffice.org

Download OpenOffice.org now!

---