Thursday, September 22, 2005

Customizations

With rare exceptions, you can create a macro to automate any task you can do manually in Word, Excel, PowerPoint, Outlook, FrontPage, or Access. Macros are ideal for automating routine drudge work—those everyday tasks that normally require multiple menu selections and mouse clicks.

For example, you can use macros to

* Print company letters and companion envelopes—routing the printout to the correct network printer, and selecting the correct paper trays for letterhead, additional pages, and envelopes—and then printing file copies on yet another printer, or with a different tray.
* Apply complex formatting rules—everything from scanning corporate reports to ensuring that all "Level 1" headings start with a number, to validating the searchable keywords in a memo, to correcting common typographical mistakes such as two spaces following a period.
* Collate and aggregate budgets, at any organizational level, complete with charts and custom pivot tables, based on Excel spreadsheets submitted by each work unit. When changes come, roll the new numbers into the divisional or corporate report in minutes.
* Retrieve data from an Access customer database and generate collection letters in Word for all customers whose accounts are 90 days or more past due.

What Can You Do with Macros?

Say your branch offices post their sales reports over the weekend on your company's intranet. Your boss expects you to download each report, format it according to company style, add your analysis, and then turn it into a memo that can go out under the boss's name to everyone else in the department. You can write a macro to automate much of the task. The sequence might go something like this:

* On Monday morning, you start Word; choose File, New; select the Memo based on sales statistics; and click OK. Then you go get a latte.
* Word creates the new memo, complete with distribution list and a Word table at the top of the memo. Then the macro kicks in and opens the intranet Web page with the sales information, viewing it as a Word document. The macro steps through the document, pulling data from predefined locations on the page.
* The macro takes the data from the Web page, performs whatever mathematical gymnastics might be required, and puts the resulting numbers in predefined locations in the table. It leaves the Web page open, so you can see that the correct data has been posted over the weekend.
* The macro then hops down to the bottom of the memo, attaches your boss's signature block, and then backs up a few lines—leaving room for your analysis—and exits. You return with your latte; verify the data is correct; type your analysis; choose File, Send To; and send it to your boss for approval.

If others in your company perform similar duties, you can easily distribute the memo template and the macro. Those using the macro only need to know that clicking the button, or choosing the menu item, creates the report. As long as the format of the Web page doesn't change, they needn't know a thing about macros or VBA, or even how to modify their toolbars or menus. You can do it all for them, easily, with a macro.

No comments: