Case Study

Let's say you work in an accounting department. Each day you receive a text file from the company system showing all the invoices produced the prior day.This text file has commas separating each field.The columns in the file are

InvoiceDate, InvoiceNumber, SalesRepNumber, CustomerNumber, ProductRevenue, ServiceRevenue,and ProductCost (see Figure 1.9).

Figure 1.9

Invoice.txt file.

Figure 1.9

Invoice.txt file.

As you arrive at work each morning, you manually import this file into Excel.You add a total row to the data, bold the headings,and then print the report for distribution to a few managers.

Preparing to Record the Macro

This is a task that is perfect for a macro. Before you record any macro,you should think about the steps that you will use before you begin. In our case,these steps are as follows:

1. From the menu,select File, Open.

2. Navigate to the proper folder.

3. Choose All Files(*.*) from the Files of Type:dropdown list.

4. Select Invoice.txt.

5. Click Open.

6. In the Text Import Wizard—Step 1 of 3,select Delimited from the Original data type section.

7. Click Next.

8. In the Text Import Wizard—Step 2 of 3,uncheck the Tab key and check Comma in the Delimiters section.

9. Click Next.

10. In the Text Import Wizard—Step 3 of 3,select General in the Column data format section and change it to Date:MDY.

11. Click Finish to import the file.

12. Press the End key followed by the down arrow to move to the last row of data.

13. Press the down arrow one more time to move to the total row.

14. Type the word Total.

15. Press the right arrow key four times to move to column E of the total row.

| z |T| 16. Click the Autosum button and press Ctrl+Enter to add a total to the Product Revenue column while remaining in that cell.

17. Grab the Autofill handle and drag from Column E over to Column G to copy the total formula over to Columns F and G.

18. Highlight Row 1 and click the Bold icon to set the headings in bold.

19. Highlight the Total row and click the Bold icon to set the totals in bold.

20. Press Ctrl+A to select all cells.

21. From the menu,select Format,Column,Autofit Selection.

After you've rehearsed these steps in your head, you are ready to record your first macro. Open a blank workbook and save it with a name like MacroToimportinvoices.xis.Click the Record button or select Tools,Macro,Record New Macro.

In the Record Macro dialog,the default macro name is Macroi .Change this to something descriptive like importinvoice.Make sure that the macros will be stored in This Workbook.You might want an easy way to run this macro later,so enter the letter i in the Shortcut Key field. In the Description field,you will, by default,have your name and date. Add a little descriptive text to tell what the macro is doing (see Figure 1.10).When you are ready,click OK.

Figure 1.10

Before recording your macro,complete the Record Macro dialog box.

Figure 1.10

art+[T] ThB Workbook

art+[T] ThB Workbook

Recording the Macro

Don't be nervous, but the Macro Recorder is now recording your every move.You want to try to perform your steps in exact order without extraneous actions. If you accidentally move to column F and then back to E to enter the first total, the recorded macro blindly makes that same mistake day after day after day. Recorded macros move fast, but this is nothing like having to watch your same mistakes played out by the macro recorder again and again.

0 0

Post a comment