| After you've returned data to an external data range in an Excel worksheet, the range has some additional properties that you might want to work with. NOTE Microsoft Office applications use the term properties to refer to aspects of objects. As just one of many, many examples, a range of cells has a Borders property that specifies which borders (top, bottom, left, right, diagonal, or none) that the range has. Until you become familiar with the notion of properties you may find it helpful to think of a property as an option.
Right-click any cell in an external data range, and choose Data Range Properties from the shortcut menu. The dialog box shown in Figure 5.22 appears. Figure 5.22. If you don't see Data Range Properties in the shortcut menu, you haven't clicked inside an external data range. 
From the standpoint of managing the external data, the most important properties shown in Figure 5.22 are Save Query Definition. Don't clear this box unless you're certain of what you're doing. If you do clear it, you won't be able to refresh the query's data or even edit the query. If you clear the check box and then save the workbook, you've lost the query for good. Save Password. This is important mainly in a networked environment. (If you're in a standalone environment, who are you password protecting your data from? Yourself?) You can save the password in the DSN file that defines the data set its path, its type, and so on but that file is stored in ASCII format, so anyone with sufficient curiosity can get the password from it. In contrast, the query definition that's saved in the workbook is in a hidden name, something that's much more difficult to break into. Enable Background Refresh. Filling this check box means that refreshes can take place without interrupting your normal work in Excel. You can be entering formulas or pivoting tables while Excel updates the external data range. Clearing the check box means that you can't proceed until the query has finished executing. You'll probably have use for this property only if the data source itself is frequently updated and is large enough that queries take a long time to execute. Refresh Data on File Open. When the workbook is opened, execute the query so that the most recent data is available from its source. The only cause for concern is the presence of an event handler that runs when the workbook is opened. Then you might need to know which occurs first (the Open event's handler executes first).  To find more information about refreshed data, see "Importing Data to Pivot Tables," p. 104.
For the next four properties listed in the External Data Range Properties dialog box, assume that the external data range occupies A1:B5 on the worksheet, as shown in Figure 5.23. Figure 5.23. The original external data range is shaded. 
In Figure 5.23, the boundaries of the original range are indicated by the row numbers in column C and the column letters in row 6. The data range is about to be refreshed. Each of the next six figures show the effect on the data shown in Figure 5.23 according to which property is selected, and whether the query returns more or fewer records than are shown in Figure 5.23. Insert Cells for New Data, Delete Unused Cells. Starting with row 6, the cells in columns A and B will be pushed down as two new records are inserted. Columns from C through IV are not affected. The cells containing "Column A" and "Column B" are pushed down to accommodate the inserted cells, but nothing happens to the cells in column C (see Figure 5.24). If the data query loses records, cells below row 5 are pulled up as the lost records' cells are deleted. Again, only columns A and B are affected. The cells containing "Column A" and "Column B" are pulled up as cells are deleted, and nothing has happened to the cells in column C (see Figure 5.25). Figure 5.24. The external data range has also been set to Preserve Cell Formatting. 
Figure 5.25. The query has lost its original four records and added two new records. 
Insert Entire Rows for New Data, Clear Unused Cells. Starting with row 5, all rows are pushed down by 2 and therefore cells C5:IV6 will be empty. Notice in Figure 5.26 that the cells in column C containing "Row 5," "Row 6," "Row 7," Column A," and "Column B" are pushed down by the insertion of two rows (although those rows are not inserted where you might expect). If the refresh returns only two records instead of the original four, cells A4:B5 are cleared. Figure 5.27 shows that the cells containing "Column A" and "Column B" remain in place because unused query cells are not deleted. Also notice that cells A4:B5 are cleared only of their contents, not their formats. Because neither cells nor rows are deleted, all other data outside the external data range remains where it was. Figure 5.26. Notice that the row insertion indicated in column C does not match the record insertion indicated in A6:B7. 
Figure 5.27. Unused cells in the external data range are cleared, not deleted, but the range is redefined to A1:B3. 
Overwrite Existing Cells with New Data, Clear Unused Cells. No cells or rows are inserted to accommodate new records. If you have data below the external data range (and that's usually a worksheet design flaw), it will be overwritten and you will not be warned. Figure 5.28 shows that the values "Column A" and "Column B" have been overwritten by the new records, but nothing has happened to the data in column C because nothing has been inserted. Cells vacated by the query because there are fewer records are cleared, so no cells or rows are deleted and other data remains in place. Figure 5.28. Beware of putting information below an external data range; it can be overwritten as shown here. 
Fill Down Formulas in Columns Adjacent to Table. This can be a handy way to avoid calculating fields in the query itself, using the database manager's formula syntax and built-in functions. Just create a formula on the worksheet in a column immediately to the left or the right of the external data range, and fill this check box. When new records arrive as you refresh the data, Excel automatically copies the existing adjacent formulas through the final row occupied by the external data range.
|
