Use Access Office Links tool to convert a report to an Excel worksheet
Thursday, August 06, 2009 03:06 PM
Here's a quick and easy trick for getting your Access report data into Excel--organized just the way you want.
Microsoft Access
Use Access Office Links tool to convert a report to an Excel worksheet
Let's say you distribute a Customer report (Figure A) to all management personnel every month. Now, they would like you to send them the same report, including the City groupings, as an Excel worksheet.
Figure A
Unlike other Access Import tools, Access 2002/2003's Office Links tool can do just that. (Note: This feature is not available in Access 2007.)
Follow these steps:
- Open the Access database that contains the Customer report.
- Click Report under Objects in the Database Window and select Customer Report By City.
- Go to Tools | Office Links and click Analyze It With Microsoft Excel (Figure B).
Figure B
Access opens a new Excel workbook with the data arranged in columns exactly as the original report, including grouping the data by the City field (Figure C).
Figure C
Microsoft Excel
Be careful when you use Excel's search feature
Excel's search function, by default, searches only the current sheet, which could pose a problem if you use multiple sheets.
Unfortunately, the feature's not upfront about it either. For instance, the search for Aristotle in the following spreadsheet turns up one occurrence, in cell A14. What you can't see is that Sheet 3 contains a duplicate book list (simply for the sake of this example).
As you can see below, there's no alert or warning that specifies which sheet Excel will consider in its search. You might assume it's just the current sheet, or you might (incorrectly) assume that it's searching all of the sheets. This behavior is something you just have to know about and accommodate.
Excel will find matches on multiple sheets, but you must first define the search group in one of three ways:
- Hold down [Ctrl] to choose nonadjacent sheets. Then, click the tab of each sheet you want to search.
- Hold down [Shift] and click the first and last sheets in an adjacent group of sheets to select the clicked sheets and all those between.
- Select all sheets in the workbook by right-clicking any sheet tab and choosing Select All Sheets from the resulting shortcut menu.
Once you've created a group, Excel will display [Group] in the title bar. To ungroup sheets, click any unselected sheet or right-click a sheet tab and select Ungroup Sheets from the resulting shortcut menu.
When you run a search from a grouped sheet, Excel searches all the sheets in the group, not just the current sheet.
Microsoft Word
10 annoying Word features
One of the most common complaints about Microsoft Word is its insistence on taking control of the wheel. Many users get completely blindsided by some of Word's automatic changes, and even the more experienced among them often just live with Word's shenanigans because because they don't know how to disable them.
If you've gotten more than your share of support calls from users trying to wrestle Word into submission (or pulled out your own hair on a few occasions), the list below will help you quickly cut Word down to size.
A few things to keep in mind: First, many of the options you need are located in the AutoFormat As You Type tab. A similar set of options exists in the AutoFormat tab, but disabling those won't do you any good with Word's on-the-fly changes. Users sometimes don't make that distinction and can't understand why the changes are still happening after they thought they'd turned off the necessary settings.
Second, some of these options may actually sound appealing to your users but might currently be disabled. You can use this list to help them selectively activate the features they want, not just to turn things off. It's not always the features themselves that are annoying--it's just not knowing how to control them.
And finally, Word 2007 offers the same feature set described here, but accessing the options is a little different. The section at the bottom explains how to find them in that version.
Cheap solution: Undo
If you haven't had a chance to disable an automatic feature (or you want to leave it enabled and override it only occasionally), remember that pressing Ctrl+Z or clicking the Undo button right after Word makes a change will undo that action.
So, for instance, if Word inserts a smart apostrophe where you want to retain the straight character to denote measurement, just hit Undo to straighten it back out.
The annoyances
| Behavior | How to turn it off |
| #1: Word creates a hyperlink when you type a Web page address. | Go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Under Replace As You Type, deselect the Internet And Network Paths With Hyperlinks check box and click OK. |
| #2: Word changes capitalization of text as you type it. | A host of settings can trigger this behavior. Go to Tools | AutoCorrect Options and select the AutoCorrect tab. Here, you can deselect whichever check boxes govern the unwanted actions:
|
| #3: Word inserts symbols unexpectedly, such as trademark or copyright characters or even inserts an entire passage of text. | Go to Tools | AutoCorrect Options and select the AutoCorrect tab. This time, find the Replace Text As You Type check box. Either deselect it to suppress all replacements or select and delete individual items in the list below it.It might make sense to keep the feature enabled and selectively remove items, since the list includes scores of common misspellings that are actually nice to have corrected for you. |
| #4: Word superscripts your ordinal numbers, such as 1st and 2nd. | Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Ordinals (1st) With Superscript check box and click OK. |
| #5: Word converts fractions into formatted versions. | Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Fractions (1/2) With Fraction Character option. |
| #6: Word turns straight apostrophes and quote marks into curly characters. | Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Straight Quotes With Smart Quotes check box and click OK. |
| #7: When you try to select a few characters within a word, the highlight jumps to select the entire word. | Go to Tools | Options and click the Edit tab. In the right column under Editing Options, deselect the When Selecting, Automatically Select Entire Word check box and click OK. |
| #8: When you type three or more hyphens and press Enter, Word inserts a border line. | Go to Tools | AutoFormat and select the AutoFormat As You Type tab. Deselect the Border Lines check box and click OK.A similar option exists for inserting a table, but it's generally not going to sneak up on you: When the Tables check box is selected, typing a series of hyphens and plus marks before pressing Enter will insert a table (with the hyphens representing cells). You can turn off that option if you think you might stumble into an unwanted table insertion. |
| #9: Word automatically adds numbers or bullets at the beginning of lines as you type them. | There are two flavors of this potential annoyance. First, if you start to type something Word thinks is a bulleted list (using asterisks, say) or type 1, a period, and some text, it may convert what you type to bulleted or numbered list format when you press Enter.To prevent this, go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Then, deselect the Automatic Bulleted List and/or Automatic Numbered list check boxes and click OK.A related aspect of this behavior is that once you're entering automatic list items, pressing Enter will perpetuate it--Word will keep inserting bullets or numbers on each new line. To free yourself from this formatting frenzy, just press Enter a second time, and Word will knock it off. |
| #10: When you type hyphens, Word inserts an em dash or an en dash. | If you type a word, two hyphens, and another word (no spaces), Word will convert the hyphens to an em dash. If you type a space before and after the hyphens, it will convert them to an en dash.To disable this feature, Go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Deselect the Hyphens (–) With Dash (-) check box and click OK. |
Bonus fixes
Word may cause your users some additional grief in various other ways besides automatic behaviors. It goes a little something like this:
User: My document if full of weird code stuff and my pictures are gone.
Culprit: Field code display has been toggled on.
Solution: Suggest that the user press Alt+F9 to restore the display of field code results.
User: I'm seeing gray brackets around a bunch of my text.
Culprit: Bookmark display has been enabled.
Solution: Go to Tools | Options and select the View tab. Then, under the Show options, deselect the Bookmarks check box and click OK.
User: I'm typing and everything in front of the cursor is disappearing.
Culprit: The evil Overtype mode has been activated.
Solution: Go to Tools | Options and select the Edit tab. Then, under Editing Options, deselect the Overtype Mode check box and click OK. (It might be quicker to double-click OVR on the status bar, if you can point the user to it.)
User: Everything's gone, all my toolbars and menus and everything--there's nothing here but text.
Culprit: The user has landed in Full Screen view.
Solution: Direct the user's attention to the Close Full Screen View button at the bottom of the window (depending on the version) or tell them to press Alt+V to display the View menu. They can then select Full Screen to turn off that view mode and return to familiar territory.
Accessing the options in Word 2007
All the settings we've discussed here are accessible via the Office button in Word 2007:
- To get to the AutoCorrect dialog box, click the Office button, select Word Options at the bottom of the menu, and choose Proofing from the pane on the left. In the pane on the right, click the AutoCorrect Options button, and Word will display the AutoCorrect dialog box containing the AutoCorrect and AutoFormat As You Type tabs.
- To get to editing options, click the Office button, select Word Options at the bottom of the menu, and choose Advanced from the pane on the left. Word will display Editing Options at the top of the pane on the right. In that section, you'll find the When Selecting, Automatically Select Entire Word check box and the Use Overtype Mode option. If you scroll down to the Show Document Content section, you'll find the Show Bookmarks check box.
- The successor to Full Screen view in Word 2007 is Full Screen Reading view. Users shouldn't get stuck there, but if they do, the Close button in the upper-right corner of the window will take them back to Print Layout view.
There are currently no comments for this post.