There are two articles
on this page:
The Magic of Macros: Macro Power in Popular Programs (below)
The Magic of Macros: Letting Your Application Do The Work
The Magic of Macros:
Macro Power in Popular
Programs
By Roy W. Johnson
Published in St. Louis
Computing
Summary: This article describes how I programmed macros to extract part numbers and prices from a long spreadsheet, transfer these part numbers and prices to a Word form letter, and automatically insert descriptions of each part and total costs--a multi step process reduced to a few keystrokes.
"Just flounder around until you find a niche."
This was the advice I received from a computer consultant frend several years ago when I was thinking of "retiring" from my job as a high school history teacher to become a computer trainer and consultant.
Recently, I have found a new "niche": macro programming in popular software such as Excel, Word, WordPerfect, and Lotus. My clients tell me professional programmers can be found for major computer languages but not for the macro languages of these programs.
This new "niche" has resulted in some nice commissions. It also leads me to believe that the power of macros in these programs is not understood by most users. To demonstrate the possibilities, let me give an example.
My most recent project came from a client who was using Excel to list nearly 300 different sub-assemblies. Ten to fifteen sub-assemblies would be combined and sold to a customer as a single installation. An operator would type in the quantity next to each part number, and Excel would compute the total.
Then, the operator would switch to Word, open a form letter, enter the customer's name and other details, then use the Word Autotext feature to enter a description of each part. (With Autotext, the user fills in a key word and the program replaces it with a longer entry.)
My job was to automate the process in both Word and Excel.
First, I programmed a macro in Excel to extract only those parts with a quantity typed next to them and copy the quantity and the part number to a temporary spreadsheet. This is more complex than it might seem. The macro starts the cursor in the topmost cell of the quantity column and moves it down that column checking each cell to see if it containsed an entry. If so, it checks to see if the entry is a heading, a quantity, or the end of the spreadsheet. If it is a quantity, the macro copies that cell and the cell with the part number to a temporary spreadsheet. When all indicated parts are copied, the macro saves the temporary spreadsheet then closes the file.
Then the operator opens a template in Word and runs another macro. That macro imports the spreadsheet as a table then changes the table to text. That results in two tabulated columns with the quantity in the first column and the part number in the second.
Then the macro finds each part number, using a unique character that I added in Excel. (I used ##. ) Next, the macro opens the Autotext dialog box and replaces the part number with the part description, then repeats the "find" until all descriptions have been filled in.
This reduced the entire process to a few steps: Open the Excel spreadsheet, enter the quantities, run the Excel macro. Open the Word template, enter the customer information, run the Word macro. Presto! The Word document now contains the quantity and full paragraph description of each part in the assembly, ready to print and mail to the customer.
This example is just to give you an idea of what macros can do. Macro programming languages are as powerful as generic programming languages. Macro programming that is this detailed probably requires the services of a professional, but the time saved could quickly pay the commission.
I will repeat what I have said in earlier columns: Nearly any repetitive task in any program can be automated using macros, and., as you can see, macros can even combine programs such as spreadsheets and word processing documents.
Of course, all macro programming need not be done by professionals. For many of your everyday tasks, you can learn to set up macros for yourself that will make your life much easier.
But that's another column!
NOTE: Since this column was written, Microsoft has added Visual Basic capabilities to all of its programs, so that the entire process above could be reduced to one macro rather than two.
-The
Magic of Macros
Letting Your Application Do the Work
By Roy W. Johnson
Published in St. Louis
Computing
Summary: Uses the example in the previous article to demonstrate how to do macro programming with minimal effort by using built-in capabilities of your application.
Hey, someone is really reading these articles! My last column brought several phone calls. Most callers wanted to know where they could get books or information on writing macros, and how they could get started writing macros for themselves.
The best way to find books is to call the product support line of the application and ask what they have available. You can find the number by clicking the Help menu of your application. One example is Microsoft Word Developer's Kit, which I use in writing Word macros. Call Microsoft Technical Support with your credit card handy. For $39.00, you will get a hefty book with two disks.
As for getting started yourself, let's use Microsoft Word as an example. Word uses a language called WordBasic for its macros. It is similar to ordinary Basic but with special commands that operate only in Word.
But you don't need to know how to program in Basic to start writing macros. Let's use a macro I discussed in my last article as an example. Remember that I used Excel to extract quantities and part numbers (all ending in ##) and put them into a word document as a tabulated list. In my Word document I wanted to find each number, delete the ## (which was only to provide a means of finding the part numbers), and then replace the number with an autotext entry.
The first secret is to let Word do most of the work. With the document on my screen, I clicked on Tools/Macro/Record. A dialog box opened asking if I wanted to assign the macro to the Toolbars, Menus, or Keyboard. I planned to use another macro to run this macro, so I just typed a macro name (Findpart) and clicked OK. Word also asked what template I wanted to assign it to. Let's leave the template part for later. If you don't change it, it will go to the Global template and be active for all documents.
To record the macro, I clicked on Edit/Find and told it to find ##. When it did this and highlighted the first ## at the end of the part number (A140##). I pressed Delete to earase the ##.
Next, I wanted to select A140 and substitute the Autotext entry. I found Word would not let me double-click A140 to select it while I was in the Macro/Record mode, but I could accomplish the same thing by pressing Control+Alt and the left arrow.
The next job was to bring in the Autotext entry, which had been typed and stored. I clicked on Edit/Autotext/Insert, and presto! The autotext description for part A140 replaced the part number.
I stopped recording at this point to test the macro. Oops! Running the macro substited the description of part A140 for ANY part number. That would not do.
I had to check my Microsoft Basic Toolkit and finally call the Microsoft hotline to do this one. As it turned out, the macro only needed slight editing. I clicked on Tools/Macro/Edit, made the changes, and tested again. Great! It worked!
Here is what the macro looked like at this point. The parts that I typed in are in bold italics. Word did the rest:
=====================
REM macro name Findpart finds part names and
substitutes Autotext
descriptions.
Sub MAIN
EditFind .Find = "##", .Direction = 0, .MatchCase = 0, .WholeWord = 0, .PatternMatch = 0, .SoundsLike = 0, .Format = 0, .Wrap = 2
EditClear
WordLeft 1, 1
A$ = Selection$()
EditAutoText .Name = A$, .Insert, .Context = 0, .InsertAs = 0, .Insert
End Sub
=========================
As you can see, I didn't even have to know commands like EditFind or MatchCase--Word put them in for me. The A$=Selection$() part I got from the Microsoft hotline. It substitutes A$ for the part number highlighted, then makes that the autotext entry and inserts the autotext description to replace the part number.
This macro was only part of the entire macro job. I set up a master macro to bring in the Excel spreadsheet, convert it to a table, then run the Find macro repeatedly until all part names were found and replaced with descriptions. This required more programming knowledge. But this should give you the idea of how to let your programs do most of the work when you write macros, and perhaps suggest ways you can use macros to make your life easier.