76.

Assembling the Pieces

You have all the subordinate task macros ready for carrying out your complex monthly project:

• ImportFile opens and parses the text file.

• FillLabels makes the file look like a database.

• AddDates distinguishes one month from another in the database.

• AppendDatabase adds the new rows to the bottom of the saved database.

• DeleteSheet cleans up the temporary worksheet.

Each piece is prepared and tested. Now you get to put them all together.

Record a Macro that Runs Other Macros

The easiest way to glue macros together is to record a macro that runs other macros.

Record Macro

1. Click the Record Macro button, type MonthlyProject as the macro name, and click OK.

   Run Macro

   

2. Click the Run Macro button, click ImportFile, and then click Run.

   

3. Select the text file you want to import, and then click Open.

4. Click the Run Macro button, click FillLabels, and then click Run.

5. Click the Run Macro button, click AddDates, and then click Run.

6. Type an appropriate date, and click OK.

7. Click the Run Macro button, click AppendDatabase, and then click Run.

8. Click the Run Macro button, click DeleteSheet, and then click Run.

   Stop Recording

   

9. Click the Stop Recording button.

Now you can look at what you created. Click the Run Macro button, select the MonthlyProject macro, and click Edit. After deleting the standard comments, here's what the macro to run other macros looks like:

Sub MonthlyProject()     Application.Run Chapter02.xls!ImportFile"     Application.Run Chapter02.xls!FillLabels"     Application.Run Chapter02.xls!AddDates"     Application.Run Chapter02.xls!AppendDatabase"     Application.Run Chapter02.xls!DeleteSheet" End Sub

The MonthlyProject macro runs each of the subordinate macros in turn. The subordinate macros are known as subroutines. (By the way, this is the reason you start macros with the word Sub, so that you can turn them into subroutines simply by running them from another macro.)

Simplify the Subroutine Statements

The statement that the macro recorder creates for running a subroutine is somewhat unwieldy. You can simplify the statement, making it easier to read and faster to run.

1. Delete everything from each recorded subroutine statement except the name of the macro itself. Here's what the macro should look like when you're done:

   Sub MonthlyProject()     ImportFile     FillLabels     AddDates     AppendDatabase     DeleteSheet End Sub

2. Save the Chapter02 workbook.

3. Press F5 to test the MonthlyProject macro. (You might also want to try pressing F8 to step through the main macro and each of the subroutines.)

You now have an automated process for importing each new month's data. You've worked hard and deserve a rest. Take the rest of the day off.