OpenOffice.org Training, Tips, and Ideas

Lab files for GetOpenOffice.org workbooks have been updated.

See http://getopenoffice.org/labfiles/    The site is still in transition but the lab files are there and are updated.

Fantastic OpenOffice.org Writer extension for searching and replacing carriage returns (two in a row, etc.) with regular expressions, plus much much more

If you've been typing $^$ into the regular OpenOffice Writer search and replace dialog and coming up empty, try this plugin. It's awesome. It lets you look for a series of empty paragraph returns, or a carriage return at the end of a paragraph plus one empty return, and MUCH much more.

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

Download then choose Tools > Extension Manager to install. Restart OOo and you'll see a new icon in the upper left corner on the main toolbar.

Here's the page.

 

Here's the icon you get when you restart OpenOffice.

 

Here's the window.

 
 Here's the list of regular expression options.

 

And here's an example of what the shown expression found.

 

Slight bug in OpenOffice mail merge

In OpenOffice 3.2 and 3.3 mail merge printing, mail merge doesn't work if you restrict output using Range. However, restricting output using Selected Records works fine.

Here's how it works. Choose File > Print and click yes to print a form letter as usual. Then you have the option to print all records; to print selected records; or to print a range.

With the first two, everything works fine. But if you type in a range like records 1-3 to print, mailmerge just gives you blanks.

This isn't really a big issue since Selected Records works fine. But can cause problems if you don't know it and if like me you were just teaching an envelopes mail merge class. :) Luckily however I figured out the issue during break.

I

Converting OpenOffice documents to PDF or HTML, as a batch

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. It's a great feature that bears re-posting about. It's also really, really not obvious.

You just choose File > Wizard > Web Page to open a whole new world of Web publishing, batch conversion to HTML and PDF, and automatic formatting. 

Put this together with the fact that links in OO.o Writer documents, including linked tables of contents, retain their properties when you convert to PDF, and the potential increases exponentially.

The name of the navigation to the Web Wizard is the same as the previous versions of the software. However, back then, the Web Wizard was nothing but a quick way to get some prefab column layouts and color schemes. In 2.0, it's so much bigger.

About the Web Wizard

Here's what the Web Wizard can do:

• Create a Web page that links to OpenOffice.org documents, Microsoft Office documents, HTML files, or graphics files you specify. The Web page can have multiple layouts, including a left-hand navigation frame and a right-hand document display frame.
• Batch convert OpenOffice.org documents to PDF or HTML. This means that your main HTML index page can link to documents in the original format, to converted versions in HTML or converted versions in PDF. (Note that you can't convert graphics files to PDF, and you can't put all the OpenOffice.org files together in one PDF.)

So, here's what these capabilities allow you to do:

• You can use the wizard to create a web site from existing documents, rather than designing a new site, copying and pasting into HTML, reformatting, etc.
• You can use the wizard simply as a batch converter to HTML.
• You can use the wizard simply as a batch converter to PDF. Got 200 documents you want to change to PDF? Set up the wizard, run it, and go to lunch.
• You can use the wizard as a poor-user's version of the Photoshop Web's page batch convert feature that lets you take a bunch of pictures and put them together in a convenient form for people to view in a browser.
• You can do lots of other things that I haven't thought of yet, but that you will come up with when you fiddle with this great feature.

Using the Web Wizard

Here's how you use the wizard:

1. Get together the files you want to use. You don't have to, but you'll find it's a bit easier when you're choosing the files and if you have to run the wizard again. In addition, create an output directory for the results of the Web Wizard.
2. Choose File > Wizard > Web Wizard. This is just the intro screen. If you were doing this for a second time, if you were going to repeat a previous conversion, you would pick the conversion options from the dropdown list at the bottom. The first time, though, you just need to click Next.



3. This is the main window. Click the Add button and find the files you want in your Web page, or that you want to batch convert. You can select all the files in the dialog box; hold down CTRL, and select the first and last. Then use the up and down arrow buttons in this window to arrange the files in the right order.



4. In the same window, fill out the other fields, such as title. If you're creating a web page for internal training, put something like Internal Training in the Title field.
5. Now you specify the output format for each file. Select the first file in your list, and in the Export to File Format dropdown list, select the format you want. Do this for each file. For graphics, you can only choose the original file format. PDF Press Optimized is better quality and a larger file than PDF Print Optimized.



6. Click Next. If you're just batch converting PDFs and don't care about what the index page looks like, skip this and go to the step where you specify the output directory. Otherwise, pick the layout of the page where you'll navigate through all the files you just specified. I like the left-side frameset, the first in the second row.



7. Click Next. You can select the information that will be displayed by the link to each file in the index page. Just put a checkmark next to the information you want.
8. Click Next. You can pick the color scheme for the index page but not for the converted HTML pages, if you're converting documents to HTML. They're all a little on the overkill side, though Light Gray is reasonably subtle.



9. Click Next. Enter the information about the Web pages that you want displayed in the converted Web page.
10. Click Next. If you're just doing a PDF batch convert, here's where you come in again. Specify the directory where you want the files created. It must already exist; you can't create it through this window on the fly. You'll also want to name the options you chose very specifically, so you can do this easily another time.



11. Click Finish. Once the processing is done, go to the output directory; these are the files you'll see (they vary depending on if your main index page uses frames). Content is where the main files are.

    

12. Find the index.html file. Double-click it and you'll see your navigation page, and links to all the converted files.
13. If all you care about is the converted PDF files, open the content directory and you'll see the PDFs. Double-click to open them.

Looking at output From the Web Wizard

Let's look at what a few of the possible outputs look like. We'll start with OpenOffice.org Writer files, with a frameset navigation index page, converted to HTML.

Now, let's look at OpenOffice.org Writer files, with a frameset navigation index page, converted to PDF.

Graphics files (JPGs), with a frameset navigation index page, left in their original format.

Excited yet? I hope so. The Web Wizard is a good, flexible system with implications for reducing workload by a huge amount.

OpenOffice.org isn't really known for its killer Web page development features, and, of course, Web Wizard doesn't turn it into DreamWeaver. But if you need quick conversion of existing documents, rather than delicately nuanced Web design, this Web Wizard feature is definitely for you.

Just one final note

On some Windows systems, this feature will work one time but not additional times. A message will prompt you to run Repair. You can try, but it probably won't work. If this is your situation, the bug has already been filed with the OpenOffice.org project team, so the best you can try is to install it on another machine, or wait until the next update version comes out.

---

Top Ten Useful Grammar and Punctuation Points I Learned as a Techwriter and in Life in General (and Three to Ignore)

One of the pivotal events in my life was that Tracy Faleide at Great Plains Software (now owned by Bill) decided to hire  raw youngsters straight out of college. Once I was on board there as a techwriter, I learned more useful information about writing from Shewi than I did in college. (I'd have to say, though, that looking back, my high school English education was pretty good.) Fifteen years later, I'm still in the techwriting/editing/authoring field and loving it.

I'm taking a break from OpenOffice tips today to just talk about various grammar and punctuation tips I learned from Shewi, from editing, and various other sources. The key thing about many of these items, and useful grammar and punctuation in general, is they're not just fancy-schmancy rules. They are important rules that affect the meaning of what you say.  I think most people would agree is an important component of communication--controlling the meaning of what you're writing.

Some of them don't affect meaning, but do make it easier and more pleasant for your readers. That means they're more likely to read your email, spec, or marketing blurb, and thus get the information you're trying to convey.

I hope you'll find these useful for general business writing, technical specs, emails to your VP, or wherever it's important that the writing be clear and correct.

10. Hyphenation is important.

Hyphenation is important because it affects what a sentence means, not just because your snotty English major friend will sneer at you for using it incorrectly.

You use hyphenation in two ways (at least).

a) Hyphenation determines what describes what 
You use it to show what an adjective modifies (describes, or applies to).

The orange rimmed vase is not the same as the orange-rimmed vase.

If you have no hyphen between the two adjectives, orange and rimmed, then the adjectives have to both modify the following noun, vase.

In the example, that means that you have a vase which is orange, and which is also rimmed.

Here's an orange rimmed vase.

However, if you have a hyphen between the adjectives, everything changes. The hyphen means the first adjective modifies the second, and then together, they modify the noun.

Here's an orange-rimmed vase . It's  a vase that has an orange rim. The hyphen shows that orange modifies rimmed, not the vase itself.

 

b) Hyphenation is used with compound words
You also use it with compound words like on-line (or online), re-create (as in re-create the error), etc. With this you will drive yourself crazy trying to figure out what's right so just pick a standard and follow it consistently. The standard can be what your friend the writer says, or what the Chicago Manual of Style says, or whatever. Just be consistent.

Me, I like to combine the word unless it's unclear without the hyphen. For instance, re-create is definitely different than recreated. But you know what I mean by “online”—you don't need me to write “on-line” to understand it.

9. Forget you ever encountered ellipsis....unless you're quoting a movie review...and leaving out the...bad parts...

Instead, use semicolons, commas, or the occasional dash or colon. Or just end the sentence with a period and start again with a capital letter. Ellipsis is almost always just a lazy substitute for the right punctuation.

I wanted to tell her that her skirt was tucked into her pantyhose...unfortunately, she went up on stage too soon.

Die, die, die! Unless you're trying to re-create the cadences of actual speech, ellipsis is rarely necessary.

I wanted to tell her that her skirt was tucked into her pantyhose; unfortunately, she went up on stage too soon.

A semicolon separates these two clauses. A clause  is something that could technically be a separate sentence since each has a noun and verb. When you have two clauses like this, you can separate them with a semicolon.

I wanted to tell her that her skirt was tucked into her pantyhose. Unfortunately, she went up on stage too soon.

See? The two clauses are just fine as separate sentences. Making two sentences is another very legitimate approach.

I wanted to tell her that her skirt was tucked into her pantyhose, but she went up on stage too soon.

The but means that the second part of the sentence is no longer something that could stand by itself. Therefore, with this you just use a comma.

Read more on ellipsis here.

8. Cut down on the parenthetical phrases

If you write a lot of parenthetical phrases (and you know who you are) , your readers will find it annoying  to have to keep ducking in (and out) of the main part of the sentence. Thus I strongly (but politely) suggest that if it's important to say, just say it.  Skip the parentheses. Try your sentence without the parentheses, and just use commas if necessary. If the parentheses aren't important to your writing, leave'em out.  Or consider whether the parenthetical phrase itself is necessary. Sometimes you can totally skip it.

Do use parentheses to partition off key information that, if presented normally, might interrupt the flow of the text. One example is using them to provide a definition for a word that might be unfamiliar.  

I'd say you could apply the same reduction advice to dashes, too. If it's important to say it—and it always is, isn't it?—then consider whether it needs to be set off—set off and emphasized—by dashes. Usually you can just use commas, or start a new sentence. Dashes can be disruptive and annoying to read when they show up a lot.

7. Remember the comma.

If you would pause speaking, then you'd probably pause writing it.

If you're going to give a public speech be sure that your skirt isn't tucked inside your pantyhose.

Bleagh. Too stiff.

If you're going to give a public speech, be sure that your skirt isn't tucked inside your pantyhose.

This is better and more natural.

Another comma issue has to do with a series of items. Here's an example. Some people say they will pick up eggs, butter and bread. Others like me will pick up eggs, butter, and bread. The comma before and is called a serial comma and many wars have been fought over which is better. It doesn't matter. Just pick a way and stick with it.

6. Few and less and more (but is less more?)

Few is for items. Less is for quantities. You can have fewer raindrops and less rain; you can  have fewer hairs but less hair.

Here's the tricky part—when quantities are reduced (fewer and less), the words are different, but when quantities are increased (more), the words are the same. More hairs, more hair.

5. Dangling participles are as bad as you've heard.

A participle is a verb ending in ing used as an adjective, as in the following sentence.

Feeling embarrassed about her haircut, Felicity hid in the closet.

That's absolutely correct. The participle comes first, and the noun in the next phrase is what the participle modifies. The phrase after the noun goes with that noun too.  Felicity was feeling embarrassed, and she also hid in the closet.

Here's how it works. The noun is a big fat greedy pig and takes the phrase before it, and after it, for itself. That means both phrases had better make sense with the noun you're using.

Here are some correct examples.

Sizzling happily,    the stew   smelled delicious. The stew is the one that is sizzling happily and smelling  delicious.

Beaming widely,    Jenny   accepted the Miss Linux crown. Jenny is the one who is beaming widely and who is accepting the crown.

What if you get a noun that doesn't work with both phrases?  This happens most often when you have an implied subject (examples follow) and another noun shows up for the party.

Belinda had had a bad day.     Being tired, the bed looked great.    
In this sentence, the bed is the noun so both phrases go with bed.   The bed certainly looked great but it absolutely was not tired.  Belinda is tired. You would rewrite this; one way is Belinda had had a bad day and was very tired, so the bed looked great.

George was depressed.    Having lost the election, the Bahamas were appealing.
George is the one who lost the election, not the Bahamas. You would rewrite this; one way is George was depressed after having lost the election. The Bahamas seemed very appealing.

4. Lay off using lie incorrectly

Lie is for what you do with your own body. Lay is for what you do to other things. Lay is also the past tense of lie, unfortunately, which makes things confusing.

Lie: I am going to lie down right now; I lay down yesterday.

Lay: I am going to lay my briefcase on the table. I laid it on the bench yesterday.

If you use lay, it had better be because you're currently taking something and putting it down someplace, or because you yourself, in the past, became horizontal. (Or because you're an attorney in the Enron trial, or you got lucky over spring break.)

3. Keep your intransitive verbs off my body

Some verbs are transitive (like lay), which means you can do them to other things. You can raise hell, you can raise your hand, but you can't just spend a day raising. Raise is a transitive verb.

Some verbs are intransitive (like lie), which means you just do them. You sleep. You dream. You don't sleep yourself, you don't sleep your bed—you just sleep.

Some verbs are both. You can just lie around the house eating, or you can eat a sandwich.

You probably know which is which; just pay attention and don't get sucked into using transitive verbs intransitively.

2. Wherever possible without sounding dorky, put only in front of the thing it applies to.

Is this sentence correct?

You only need to answer three questions to win the prize.

We don't really know without asking the writer. Since I wrote the example, though, I can tell you that the sentence is incorrect. The writer wants to say that while there are multiple questions, you need to answer only three to win the prize. So the statement isn't quite accurate.  Only applies to three,   not need.

If you put only in front of the thing it doesn't apply to, the sentence can be confusing.

Here's another example.

Only think about three questions.

Does that mean you should only think, rather than give the answer, or does it mean you just need to think about three of the questions?

• The way the sentence is written, only applies to think, which means you shouldn't respond aloud, or write down answers, or do anything else.  If that's the intent, the sentence is correct.

• If the writer wanted to tell the reader that there are multiple questions and the reader only needs to think about three, however, the sentence is wrong.

So put only immediately in front of what it modifies, unless it sounds really stupid that way.

1. Use the word that is correct (the correct word, which helps your readers understand you, is always a good choice)

When do you use which, and when do you use that?  This is another grammatical point that very much  affects the meaning of what you say.

Which is for additional information you feel like providing. That is for specifying one item among several.

Here are some examples and some more explanation.

Let's say you're in the middle of doing a jigsaw puzzle, and you want your friend to reach over and give you a specific piece. You would say “Give me the piece that has the star on it.” That reduces the choices to the one that matches the information in the phrase following that.

 

If, however, there were  only one puzzle piece left, you could correctly say, “Give me the puzzle piece, which has the star on it.” You're just talking about The Puzzle Piece, but mentioning, just because you think it might be interesting or informative, that it has a star on it. Which gives additional nice-to-know information. It doesn't restrict the way that does.

That would be kind of silly thing to say in this context,  since you don't really need to tell your friend that the only puzzle piece left has a star on it. She can tell. But it's correct. 

A better example of when to use which would be this sentence.

Rye bread, which is very nutritious, is an excellent basis for any sandwich.

Which is the kind of word you might use in a novel; that is the kind of word you'd more likely use in technical directions. Which is more on the pleasantly descriptive side; that provides important information.

The phrase you use which in is always surrounded by commas, as in the above rye bread example. Or the which phrase  might have a comma before it and a period after it, as in "Pass me the puzzle piece, which has a star on it."

Here's what I think you can ignore.

A lot of people make a lot of fuss about these items. I think they don't matter and you can just do what comes naturally.

• Ignore the rule about not ending a sentence with a preposition (up to a point). A preposition is anything, as I was taught in grade school, that a squirrel can be in relationship to a tree. In, above, beside, etc.
  
  That said, I think prepositions at the end are just fine if the sentence is clear, accurate, and understandable. If I tell you that I give up, you understand me.  Are you going to be one of those people who say “This is something up with which I will not put?” Of course not.
  
  Now, you might not want to say It's the bananas that I'm sick of. You would say I'm sick of bananas because it's more direct, vivid, etc. It's better writing. If you've got a lot of sentences ending in prepositions, that probably indicates some  awkwardness or excessive length. You could tighten up your writing to make it clearer and shorter by rewriting those sentences.
• Ignore the rule about not splitting infinitives. This is a stupid hangover from Latin. Split your infinitives. Tell the world that you're going to aggressively pursue learning to program in Ajax. (The infinitive verb to pursue is split in the middle by the adverb aggressively.)
• Just forget about forming plurals for words that end in ex, using ices. I think this is silly. In English, we form plurals with s or es—jobs, sandwiches, etc. So talk abut indexes, not indices, unless you go to work each day in an actual ivory tower.

To Learn More

If you want to learn more about grammar, buy The Deluxe Transitive Vampire : A Handbook of Grammar for the Innocent, the Eager and the Doomed . (As you see, she does not put a comma in front of “and”.) It's wonderful and hilarious.  http://www.amazon.com/gp/product/0679418601

For punctuation, see her other book, The New Well Tempered Sentence : A Punctuation Handbook for the Innocent, the Eager, and the Doomed . http://www.amazon.com/gp/product/0395628830

 

Using Vlookup() (or Hlookup()) in OpenOffice.org Calc spreadsheets, with a Data Validity dropdown list

For all you spreadsheet users: here's something kind of cool.

Let's say that you have a set of data. You have a list of items, and for every item that there is a unique item number, category number, and packaging type. (One row and three unique columns.)

Or you have been getting your home entertainment organized and you have a perfect system for throwing parties: for every main dish there is a specific drink, appetizer, dessert, and game.

Having the data isn't the trick. What the data lets you do is that elsewhere in your spreadsheet, you can type or select the first item from a list, and have one or more of the other associated pieces of data pop into the cells next to it. Select the main dish and you also automatically get the associated drink, appetizer, dessert, and game.  

You use =VLOOKUP() OR =HLOOKUP to do this.

Here's an example. I have this data. There are several columns but here are the first two.

Here's one thing about the data. Be sure to sort it. Sort it by the first column, alphabetically or numerically. Select all the data, choose Data > Sort, and sort as usual.

At another spot in the spreadsheet I can set this up so that when I type "Beans and rice" in cell C19, the formula here.....

will automatically display the right type of drink for beans and rice (that I have set up in the data set).

 

How does the formula work?

($C19;$A$10:$D$15;2)

The first part $C19 (the $ is just an absolute reference) is the cell containing the value that I want to look for in the FIRST column in the data set. In case the type of food such as beans and rice.

The second part is the range of data.

The third part is the column containing the data I want. I type 2 for the drink; 3 if I want to display the column containing appetizer information.

You can keep on going by adding more columns. Use the same formula but set it up so that the last argument (the column) is 3, 4, and 5 respectively.

 

and that's how this looks.

If you're thinking that typing the names of the dishes is a lot of work, especially if instead of six main dishes you had 122 part names or numbers, you're right. Ideally you'd set up a dropdown list.

Click in the cell where you want to display the first piece of data, the main dish. (You probably wouldn't make the lists and VLOOKUP positioned right next to the original data set; I'm just showing them side by side because it's simpler, and it's frankly easier to get screen shots this way. ;> )

1. Choose Data > Validity.

2. From the type list select Cell Range, then type an absolute range (with $row$column format) as shown, around the column of labels.

 

2. Click OK.

Then click the little tiny black handle in the lower right corner of that cell where you made the list, and drag it down to put in a list in other cells too.

Now you can just select something from the list, and all the corresponding info, from the data set, will appear in the cells where you've also put the VLOOKUP formula.

(HLOOKUP is the same, but rotated 90 degrees.)

Getting rid of the blanks in a spreadsheet column or row, and more

Note: As Karl pointed out in a comment, this is actually a complex solution. It is a good solution for two or more columns, but if you have one column as shown here, the simplest approach is to just sort the column and the blanks and cells with one space in them will be grouped together. Delete those cells and you get the same result.

I'm going to a big blues dance event this weekend. The people signed up are listed on EventBrite, so since I wanted a list of people coming, I copied the list and pasted it into OpenOffice. But when I pasted the list to a spreadsheet, I got a bunch of blank cells. I want to have a nice singlespaced list of the names.

 

 By the way, Edit > Paste Special, Skip Empty Cells, won't do what I want. It's a badly phrased option and has to do with whether you overwrite when pasting.

Anyway--so to remove the empty cells I selected the column and chose Data > Filter > Standard Filter. I made sure that the Name column was selected, left = selected, and selected Not Empty.

 

I clicked OK and....voila. Voi not, that is. 

That pesky EventBrite site puts in a space, just a single space, in some but not all of the empty lines. Why? Well, why not. I discovered this by clicking in the remaining seemingly empty cells, and yep, there was a blank space.

 

So now I need to screen out not only stuff that's not empty, but stuff that doesn't have a space as the only content. That's a little hard....and I don't want to search and replace to remove spaces, since then I'll end up with TrentPrice instead of Trent Price.

I went back to Data > Filter > Standard Filter and filtered out everything that STARTS with a single space. 

 

And that worked great.

 

Be careful of "templates"

I've been writing about how you can use free templates created for Microsoft Office in OpenOffice.

BE CAREFUL WHAT TEMPLATES YOU PICK. SOME OF THEM ARE WORSE THAN USELESS.

This has nothing to do with what software you're using -- it just has to do with being careful.

I was just looking around for an example to use and came across a template chock full of nested numbering that DIDN'T USE THE NUMBERING TOOLS. The numbers and letters were all typed. 1, 2, 3, so that if you hit enter at the end of an item, you'd have to type in the next number, 4. If you needed to insert an item in the middle of a list, you'd need to RENUMBER EVERYTHING AFTER IT.

So be careful what you pick. Just because it's called a template doesn't mean it will help.

Free Microsoft Office templates for Word, for use with OpenOffice Writer

As I posted yesterday, you can use OpenOffice.org with most of the templates out there. Writer/Word, Calc/Excel, and Impress/Powerpoint. Also remember that OpenOffice opens .docx files without any extra help.

So google away, find your Word or Excel template, download it, and follow the steps in yesterday's post to save it in OpenOffice template format in your own template collection. Here's one of many sites. http://www.docstoc.com/

Here's the only difference: it's after you've saved it. Instead of being able to access it the way you do with Impress, you choose File > New > Templates and Documents to get it.

 

Then you'll see a window with all your templates. It might be in a folder of templates like My Templates or it might be at the top of all your folders. You can tell by the info in the title bar. The template will be in the folder you picked when you chose File > Templates > Save. Then just double-click and you're good.

 
 

Quick new presentation templates to use in OpenOffice Impress

As you know, OpenOffice doesn't come with the dazzling depth or array of templates that Microsoft Office users get. However, here's the thing. 

A) Templates aren't software. You can get great free software, without many templates, then go out and get the templates separately, also for free.

B) You can use Microsoft Office format templates to your heart's content in OpenOffice.

So here's what I just did.

1. Googled free Microsoft Office templates.

2. Picked this of the many results. http://www.presentationmagazine.com/free_powerpoint_template.htm

3. Downloaded this one (a dear friend just got married to the man I set her up with -- it seems an appropriate choice. :) )  http://www.presentationmagazine.com/wedding_powerpoint_template.htm

4. From OpenOffice, chose File > Open and opened the downloaded file.

 

5. Now, I can just use it, save it as a presentation, do whatever I want at this point. However, if I want it in that nice little catalog of templates that pops up when I create a new presentation....

I choose File > Templates > Save and name it, then click OK.

 

6. And now (without restarting or anything) when I choose in OpenOffice File > New > Presentation, that wedding template is right there in My Templates.