Microsoft Office Tricks and Tips

 

 

If you are an MS-Access programmer, and would like syntax highlighting and formatting for your SQL queries, please try out my new Access Add-In: Access SQL Editor!

There is a 14-day trial here, and you may purchase it here. For support, please contact me at signature.gif

Excel

  • Get unique rows with Advanced Filter. Put your cursor in the header row and use Data->Filter->Advanced Filter. Click the “Unique Rows” checkbox.

  • Filter big spreadsheets with Autofilter. Highlight your header row, and use Data->Filter->AutoFilter to create drop-down boxes that contain all of the possible values in each column. Then you can select values from each column to limit what you actually see. (2000,2003)

  • Select the entire used range of a spreadsheet. Select anywhere in your data except for cell A1, and then hit Ctrl-A. (2003)

  • Copy-and-Paste right into Access. If you find yourself importing entire Excel sheets into Access a lot, copy-and-paste might be quicker than importing. Save your Excel document to disk, then copy the data you want to put into Access. (2003)

Access

  • Drag tables between databases. You can copy tables from one Access database to another simply by dragging them from one open database window to another. You can position the two Access databases side-by-side and drag. If one of your databases isn’t visible, you can drag the table from the source database down to your Windows taskbar, drag it over the destination database’s taskbar item, and the other database will pop up. Then move the table directly into the destination database’s database window. (2003)

USysRegInfo Example

Generic USysRegInfo for an add-in to any Access version.

Subkey

Type

ValName

Value

 

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&Menu Item Name

0

 

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&Menu Item Name

1

Library

|ACCDIR\AddInName.mdb

 

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&Menu Item Name

1

Expression

=FunctionToCall()

 

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&Menu Item Name

1

Version

1

Word

Powerpoint

Visio