OpenOffice.org Training, Tips, and Ideas

Searching and replacing for carriage returns, tabs, and other characters, using regular expressions

Call them carriage returns, line breaks, paragraph marks, whatever, sometimes you want fewer of them. Maybe you've brought in some ASCII text that had a line break or two after every paragraph and now with formatted text you don't need it. Or you're turning a spreadsheet or database into text or vice versa.

At any rate, it would be nice to use the Find and Replace window to quickly find'em and change them to whatever you want: nothing at all, or the phrase "el elegante" or whatever.

Note: If you're a macro kind of person, see this page on the ooo forum.

Searching and Replacing, Step by Step

In your OpenOffice.org document, choose Edit > Find and Replace or press Ctrl F. The Find and Replace window will appear.

In the Find and Replace window, enter the symbol for what you want to search for, in the Find field. Here's a quick reference to the symbols to enter for what you're looking for.

• Regular carriage returns  are $
• Soft returns inserted with a Shift Return, are \n
• Just an empty paragraph, i.e. a carriage return but with no text on that line, is ^$
• Tabs are \t

In the Replace field, you typically don't enter anything since you're probably just trying to get rid of whatever you're searching for.

• If you want to replace something with a carriage return, put \n in the Replace field.
• If you want to replace one carriage return with two, put \n\n in the Replace field.
• One thing--you can't replace something with soft returns. As you see, a \n in the Replace field turns into a normal hard return.
• Just use \t normally, in both the Search and the Replace fields, for a tab.

Once your Find and Replace fields contain what they should, click the More Options button. Select the Regular Expressions checkbox. This will make the program look for what those codes represent, rather than literally those characters.

If you're using a mix of regular expressions and normal characters, you might need to use a \ in front of anything you want evaluated normally. For instance, if you really are looking for the symbol $ but you want to replace it with a carriage return \n, then you need to actually search for \$ in the Search field and replace it with \n because $ is a special character.

This illustration shows you're looking for a carriage return (any carriage return), and you're going to replace it with nothing.

Click Find. The first instance (from where the cursor was) of the thing you're looking for will be highlighted.

Click Replace to do the replacing.

And so on. Keep going until you're done. Use Replace All only when you're absolutely positive you'll get the results you want.

---

---

---

New book about a Medicare whistle-blower

My cousin's son-in-law book about his whistle-blowing experience is out today!

The title is Undercover and it's by John Schilling.

"When John Schilling, an unassuming mid-level accoun­tant, went to work for the Columbia Hospital Corporation, he never expected to become the catalyst for the series of "whistleblower" cases that ripped through the healthcare industry in the late 1990s. But when he unwittingly discovered that the company was siphoning billions of dollars away from Medicare and stealing from American taxpayers, he was faced with a choice: Speak up for what he believed to be right, or remain silent. Undercover tells the story of Schilling's harrowing journey from ordinary citizen to federal informant. The book recounts how Schilling allied himself with the FBI and the Justice Department and--unable to confide in friends or family--journeyed into an undercover world in which he carried a wire and mapped out offices for secret government raids. Suspenseful and provo­ca­tive, Undercover chronicles Schilling's nine-year ordeal that eventually led to the resignation of high-level executives and forced Columbia to return $1.7 billion dollars to the federal government. A compelling account of one man's decision to risk everything for the greater good, this book reveals the personal side of a thankless role that resulted, ultimately, in justice."

My family pretty much just has nice basic jobs, drinks coffee, and occasionally gets wild and crazy and organizes a family reunion. ;>  This is huge and amazing. I'm very excited, and I hope that anyone who likes to see justice triumph over power will enjoy reading it.

Sorting data in OpenOffice.org Calc (repost)

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.

---

---

Direct book sales are on again

See this URL.

http://openoffice.blogs.com/bookresources/2008/04/ordering-the-op.html

Solveig

Some of my favorite OpenOffice.org features (repost)

(This is a repost of an older entry. The features are still the same, but the windows will look a little different in the current version.) 

 

Everyone's got their favorite features that make life easier, more fun, or both. Here are a few of my favorites.

Making PDFs Straight From OpenOffice.org

One of the greatest, most convenient features is the ability to make Adobe Acrobat PDFs from any OpenOffice.org document. Why is this cool? Because anyone can read a PDF document no matter what software they have (since everyone has the little program for reading PDFs). It’s the perfect way to send a document to someone else, if they don’t need to change your document.

Just click on the handy PDF icon on the top of your work area, give it a name and specify a location for the PDF file, and click Save. Then email that mydocument.pdf or quarterlyreport.pdf document.

If you want more control over the PDF, choose File > Export as PDF, name the file, then set options in the next window.

Send Document as Email

To make it even quicker, choose File > Send > Document as PDF Attachment. That starts your mail program, creates a new mail document, AND attaches a PDF of your current document to that email. It really doesn’t get much slicker.

Or if you don't need a PDF, just choose File > Send > Document as Email.

Paste Icon for Inter-Text-Document Pasting

When you're pasting from Microsoft Office to OpenOffice.org, or from OpenOffice.org 2.0 to OpenOffice.org 1.x, you're not going to get text. You're going to get a frame of text or an icon object.

What I've done to get around this is to use Notepad as an intermediary. But it's a whole lot easier to use OpenOffice.org's multiple paste format features. Either choose Edit > Paste Special and choose unformatted or formatted text, or just click on the paste icon and choose your option. Formatted Text (RTF) usually works just fine.

Make the Icons a Decent Size

Choose Tools > Options > OpenOffice.org (or StarOffice) > View and make sure the icons are large enough. I like to see my icons, not squint at them.

The List Object Bar and the List Formatting Window

I used to curse the indenting and levels of OpenOffice.org lists until I started using the specialized object bar. Choose View > Toolbars > Bullets and Numbering, or just make a list and the floating one pops up.

Also, when you're developing complex list formatting, always use the numbering/bullets formatting window (Format > Bullets and Numbering) and use only the last two tabs, Options and Positioning. These are the only tabs that let you specify specific formatting that's different for each level.

Make Shortcuts

This was in my configuration blog too but I really like it. The same tab where you turned off word completion (Tools > AutoCorrect, Replace) 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.

Just choose Tools > AutoCorrect and click on the Replace tab. 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. Click New, then click OK. In your document, type the shortcut, followed by a space, and your word will appear.

Making My Own Color and Other Fills

I might be pickier than most about the color I want for drawings, but for one thing, I don't like the greens that come with OpenOffice.org. Too dark or too limey. So I make my own.

Choose File > New > Drawing, then choose Format > Area and click the Color tab. Or just choose Tools > Options > OpenOffice.org (or StarOffice) > Colors.

Click Edit and fiddle til you have the color you want.

Click OK, then type a new name in the main color field and click Add. The new color now shows up in all color lists including the font color lists.

Following the navigation for doing this in Draw, you can make your own gradients, hatches, and bitmaps in the same window.

3D Shapes

In Draw and in Writer, you can use the 3D shapes to, well, draw 3D shapes. Even better, you can make them intersect. Draw two shapes, select one, cut it, select the other one, press F3, and paste. Then move them together.

 

To change their orientation, click on each shape once to get green handles, then again to get red handles, and swivel them around.

I'm not sure how useful this is to everyone but it sure is cool.

You can also convert any item to 3D, in Draw. Choose File > New > Drawing and create any shape, even text. Then right-click on the shape and choose Convert > To 3D. Shazam.

---

---

Linux Comic Strip!

Check it out. The article

http://www.rlhc.net/blog/2008/04/15/linuxcom-comic-strip-aims-for-a-fun-way-to-educate-new-linux-users/

and the strip.

http://www.hackettandbankwell.com/

Hackett and Bankwell #1

Synopsis: Woody Hackett learns from his business partner, Jerome Bankwell, that they are the new owners of a documentary production studio that still uses Mastersoft, and that he will need to travel to their facilities in the desert in order to get them setup with Ubuntu Linux. At "Interplanetary Pictures," Woody shows their crew how to get started using the Ubuntu GUI following an installation. Guiding them through some basic software installation, Woody demonstrates to Kaori Soto and her associate Calvin Green basic ideas of GUI operation, so that they can use what they've learned to install other programs they might need down the road.    Areas covered in first issue

• Preview Hackett and Bankwell #1 Here (.pdf)
• Order Hackett and Bankwell #1 Here

Diggin' DimDim: Open Source Multi-Platform Web Collaboration Software

I think I've found the software I'm going to use for online training, seminars, and one-on-one tutoring and support. It's in Beta, but it looks really good.

DimDim has the key things I need:

- supports Windows, Linux, and Mac (not sure if Linux or Mac can be the host)
- supports sharing a presentation, or my desktop, or a collaborative whiteboard
- supports audio and video (with some webcams)

and is free for up to 20 participants. Click here to see pricing for over 20.

No download for the participants, just the host. They use Flash.

There's a "Collaboration" link on the client side that just processed for a while and didn't give me anything. I'm desperately hoping that that is a to-be-implemented feature that lets me manipulate files on client computers or vice versa. That's a huge, huge nice-to-have for me, bordering on essential. I don't know for sure that it doesn't exist, but I haven't found it yet or found info on it since I'm not sure how broadly to interpret the word "share". I'll update this post when I find out.

Recording and archiving is coming.

Feature list.

I am seriously impressed. I've fallen too soon for other software that turned out to not have what I needed (and plenty that had what I needed but for a robust price), so I'm taking it slow this time. But I think Dimdim Is the One.

Here's what the main window looks like for the presenter. I tried it on two client computers, Windows XP and Novell Suse (that's the gibberish in the chat window that I typed just to try it out).

Audio worked with my Creative Live! Webcam with voice, video didn't.

Sign up if you want to host meetings through DimDim.

Seminars, Support, Remote Training

I want to mention again my ongoing plans to provide all sorts of services through collaboration software, hopefully DimDim. These are in the near future; rates will be announced with the services.

Support -- Got an issue you want help figuring out? Send a request to my calendar for a time, upload the file to the collaboration software or email it to me, and I'll help you with the problem document or task.

Tutoring -- Want to learn something, like mail merges or styles or pivot tables? Just make an appointment and you'll get one-on-one attention.

Remote custom training for groups -- If you just want a few hours of training at a time (which makes it less practical to fly me in) or you have employees in many locations who need training, remote training makes a lot of sense. Contact me to plan topics and dates. We can use DimDim or your own collaboration tool such as WebEx, GotoMeeting, or ReadyTalk.

Regularly scheduled seminars -- I'll ask for requests, then schedule short (half-hour to two-hour) seminars on scheduled topics. Costs for these per person would be lower than signing up for tutoring.

Regularly scheduled free seminars -- I'll schedule free half-hour or hour-long seminars periodically on popular topics like mail merge, configuration, considerations for planning a transition, etc.

---

---

Un-displaying the grid lines in OpenOffice.org Calc

Tired of those pesky gridlines being displayed onscreen? Here's how to turn them off.

Thanks to Marek for pointing this out -- I like to consider myself the queen of "it's under tools > options" but I only looked under the View menu before resorting to hack measures.

Choose Tools > Options > Calc, View. Deselect Grid Lines. You can change the color of the grid lines here, too.

Note that by default, they don't print. To make them print, choose Format > Page, Sheet tab.

---

---

Attaching a Document (Including Templates) to an OpenOffice Menu Item or Toolbar

This is huge. Thanks to TerryJ on the users@openoffice.org alias for pointing the way.

So you're an IT director. You want to make things easy for your users (and thus easy for your team so they need to answer fewer questions). One way to make things very easy and simple for your users is to create templates.

• Create a template that has no page number on the first page and a page number, starting at 1 or 2, on the 2nd page. (A user could create this but it involves understanding styles and is just way more difficult than it needs to be.)
• Create a template that has a watermark background like Confidential.
• Create a template that has whatever characteristics are needed for a department's tasks.

Now, you could create a template and just set it as the default template so that users get that template when they choose File > New. But if you have multiple documents they need to make, you probably don't want to do that. Or you can do that, but you have more than one template you want to offer.

You could just put your templates in a nice centralized location for your users. However, some users don't view it as all that convenient or obvious to choose File > New > Templates and Documents, the pick the right category and the right template.

Here's how to make it easy, and thus likely, for users to use the templates you provide for them, and thus for them to not even enounter problems, or encounter tasks that are harder than they need to be.

Make a menu and attach templates to menu items on that menu.

Now, this means more up-front work for you, but then it's done.

A)  Make your templates and put them in a central network location. Be sure they're in template format, not document format.

B. Create a new menu.
Choose Tools > Customize, click the Menu tab, click New, then name it and click OK.

C. Now you just need to add menu items that point to the templates.

First, create the macro.

1. Choose Tools > Macros > Organize Macros > OpenOffice.org Basic.
2. This window will appear. The macro I already created is listed there. In this example I'll create another one that does the same thing.

3. In the Macro Name field, type the macro name like OpenTemplate.

4. Click New.

5. What you now see in the macro editing area will depend on what was there last. I'm not incredibly familiar with the macro window but I know that what I did worked ;> so you should be able to follow these directions successfully.

Regardless of what else is in there, you want to have the following. Find the part that says 
Sub macroname
End sub

and put your macro in the middle.  Leave all the other macros that are there.

6. Now you want to write the following macro. There are variations here but this is what I did that worked.

I show an example first, then syntax.

Example

Sub OpenTemplate

Dim sUrl as String

sUrl = "file:///C:/openofficemacros/sampletemplate.ott" ' note you must use url format
If NOT FileExists( sUrl ) Then : Msgbox( "No file named " & sUrl ) : Exit sub : End If
StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, Array() )

End Sub

Now the syntax

Sub name of macro

Dim sUrl as String

sUrl = "file:///path to file you want to open"'    ' note you must use url format
If NOT FileExists( sUrl ) Then : Msgbox( "No file named " & sUrl ) : Exit sub : End If
StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, Array() )

End Sub

 7. Click the Save icon and close the macro.

8. If you choose Tools > Macros > Organize Macros > OpenOffice.org Basic again, you'll see the macro name. Here the original one I wrote and the one I added for this example are both shown.

9. From here, you can either:
Select the macro to use and click Assign
OR
Close the window, then choose Tools > Customize, and in the Menus tab,  select the menu you created.

10. Click Add next to the empty list for menu items

11. In the window that appears, expand the Macros category at the bottom, and select the macro you wrote.

12. Click Add.

13. Close the window.

14. Select the item, click and hold down on Modify.

15. In the window that appears, rename the item however you like and click OK.

16. Click OK in the Customize window.

17. Now choose the menu and the menu item.

18. The document you created appears. If you created it in template format, then it will be an untitled copy of the document.

D. Copy the configuration to other computers.

1. Copy the macro to the same location on other computers.

2. Copy the configuration file where the menu and menu items are stored to the same location on other computers. It's called menubar.xml; there are several depending on what the menu is for. Here's where it is on Windows for a Writer menu; for Calc it would be scalc\menubar\menubar.xml, and so on.

<openofficeinstalldir>\soffice.cfg\modules\swriter\menubar\menubar.xml

Here's what the content looks like in the menubar.xml file.

<menu:menu menu:id="vnd.openoffice.org:CustomMenu1" menu:label="Use These Templates">
  <menu:menupopup>
   <menu:menuitem menu:id="vnd.sun.star.script:Standard.Module1.OpenTemplate?language=Basic&amp;location=application" menu:helpid="vnd.sun.star.script:Standard.Module1.OpenTemplate?language=Basic&amp;location=application" menu:label="Use this for meeting minutes"/>
  </menu:menupopup>
</menu:menu>

See this link for the original explanation.

---

---

OpenOffice! The Musical! (Friday fun)

There isn't one yet.

But how much fun would it be to follow these folks' lead?

http://improveverywhere.com/2008/03/09/food-court-musical/

Do it in a big office building somewhere, or in a mall, maybe outside the Mac store.

The big "I wish" song that starts off every musical, some ingenue gets up and sings, let's see.

Word 07, what a shill
Just money making for ol'Bill
My numbered lists are strange and wrong
It makes me break into a song....

And so on, and then the cartwheelers come along, and the chorus sings about how they wish there was something free and open and that had built-in PDF (what rhymes with PDF?), etc.

Coverage on the evening news, of course.

Just a thought, as I end my lunch break and prep for this afternoon's Advanced Calc class.

---

---

Creating a dropdown list in OpenOffice Calc that references a list of values (repost)

I just started using feature this recently, for my LinuxWorld presentation. It's very nice. Anytime you can reference some text, rather than embedding it in the formatting, it's good.

Some background: under Data > Validity, you can control what people can enter in spreadsheets, and offer them help in the form of lists, help tips, etc.

I wrote about the Validity tools here

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

and elsewhere on my blogs. However, what I haven't talked about yet is how to make a dynamic list.

Let's say you've got a spreadsheet documenting the year's performance for the 120 people in your company, one sheet for each person. They're summarizing what they've done this year, and you want them to list the projects they've worked on. You would like to list the Official projects rather than having them type in whatever. So you want a dropdown list. Howeve, you don't want to have those projects typed in that dropdown list because then if you change it, or use this spreadsheet next year, you'll have to correct the list 120 times. (You can copy and paste, but still.)

So you make a list of the projects that references a cell range in a sheet called Projects, say, and all you need to do is update that one cell range to make all the lists update.

Here's how you do it.

1. Click in the cell where you want the list to appear. If you want the list in multiple cells, select multiple cells.

2. Choose Data > Validity.

3. Select Cell Range in the list.

4. Type the range. To type a range in the same sheet , type something like this:

$F$1:$F$20

You need the $ to make the reference absolute.

If the range is in a different sheet, add the absolute sheet reference in front, like this.

$Projects.$F$1:$F$20

5. Click OK.

Then if you need to, just change the contents of the cell range and the list updates. Previous entries in those spreadsheets containing entries no longer in the list do not change.

---

---

Personal Information Manager (PIM) on the roadmap for OpenOffice.org

Here's the article.

"In 2006, a Personal Information Manager (PIM) has been added to the roadmap of OpenOffice.org. Users constantly asked for this feature, and it's still seen as a key missing piece towards a full OpenSource Office solution. The PIM targets to complete OpenOffice.org's productivity offering by providing an integrated E-Mail, calendaring and addressbook client. The roadmap presented at OOoCon 2007 lists it as one of the highlights of the upcoming OpenOffice.org 3.0 release."

I definitely get a lot of questions about this. Of course, Evolution or Thunderbird and Sunbird are current options.

Read more.

---

---

Selecting two or more drawing objects in an OpenOffice Writer document

I have been spending some time under Tools > Customize, Keyboard tab, looking through the features. I was looking at the Writer features for 2.4 when I came across this little item.

Gosh, I said to myself, could this be the option, very well hidden, that would let me multi-select objects in Writer?

Because, as you may have experienced, it is difficult to select two or more objects at the same time using Ctrl or Select.

Well, not quite. You still can't select two or more pictures or frames at the same time. But Ctrl + F8 does let you select two or more drawing objects at the same time. Select the first one, hold down Shift, and select the next one.

---

---

Download your OpenOffice.org 2.4 and check it out

http://www.openoffice.org/

Check out the nice web site redesign, too.

---

---

Another way to do labels, online

Try this. I haven't worked with it but a reader says it's quite simple, and free.

If you're looking for info on labels with OpenOffice.org, it's all under the Labels category; try also Mail Merge.

---

---

x

Off topic: Monty Python Fans, Rejoice

This isn't the first post you've seen on this. And I regret that so many people are still waiting for their bags, stored somewhere in and around Heathrow.
http://www.boston.com/business/articles/2008/04/02/fedex_to_sort_out_heathrow_baggage_mess/

But how great is it that there's already a song just for those who are, well, worried about the baggage retrieval system they've got at Heathrow?

I get too much of a kick out of it not blog about it.

Using Names to have a more readable reference to items in formulas

Who doesn't love a formula like this?

=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)

That's all very well, but how to do you add the names?

You can assign any name you want by selecting one or more cells and choosing Insert > Names > Define.

 

Type the name, click Add, and click OK.

Then you can create formulas like this.

Note that you can't do this:
=Ement+Admit

because those names refer to ranges. You can't do  =Ement+Admit because you can't do =B17:B20+E17:E20

You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit  (total Entertainment Expenses plus Total Admission Expenses).

 

There's a quicker way to get all your columns named. Let's say you've got this data.

Select all the data and choose Insert > Names > Create. In this case it makes sense to select the checkmarks for top row and left column since that's where the labels are. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals, for instance, like this. Note that when you're in a formula and you start to type a name, it will appear in the Formula field as Prepaid_Taxes does here. Press Enter to accept it so you don't have to type the whole name.

As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let's say that you've got all these amounts, Bus exp and Salary etc all over on different sheets or farther apart, so you would have to do the + instead. )

and get a result.

Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

---

---