Task Four: Appending to the Database | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

Now that you’ve added monthly dates to the imported Nov2007 worksheet, it has the same columns as the order-history master list, 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 Data to a Master List

First you’ll remove the headings and copy the remaining data from the Nov2007 work-sheet. Then you’ll open the master list workbook, select the first blank cell below the existing list, and close the database file.

Select one of the Nov2007 worksheets that has the labels filled in and the dates added, click the Record Macro button, type AppendData as the macro name, and then click OK.
Select cell A1. Then on the Home tab of the Ribbon, click the Delete arrow, and click Delete Sheet Rows.

This deletes the heading row so that you won’t include it in the range you copy to the database.
Press Ctrl+* to select the current region, right-click a cell in the selected region, and click Copy.
Click the Microsoft Office Button, and click Open. If necessary, navigate to the ExcelVBA07SBS folder.
From the Files Of Type list, select All Excel Files, select Orders.xlsx from the list of files, and then click Open.

The Orders.xlsx workbook opens with cell A1 selected.
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 A3267.)
On the Home tab of the Ribbon, click Paste to append the rows you previously copied, and then press the Esc key to remove the copy message from the status bar.
Click the Microsoft Office Button, 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 text file because you want to first test the macro.
Select cell A1, and then click the Stop Recording button to turn off the recorder.

Step Through the AppendData 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 as necessary.)
Click the View Macros button, select the AppendData macro, and click the Step Into button. Look at the first five lines of the macro:
```
Sub AppendData()   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 these first five simple statements in the macro.

In the Visual Basic editor window, the statement that opens the master list should be highlighted:

Workbooks.Open Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"

This statement opens the master list workbook.

Tip

If you remove the path from the file name, leaving only the actual file, the macro looks for the file in the current folder. That would be useful if you move the project to a new folder. However, if the master list is always in the same location, but the source file may be in different locations, it is better to leave the full path of the master file.

Press F8 to execute the statement containing the Open method and highlight 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 bottom cell in column A of the existing list and highlight the next statement:
```
Range("A3267").Select 
```
This statement selects cell A3267. 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. Make a note to fix this statement.
Press F8 to select cell A3267 and highlight the next statement.

The next two statements work together:
```
ActiveSheet.Paste Application.CutCopyMode = False 
```
These statements paste the new rows into the database and remove the status bar message.
Press F8 twice to paste the data and highlight the next statement:
```
ActiveWorkbook.Close 
```
This statement closes the active workbook. If you’ve made changes to the workbook, it also prompts you to save the changes. Make a note to add an argument that provides the answer automatically.
Press F8 to close the database workbook. Click No when asked whether you want to save changes. This highlights the next statement.

Only two statements remain in the macro:
```
  Range("A1").Select End Sub 
```
Press F5 to run the remainder of 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 existing list, regardless of how many rows are in the list.
- Don’t prompt when closing the database.

Record a Relative Movement

Take a closer look at the two statements in AppendData that find the first blank cell under the database. Imagine what will happen when you run this next month, when the master list will have 3444 rows. The statement

Selection.End(xlDown).Select

will select cell A3444, the appropriate bottom row, but then the statement

Range("A3267").Select

will select the absolute cell A3267 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. The strategy for fixing the macro is to use the recorder to create a new, temporary macro that contains a statement with the appropriate relative movement. You can use the new recorded statement to replace the offending statement in the current macro, and delete the temporary macro.

Click the Record Macro button, type TempMacro as the macro name, and then click OK.
On the View tab of the Ribbon, click the Macros arrow, and click Use Relative References.

When the Relative References option is activated, the recorder creates new cell selections that are relative to the starting selection. You want to record the action of moving down one cell. That’s an action you can record from any cell, on any worksheet (except, of course, a cell in the bottommost row of the worksheet).
Press the Down Arrow key once. That’s enough to record the relative movement you need.

Click the Stop Recording button. Then on the View tab of the Ribbon, click the Macros arrow, and click the Use Relative References button to deselect it.

Tip

The Use Relative References button has a slightly different colored background while it is active-that is, while relative references are being recorded. But you can’t see the button while you’re recording the macro. If you enable the Developer tab, as described in the section titled “Enable the Developer Tab in the Ribbon” in Chapter 9, “Launch Macros with Events,” the Use Relative References button is directly visible on the Ribbon, but only while the Developer tab is active. To always be able to see at a glance whether Relative References are currently in effect or not-and to switch the state at will-add the Use Relative References command to the Quick Access Toolbar. To do that, simply right-click the Use Relative References command on the Macros list, and then click Add To Quick Access Toolbar.

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.

See Also
For more information about relative ranges see the section titled “Relative References” in Chapter 4, “Explore Range Objects.”
Select the new statement, and copy it.

Sometimes when you record new temporary macros, it can be hard to find the original macro. The Visual Basic editor has a Procedure list that can help you find a macro by using the macro name.
Open the Procedure list (below the toolbars on the right side of the Module window), and select AppendData.
Select Range("A3267").Select, delete it, and paste the new statement in its place.
Delete the entire TempMacro macro (using the Procedure list, if necessary, to find the macro). To delete the macro, simply select all the statements from Sub TempMacro to End Sub, and then press the Delete key.

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

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. While testing, you can have the macro not save the workbook. Once you’re ready to really use the macro, you can change the argument to True.
Save the Chapter02 workbook. Find a new source worksheet, and then run and test the AppendData macro.

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

Here’s the final version of the AppendData macro:

Sub AppendData()   Range("A1").Select   Selection.EntireRow.Delete   Selection.CurrentRegion.Select   Selection.Copy   Workbooks.Open Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"   Selection.End(xlDown).Select   ActiveCell.Offset(1, 0).Range("A1").Select   ActiveSheet.Paste   Application.CutCopyMode = False   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.