-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 14 September 20, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Office: A Flurry of Fixes
2) Excel: Transpose Trick Keeps Tables in Sync
3) Reader Challenge: Word Tables and Date Math
4) Word Quick Tip: Omit Headers on the First Page
Premium Edition Extra:
Office: Change the My Places Bar with Free Software
-------------------------------------------------------------------
1) OFFICE: A FLURRY OF FIXES
-------------------------------------------------------------------
Microsoft Reveals Security Problem with WordPerfect Converter
Last week Microsoft released Security Bulletin MS04-027, revealing
that customers using Office, FrontPage, Publisher (versions 2000
through 2003) or the Works Suite versions 2001 and 2004) could be
at risk if they use the WordPerfect 5.x converter that's part of
the products.
According to the company, "If a user is logged on with
administrative privileges, an attacker who successfully exploited
this vulnerability could take complete control of an affected
system, including installing programs; viewing, changing, or
deleting data; or creating new accounts with full privileges. Users
whose accounts are configured to have fewer privileges on the
system would be at less risk than users who operate with
administrative privileges. However, user interaction is required to
exploit this vulnerability."
For details, visit:
http://www.microsoft.com/technet/security/bulletin/ms04-027.mspx
You can visit the Office Update site to download and/or
automatically install the update: http://office.microsoft.com/en-
us/officeupdate/.
- - -
Problem with Excel 2002 VBA
If you installed Office 2002 Service Pack 3, you may have noticed a
new problem if you run VBA code. Specifically, Knowledge Base
article 883950 discusses a bug introduced in SP3. According to
Microsoft,
When you run a Microsoft Visual Basic for Applications (VBA)
macro command to display the Templates dialog box to create a
new file, Microsoft Excel 2002 displays the New Workbook task
pane instead. An example of a macro command may be
Application.Dialogs(xlDialogNew). The macro does not pause in
time for you to select a file or to select a template to open.
Therefore, the macro does not capture your selection.
For details, and the fix, visit
http://support.microsoft.com/default.aspx?scid=kb;EN-US;883950
The fix will be included as part of the next Service Pack for
Office 2002.
- - -
Problem with Signed Messages in Outlook 2002
Office 2002 SP3 also introduced a problem with signed messages in
Outlook. As Knowledge Base Article 883927 notes: "When you send a
signed message with Outlook 2002, the root certification authority
(CA) is also included in the message."
If you've experienced the problem, you'll find the fix at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;883927
The fix will be included as part of the next Service Pack for
Office 2002.
- - -
Stopping Attacker's Code Execution in Office, Visio, and Project
Microsoft announced updates to fix vulnerabilities in Project and
Visio 2002 and 2003, as well as Office XP and 2003. The patch
prevents a specially crafted image from being able to run the
attacker's code.
Office applications that pose a problem are Word, Excel, Outlook,
PowerPoint, FrontPage, and Publisher 2002 (with SP3 installed) and
2003 (for versions without SP1 installed), plus InfoPath and
OneNote 2003. Visual Studio .NET users (versions 2002 and 2003)
are also impacted.
Links to the fixes you need are provided in Security Bulletin MS04-
028:
http://www.microsoft.com/technet/security/bulletin/MS04-028.mspx
Running Office Update should also install the fix.
-------------------------------------------------------------------
EASY-TO-USE WEB HOSTING FROM $9.95
-------------------------------------------------------------------
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:
http://www.officeletter.com/thehostgroup.html
-------------------------------------------------------------------
2) EXCEL: TRANSPOSE TRICK KEEPS TABLES IN SYNC
-------------------------------------------------------------------
Works with Excel 97 and above
I often create long tables that report sales by date and by agent.
I put the dates across the top (as column headings) and the agent
names in rows, then fill in sales figures where the appropriate
date column and agent row intersect.
Sometimes, however, I want to see the organization reversed -- that
is, columns become rows and rows become columns. It's easy to do.
I copy the cell range, then use the Edit/Paste Special command and
check the Transpose button (at the bottom of the dialog box).
Excel now displays the table with agent names across the top and
dates going down -- the data (such as sales for Agent Anderson in
July 2004) is kept in the proper place.
If I change Anderson's sales figure for July in the original table,
my transposed table displays the original figure. Thus, I have two
similar tables with unsynchronized data. Uh, oh.
Here's how to create a transposed, synchronized table.
First, note the dimensions of your original table. Include the
header row (containing dates) and header column (containing agent
names) and all the data; my original table was in cells A1 through
J30 -- that is, 10 columns by 30 rows. The transposed table will
thus be 30 columns by 10 rows.
Select a cell range of that "transposed" dimension for the
destination table (I picked cells A41 to AD50 -- 30 columns, 10
rows). Now enter the formula
=TRANSPOSE(A1:J30)
and instead of pressing Enter, press Shift + Ctrl + Enter to
create an array formula.
Now we have a transposed table. Enter a new value in original
table and you'll see that the corresponding cell in the new table
is updated.
NOTE: There are two limitations to the transpositions we've been
talking about.
1. If I add another Agent or another Sale Date, I have to start
over -- adding a column or row in the original table doesn't
automatically add a column or row to the transposed table.
2. If I make a change to the destination (transposed) table, the
change isn't reflected in the original table. The "synching" goes
one way only, from the original range to the transposed range. For
that reason, you may want to protect the cell range of the
transposed table to prevent accidental changes.
-------------------------------------------------------------------
SPONSOR: GET ORGANIZED WITH FEATURES OUTLOOK DOESN'T OFFER
-------------------------------------------------------------------
AMF Daily Planner & PIM includes a complete Rolodex(tm)-style contact
manager, a full calendar, and a sophisticated scheduling system for
your appointments and events. It even keeps track of your notes and
tasks. Its folder-organized system offers multiple data views,
powerful search, and easy import/export. Plus, unlike Outlook, you
don't need a server to add networked users. Get more information
here:
http://www.amfsoftware.com/affilpim.pl?affil+10057
-------------------------------------------------------------------
3) READER CHALLENGE: WORD TABLES AND DATE MATH
-------------------------------------------------------------------
TOL reader Joy Taylor is trying to create a table in Word that has
a different (sequential) date in each cell. For example, she wants
a table that will have the following data in the first columns of
the first seven rows:
Monday, September 20, 2004
Tuesday, September 21, 2004
Wednesday, September 22, 2004
Thursday, September 23, 2004
Friday, September 24, 2004
Saturday, September 25, 2004
Sunday, September 26, 2004
She wants to create the data in Word tables using only Word
functionality. Using Excel to create the data and then import
(paste) it into a Word table, or embedding an Excel worksheet into
Word, aren't acceptable techniques. She prefers to enter the first
date (in our example, she'd enter September 20 or 9/20 in the first
cell) and have Word's table math calculate the remaining values.
We've tried at The Office Letter to create such a table, but ran
into a number of roadblocks. Values in Word cells can be formatted
in a number of ways it they contain a number, but dates don't seem
to be supported.
Can you offer a technique that will solve Joy's problem? Send your
solution to [EMAIL PROTECTED]
-------------------------------------------------------------------
4) WORD QUICK TIP: OMIT HEADERS ON THE FIRST PAGE
-------------------------------------------------------------------
Works with Word 97 and above
If you don't want the header you've defined to appear on the first
page of your document:
1. Move to the first page and choose View/Header and Footer from
the main menu.
2. Click on the Page Setup button in the Header and Footer toolbar.
(It looks like an open book.)
3. Click on the Layout tab.
4. Check the "Different first page" box. Word clears the header
and changes the header label to "First Page Header." You're ready
to enter the header text for the first page. (If you don't want a
header on this page, just leave the header area blank.)
5. Click on Close in the Header and Footer toolbar.
This procedure also creates a separate footer for the first page.
-------------------------------------------------------------------
PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about free
software that lets you quickly change your My Places list in the
File dialog boxes.
Why not subscribe to the Premium Edition today for just $12/year?
HTML format, extra content, access to all back issues, a fast
search engine, a printer-friendly format option, and no
advertising! Visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
CONTACT POINTS
-------------------------------------------------------------------
SUBSCRIPTION CHANGES
Your subscription record shows the following:
Email address: [EMAIL PROTECTED]
First name: subscriber at [EMAIL PROTECTED]
Last name:
Full name:
If you would like to edit the above data, click here:
http://www.office-letter.com/cgi-bin/mmp/[EMAIL PROTECTED]
To unsubscribe, visit:
http://www.officeletter.com/sub/substdremove.html
and enter your e-mail address.
SHARE YOUR TIPS: Send them to [EMAIL PROTECTED] and include
your permission to acknowledge you by name.
FEEDBACK: Send your suggestions and comments about The Office
Letter to [EMAIL PROTECTED]
RECOMMEND THE OFFICE LETTER TO YOUR FRIENDS AND COLLEAGUES
Point them to http://www.officeletter.com/current.html
MORE FREE TECH E-NEWSLETTERS: http://www.techletters.com
----------- THE OFFICE LETTER ----- www.officeletter.com ----------
Tips and Tricks for Microsoft Office - Published Weekly
Copyright 2004 Masterware, Inc. All rights reserved
Now In Our Fourth Year - ISSN: 1543-5768
Editor in Chief: James E. Powell
Contributing Editors: Jim Boyce (www.boyce.us)
Dick Archer (www.diseno.com)
