Sharing Autocorrect Shortcuts

Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them?

AutoCorrect, which debuted in Excel 95, can correct common spelling errors on the fly. As many users of Excel have discovered, you can also assign "shorthand shortcuts" that expand to a longer sequence of characters. For example, you may define "awc" as an AutoCorrect shortcut for "Amalgamated Widget Corporation of America." When you type awc into a cell, Excel will then replace it with the associated text string. You define and manage your shortcuts by using the Tools, AutoCorrect command.

Excel and all other Microsoft Office applications store AutoCorrect shortcuts in a single *.acl file in your Windows folder (the exact file name will vary). So changes you make from Excel, say, will be available in Word. However, there's no tool for manually editing the binary *.acl file and moving such a file from one system to another will invalidate your existing entries. The solution is to use a VBA macro to create the shortcuts.

Start Excel with a new workbook with one sheet, and then enter your shortcuts and their associated text into columns A and B, respectively (as in the figure below). Enter as many as you like, beginning in row 1, and don't include any blank rows between the entries. Save and name this worksheet.

Select Tools, Macro, Macros to display the Macros dialog box. Type CreateShortcuts in the Macro Name field and click Create. Then enter the following macro into the VBA module, and press Alt-F11 to return to Excel.

Sub CreateShortcuts()
  ItemCount = Application.CountA(Range("Sheet1!A:A"))
  For Row = 1 To ItemCount
    ShortText = Cells(Row, 1)
    LongText = Cells(Row, 2)
    Application.AutoCorrect.AddReplacement ShortText, LongText
  Next Row
End Sub

Save the workbook and distribute it to your coworkers. To add the AutoCorrect shortcuts, open the workbook, select Tool, Macro, Macros, and then execute the CreateShortcuts macro. Be aware that existing shortcuts with the same name will be overwritten without warning.