Tips, tricks, tools, and techniques for Microsoft Office

The issue you're reading now is the last weekly newsletter from The Office Letter, but it's not the end of our tips. Beginning in 2008, we'll be publishing tips on a monthly basis, in the same format you're used to -- but those tips will be online only. To learn how to keep abreast of our publishing schedule, please see the next article.

While managing the mailing list, site, and day-to-day operations has been a one-man affair, I've had considerable help in making The Office Letter the great collection of tips it has become. I would like to thank:

• Dick Archer, who was with me from the time Office Letter hadn't even been named or a site designed

• Jim Boyce, who jumped in to help with dozens upon dozens of Outlook tips and tricks

• Steve Hansen, who has written some of the easiest-to-understand columns on VBA I've seen anywhere

• Our advertisers, sponsors, and supporters, including Microsoft, CreateCDLabels.com, Addins4outlook.com, and others who kindly provided products to give away in drawings for our Premium Edition subscribers

• The Hosting Group, which has provided our Web hosting

To all our readers who have written with their questions, and, more importantly, with their tips and answers to the problems we've published from other readers, thank you.

To our Premium Edition subscribers, who helped monetarily, a special thanks for keeping this operation afloat.

It's been a rewarding six and a half years. I appreciate all the notes of gratitude and best wishes. You're very kind.

Jim Powell
Editor
The Office Letter

^ Top of page ^

Thinking about holiday gift ideas? Give the gift that the Outlook pros use: a time-saving add-in from Sperry Software that makes life in Outlook easier than ever. And we're making it easier than ever on you -- it's buy one get one free season! Choose any one of our 36 powerful add-ins, and get another add-in at no cost. And if you choose to order it on CD, you can even have it gift wrapped! Visit    http://www.SperrySoftware.com to get started.

^ Top of page ^

Although this issue marks the end of our weekly newsletter, we will continue to update our site with new tips and product reviews, and answer reader questions, every month.

There are several ways to stay on top of the new content. Adding The Office Letter to your list of browser favorites is one way. Another is to use our RSS feed.

"RSS feed" is a fancy name for a special file that includes headlines, a brief description, and the date and time new tips are published. The Office Letter's RSS feed

   http://www.officeletter.com/tolrss.xml

can be added to any RSS feed reader; there are many commercial, shareware, and freeware options that only need to know the URL of the feed you want to monitor.

For a description of how to use RSS feeds with several leading free products (such as custom Google, MSN, and Yahoo home pages, visit

   http://www.officeletter.com/usingrss.html

If you have your own Web site (or want to publish links to our tips), you can learn how to add our tips to any page here:

   http://www.officeletter.com/red/rsssetup.html

^ Top of page ^

As this column approaches the end of its planned life, I suppose it is fitting to discuss the different ways that you can distribute your VBA application. While distributing your application is one of the last tasks you perform, choosing the distribution method is something you should think about at the beginning of the development process.

There are three primary forms that a VBA application can assume: a document, a template, or an add-in.

The majority of the VBA applications I've created have been distributed as a generic document (i.e., Excel workbooks or Word documents). One of the advantages of this method is that other than saving the document and sending it to the folks who want to use it, many times you don't need to do anything special. As long as your users can open the workbook, they can use the functionality it supplies, provided it passes the Microsoft Office security checks.

Another distribution option for your solution is a template. This is useful for applications that provide standard functionality on a repetitive basis and the desired outcome is a document of some sort -- things like expense reports, capital expenditure requests, and trouble-tickets. Templates require a little more planning when it comes to distribution -- especially if it's being distributed to a large number of users.

You might consider creating a utility or setup program that installs the template to the proper template directory (or provide documentation on how to do this manually). Likewise, you'll probably need to provide some minimal guidance on how to create a workbook/document based on a template. Keep in mind that not everyone knows how to use Excel or Word near as good as you do.

Note that there is considerable overlap between what you can do with a document versus what you can do with a template. For example, an alternative to using a template is to distribute a standard document that contains a user interface element that says something like "Create New <your document>". When you click on this UI element, you can provide a way for the user to name the document, select the file location, and then save the document using the new name and location. There are minor differences in how this works versus a template, but the end result is the same.

Distribute your application as an add-in when you have functionality that is not specific to a single type of document. For example, if you develop many user-defined functions specific to your business and want to use the functions within many workbooks, create an add-in. To make an add-in. All you need to do is save the workbook as one. Prior to saving it, you should lock the VBA project so that people can't view the code that implements your add-in.

After you've created the add-in, you can send it to users and have them select Tools/Add-Ins from the Excel menu, click Browse, and then locate and select the add-in file you provided. Alternatively, you can create a utility or setup program that installs the add-in automatically for users.

One benefit of using an add-in is that the structure of the workbook is not visible to the user. Workbooks and templates must have at least one visible worksheet; however, this does not apply to add-ins. There is no way for users to modify an add-in unless a user modifies the workbook with which the add-in was created.

Happy holidays!

-- Steve Hansen

^ Top of page ^

All plans include 24/7 technical support, unlimited e-mail, state-of-the-art Web site builder, control panel admin, FrontPage and ASP support. Domain names just $12.95. No contracts required, and we'll set you up for FREE in under 20 minutes. Sign up today at:

^ Top of page ^

Competitors have been building online sites that enable you to share and edit Office documents, but all have some limitations -- features within Word or Excel that they don't support (such as charts), making them less-than-perfect solutions.

Last week, Microsoft revealed its tool for sharing Office documents with friends or colleagues -- or just with yourself, if you need access from multiple computers or multiple locations. While online file-sharing services, e-mailing files to yourself, or saving files to USB drives are approaches to saving files, Office Live Workspace (OLW) is more efficient. Furthermore, it allows users to preview Office documents even if the computer you're using doesn't have Office installed. Best of all, it's free.

The free service requires only a Windows Live ID (an MSN Hotmail, MSN Messenger, or Microsoft Passport account) to enroll. Once you've signed up and Microsoft initializes your workspace (called the Documents workspace), you get a simple work area that makes it easy to add documents to the workspace and individually make files available to others (see Figure 1).

Figure 1

After you upload files to your workspace, you're ready to invite colleagues to view and/or edit them by selecting the file and entering their e-mail addresses. (The trick is that your colleagues' e-mail addresses must match the addresses associated with their Windows Live IDs, so this takes a little up front planning.) Invitees must log on with their own Microsoft Live ID and password in order to view or edit documents you've shared. (They can also then sign up for their own workspace if they wish.)

In addition to Word, Excel, and PowerPoint files, you can create a brand new file type within your workspace. A "List" file is perfect for creating simple columnar lists (see Figure 2). (One of the most popular uses of Excel is for maintaining lists, the company told me several years ago.) Each column you define can be text, multi-line text, a number, yes/no, or a date. You can sort the data by any column, and you can export the data to Excel.

Figure 2

OLW also includes predefined List templates for quickly adding a task list, contact list, or event list. (The task list includes task title, due date, and check boxes for "high priority" and "completed" fields.) You can sync any lists created with these templates (which are people- and task-oriented) with Outlook 2003 and 2007 (information is stored in separate Outlook folders).

The program also lets you upload other file types (such as JPG and HTML) but not those that could be harmful (such as executables). You can share and view these non-Office files but not edit them. Furthermore, Microsoft checks uploaded files for viruses and other vulnerabilities during the upload, a step that didn't seriously impact performance.

A final new file type for your workspace is a Note -- a simple text document, suitable for quick and dirty documents (think Notepad documents with minimal formatting for creating text files -- just perfect for jotting down notes or ideas).

MULTIPLE WORKSPACES

You can have multiple workspaces; I created one to use exclusively for business documents and another for personal files. Microsoft has defined several workspace types (besides "blank" for creating an empty workspace) that will add several default documents at the same time. For example, the Class type (for students) pre-populates the workspace with a contact list, outline file for an essay, and more). An Event workspace template includes files for creating invitations, flyers, agendas, and attendee lists, among other things; it's a good choice for creating quick to-do lists or project task steps, which is often sufficient when a team of workers needs to see the progress of individual tasks.

Other workspaces are suitable for tracking household chores such as creating a grocery list), job searches (with interview schedule, cover letter, and resume template), Meeting (for agendas, invitee lists, and more), and six others.

You can delete any default documents and move files between workspaces with ease.

Your additional workspaces are slightly different than the default Documents workspace, however. Files within the Documents workspace can be shared individually. Files within additional workspaces cannot -- when you share a workspace, you automatically share all files within it. That can make your Documents workspace rather messy if you need to share different files in many ways -- OLW doesn't support hierarchies (such as folders on your hard drive) to keep the display of the Documents workspace tidy.

When you grant permissions to others, you specify their access (editors have full access, viewers can only see the file). After logging in, a user can view the document, but to edit it a user must open the document in the originating application (e.g., Word), which is easy to do from the workspace itself. (As you might gather, OLW isn't like other online applications -- you can't edit a Word document unless Word is installed on your system.)

If more than one person edits the document simultaneously, all will see edits anyone makes in real time. To use this feature, you must install Microsoft's SharedScreen feature; a link within OLW makes that easy.

USING A WORKSPACE FROM WITHIN OFFICE

You can work more efficient by installing a special add-in that works within Word, Excel, and PowerPoint (for XP, 2003, and 2007). The setup adds a toolbar that lets you open or save documents directly from and to the workspace (see Figure 3). It's almost like having your workspace as a giant online storage area, with the added advantage of being able to share your files. The toolbar makes the whole process as transparent as you could want.

Figure 3

When you open a file from your workspace in Word, Excel, or PowerPoint, your Office application provides a Shared Workspace Task pane that gives you document information, such as who last modified the file and the date and time of that modification. Editors can add comments about files as well, so annotations of interest to others are easily viewed. You can create a version history and restore your copy of the document to a previous version.

The layout of the Workspace looks similar to SharePoint, but it's really SharePoint Extra Lite. You can't set up a sharable calendar, for example, and you can't control the layout. There are no alerts to tell you when someone has changed a document, and you can't compare versions of a document.

You can sign up at http://officelive.microsoft.com, but Microsoft won't immediately grant you access. It's wisely working through the queue of those enrolling once the company has sufficient capacity to make sure performance is acceptable to everyone. Even so, in its initial stage, performance varied. Sometimes it was slow to load, but once you've reached your workspace, performance is quite good.

If you've been looking for a way to collaborate with others by easily sharing and editing files with colleagues or friends, Office Live Workspace works well. It's free and currently ad-free, though Microsoft says it may add advertisements in the future.

-- James E. Powell

^ Top of page ^

Access 2003
http://office.microsoft.com/en-us/access/HP030898861033.aspx

Access 2007
http://office.microsoft.com/en-us/access/HP100913941033.aspx

Excel 2003
http://office.microsoft.com/en-us/excel/HP052037811033.aspx

Excel 2007
http://office.microsoft.com/en-us/excel/HP100738481033.aspx

Outlook 2003
http://office.microsoft.com/en-us/outlook/HP030842231033.aspx

Outlook 2007
http://office.microsoft.com/en-us/outlook/HP012303961033.aspx

PowerPoint 2003
http://office.microsoft.com/en-us/powerpoint/HP051955191033.aspx

PowerPoint 2007
http://office.microsoft.com/en-us/powerpoint/HP101547101033.aspx

Word 2003
http://office.microsoft.com/en-us/word/HP051866641033.aspx

Word 2007
http://office.microsoft.com/en-us/word/HP101476261033.aspx

^ Top of page ^

Create and print beautiful, professional-looking CD and DVD jewel case and CD labels (even MP3 or Video CD labels) in just minutes! Say goodbye to the handwritten marker look; forget expensive graphics programs. CD/DVD Jewel Case & Label Creator from CreateCDLabels.com works with every type of paper or CD/DVD label -- matte, glossy, clear, and even colored. It's simple to make both front and rear CD jewel case covers with side spines as well as CD/DVD labels, DVD covers, envelopes and more -- all in one package!

For an up-close look at the program, visit

   http://www.createcdlabels.com

On Friday, December 21, two lucky Office Letter PREMIUM EDITION subscriber will win a copy of CD/DVD Jewel Case & Label Creator, a $20 value.

Full disclosure: The Office Letter receives no compensation for the mention or distribution of this product.

^ Top of page ^

This week Premium Edition subscribers are learning two clever tricks for building SmartArt diagrams in Word and Excel 2007.

^ Top of page ^

• Share your best shortcuts, tips, and tricks with other TOL readers. Send us your tips, and please include your permission to acknowledge you by name in the newsletter.

• Send us your suggestions about what you'd like to read about in future Office Letter editions.

• Premium Edition subscribers enjoy the newsletter in HTML format, along with access to all back issues, a fast search engine, and no advertising. A subscription costs just $15/year.

• If you like this newsletter, share the free edition with your friends and co-workers. Point them to: http://www.officeletter.com/subscribe.html
  and encourage them to sign up! Our standard edition is free, and readers can unsubscribe at any time.

  ^ Top of page ^