28. | FrameMaker 6: Beyond the Basics

Task Four: Appending to the Database

Now that you've added monthly dates to the imported Nov2002 worksheet, it has the same columns as the order-history database, so you can just copy the worksheet and append it to the first blank row below the database. Of course, you don't want to include the column headings.

Append a Worksheet to a Database

First you'll copy the data (without the headings) from the Nov2002 worksheet. Then you'll open the database, select the first blank cell below the database, rename the database range to include the new rows, and close the database file.

Record Macro

Select one of the Nov2002 worksheets that has the labels filled and the dates added, click the Record Macro button, type AppendDatabase as the macro name, and then click OK.
Select cell A1. Choose the Edit menu, click Delete, click the Entire Row option, and click OK.

This deletes the heading row so that you won't include it in the range you copy to the database.
Press Ctrl+Shift+* to select the current region, and then click Copy on the Edit menu.

Open
On the toolbar, click the Open button, type Orders.dbf in the File Name box, and click Open.

The Orders.dbf database file opens with cell A1 selected. The values in column A appear as number signs because the column is too narrow to display the dates.
In the column headings, double-click the border between columns A and B to adjust the width of the Date column. (The dates in the database look different than those in Nov2002 because of formatting differences.)
Press Ctrl+Down Arrow to go to the last row of the database.
Press the Down Arrow key to select the first cell below the database. (It should be cell A3301.)
Choose the Edit menu, click Paste to append the rows you previously copied, and then press the Esc key to remove the copy message from the status bar.

Press Ctrl+Shift+* to select the entire new database range, including the newly appended rows.

Important

When you open a dBase file in Excel, the range containing the actual database records is automatically named Database. When you save the updated Orders.dbf file as a dBase file, only the values within the range named Database are saved. Any other cell values in the file are discarded. To have the new rows saved with the file, you must enlarge the Database range definition to include them.

Choose the Insert menu, choose the Name submenu, and click the Define command. Type Database in the Names In Workbook box, and then click OK.

Important
Don't select Database from the list of names. If you do, the range the name refers to will remain unchanged.

Now the entire database, including the new rows, is included in the Database range name and will be saved with the file.
Choose the File menu, click Close, and then click No when asked whether you want to save changes. For now, you don't want to save the database with the new records to the Orders.dbf file because you want to first test the macro.

Stop Recording
Select cell A1, and then click the Stop Recording button to turn off the recorder.

Step Through the AppendDatabase Macro

Step through the macro to see it work, and note any changes you should make.

Activate a worksheet with the labels filled in and the dates added. (Run the ImportFile, FillLabels, and AddDates macros if necessary.)

Run Macro
Click the Run Macro button, select the AppendDatabase macro, and click the Step Into button. Look at the first five lines of the macro:
```
Sub AppendDatabase()     Range("A1").Select     Selection.EntireRow.Delete     Selection.CurrentRegion.Select     Selection.Copy
```
These statements are similar to statements you've seen in earlier macros.
Press F8 five times to execute the first five statements in the macro. In the Visual Basic window, the statement that opens the database should be highlighted:
```
Workbooks.Open Filename:="C:\ExcelVBA\Orders.dbf"
```
This statement opens the database.

Tip
If you remove everything except Orders.dbf from the file name, the macro looks for the file in the current folder. That would be useful if you move the project to a new folder.
Press F8 to execute the statement containing the Open method and move to the next statement:
```
Columns("A:A").EntireColumn.AutoFit
```
This statement makes column A wide enough to show all the values.
Press F8 to resize the column and move to the next statement:
```
Selection.End(xlDown).Select
```
This statement is equivalent to pressing Ctrl+Down Arrow. It starts with the active cell, searches down to the last nonblank cell, and selects that cell.
Press F8 to select the last cell in the database and move to the next statement:
```
Range("A3301").Select
```
This statement selects cell A3301. That is the first cell below the database this month, but next month it will be wrong. This is the statement the recorder created when you pressed the Down Arrow key. What you wanted was a statement that moves one cell down from the active cell. You'll need to fix this statement. Make a note to do so.
Press F8 to select cell A3301 and move to the next statement. The next two statements work together:
```
ActiveSheet.PasteApplication.CutCopyMode = False
```
These statements paste the new rows into the database and remove the status bar message.
Press F8 twice. The next two statements redefine the Database range:
```
Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _     "=Orders!R1C1:R3478C7"
```
The first statement selects the current region, which is the correct range for the new database range. The second statement gives the name Database to the specific range R1C1:R3478C7 (A1:G3478). This isn't what you want. You want the name Database to be assigned to whatever selection is current at the time the statement executes. You'll also need to fix this statement. Make a note to do so.
Press F8 twice to move to the statement that closes the workbook file:
```
ActiveWorkbook.Close
```
This statement closes the active workbook. If you've made changes to the workbook, it also prompts you to save the changes. You can program the macro to always save changes or (while testing) to never save changes. (See the 'Choose Whether to Save Changes While Closing a File' section later in this chapter.)
Press F8 to close the database workbook. Click No when asked whether you want to save changes. Only two statements remain in the macro:
```
Range("A1").SelectEnd Sub
```
Press F8 twice to end the macro.

The macro works now only because you're running it under circumstances identical to those when you recorded it, with the same current month file and the same database file. Here's a recap of the changes you'll need to make:

Select the first row under the database.
Give the name Database to the current selection.
Don't prompt when closing the database.

Record a Relative Movement

Take a closer look at the two statements in AppendDatabase that find the first blank cell under the database. Imagine what will happen when you run this next month, when the database has more rows. The statement

Selection.End(xlDown).Select

will select the bottom row, but then the statement

Range("A3301").Select

will always select the absolute cell A3301 anyway.

When you select a cell, the macro recorder doesn't know whether you want the absolute cell you selected or a cell relative to where you started. For example, when you select a cell in row 1 to change the label of a column title, you always want the same absolute cell, without regard to where you started. But when you select the first blank cell at the bottom of a database, you want the macro to select a cell relative to where you started.

The macro recorder can't automatically know whether you want to record absolute cell addresses or relative movements, but you can tell the recorder which kind of selection you want. Use the recorder to record a new statement that you can use to replace the offending statement. You'll record the new statement in a new, temporary macro, and then copy the statement and delete the temporary macro.

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

Relative Reference
On the Stop Recording toolbar, click the Relative Reference button.

When this button is activated, the recorder makes all new cell selections relative to the original selection. Now you need to replace the statement that selects cell A3301 with one that makes a relative movement.

You want to record the action of moving down one cell so that you can record the macro from any cell, on any worksheet.
Press the Down Arrow key once to record a relative movement.

Stop Recording
Click the Relative Reference button to deselect it, and then click the Stop Recording button.
Edit the TempMacro macro and look at the change. The new statement you recorded should look like this:
```
ActiveCell.Offset(1,0).Range("A1").Select
```
This statement means, 'Select the cell below the active cell.' It really does. At this point, you don't need to understand everything about how this statement works. Just trust the recorder. But you might wonder why the statement includes the words Range("A1") when it has nothing to do with cell A1. This statement calculates a new single-cell range shifted down one cell from the original active cell. The macro treats that new range as if it were the upper left corner of an entire 'virtual' worksheet and selects cell A1 of that imaginary worksheet!
Select the new statement, and copy it.
Open the Procedure drop-down list (below the toolbars on the right side of the Module window), and select AppendDatabase.
Select Range("A3301").Select, delete it, and paste the new statement in its place.
Select the TempMacro macro from the Procedure drop-down list.
Delete the TempMacro macro by first selecting all the statements from Sub TempMacro to End Sub and then pressing the Delete key.

With the Relative Reference button, you can control whether selections are absolute or relative to the current active cell. You can turn the Relative Reference button on and off as many times as you need while you're recording a macro.

Name the Current Selection

The statement in the AppendDatabase macro that defines the Database range name contains a potentially serious problem.

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _     "=Orders!R1C1:R3478C9"

This statement sets the name Database to the range that the database occupies at the end of this month. If you don't change this statement before next month, December orders will be discarded from the database when you save it. This is a case where the macro recorder generates a complicated statement when a simple one would work better.

Delete the entire recorded statement.
In its place, type
```
Selection.Name = "Database"
```
Name is a property of a range. By simply assigning a word in quotation marks as the value of the Name property, you can name the range.

Choose Whether to Save Changes While Closing a File

The statement that closes the database file looks like this:

ActiveWorkbook.Close

This statement triggers a prompt that asks whether you want to save changes to the file, because you've made changes to it since you opened it. Sometimes when you automate a process, you know that you always will (or won't) want to save changes. The Close method has an optional argument that allows you to specify whether to save changes. For now, while you're testing the macro, set the statement to not save the changes.

Change the statement that closes the workbook to this:
```
ActiveWorkbook.Close SaveChanges:=False
```
The SaveChanges argument answers the dialog box's question before it even gets asked.
Save the Chapter02 workbook. Then run and test the AppendDatabase macro yourself.

Once you've finished testing the macro and are ready to use it regularly, change the word False to True.

Tip

Technically, since the active workbook happens to be a dBase file, setting the SaveChanges argument to True prevents Excel from asking whether you want to save the changes, but Excel still displays a dialog box to ensure that you want to save the file as a dBase file. If the active workbook is a native Excel workbook, however, the SaveChanges argument causes Excel to save it quietly.

Here's the final version of the AppendDatabase macro:

Sub AppendDatabase()     Range("A1").Select     Selection.EntireRow.Delete     Selection.CurrentRegion.Select     Selection.Copy     Workbooks.Open Filename:="C:\Excel VBA Practice\Orders.dbf"     Columns("A:A").EntireColumn.AutoFit     Selection.End(xlDown).Select     ActiveCell.Offset(1, 0).Range("A1").Select     ActiveSheet.Paste     Application.CutCopyMode = False     Selection.CurrentRegion.Select     Selection.Name = "Database"     ActiveWorkbook.Close SaveChanges:=True     Range("A1").Select End Sub

If you want, you can run the macro again now. It will work the same as it did before, but it's also ready for next month, when the database will have more records.

You're almost finished. The only task left is to get rid of the imported worksheet.