2.1. Using External Data

External Data refers to any data that does not reside in Excel. Using the Import Data function on the Data menu, you can import entire tables or queries from Access and other databases. You can access this function by going to Data Import External Data Import Data. In addition to importing data from a database, you can also use this feature to import text files, XML files, web pages, etc. This is a very simple way to bring in all of the data from a table or query. The nice part about this feature is that you can refresh the data at any time by pressing the refresh button on the External Data toolbar (Figure 2-1). Also, as with any external data range, you can set it up so that any formulas done at a row level will be copied as the data range expands (described later in the chapter).

There are several other features available when working with an External Data range. These are available on the External Data Range Properties , which can be accessed either by right-clicking in the data range and selecting Data Range Properties, or from the External Data Toolbar. Refer to Figure 2-2 to see these properties.

Figure 2-1. The External Data toolbar

Figure 2-2. The External Data Range Properties dialog box

While there are many options in the properties dialog, two key ones are "Refresh control " and "Fill down formulas in columns adjacent to data." Under the "Refresh control" section, there is a checkbox for "Refresh Data on File Open." This ensures that anytime the Excel workbook is opened, it will use the most recent data. If you are using a data source that is updated daily, this is probably enough; however, if you are using a data source that is constantly being updated, you can also select "Refresh every" for the amount of time that you want to elapse between each data refresh.

Under the "Refresh data on file open" checkbox, there is another checkbox that allows you to not save the data with the spreadsheet. This is useful if you are accessing data that is password protected and you want to ensure that no one can simply access the data in a saved Excel spreadsheet. For example, if Human Resources uses an Excel workbook to track salary or performance appraisal scores, checking this box ensures that the data does not get into the wrong hands. When you check this box, the Excel workbook opens with the data range cleared, connects to the data source, and pulls the most recent data. This prevents people who do not have access to the database or more precisely, people who do not have access to that table or query in the database from refreshing the data. If you do not have this checked, the data from the last refresh is visible when the Excel workbook is opened before the refresh.

The second option on the External Data Range Properties dialog box that you will find very useful is the option to fill down formulas. You will often perform calculations on the data at a row level. For example, you might pull some data from a sales table and want a formula that tells you whether the margin on each sale is acceptable. If you check the box to copy the formulas down, as the table (or query) grows, it will copy the appropriate formulas. It is important to note that the formulas must be adjacent to the data; formulas on another sheet or in non-adjacent rows need to be copied down using another method. The other thing to keep in mind is that the formulas must be to the right of the data. This point is not clear in the Excel documentation, but upon testing, you will find that formulas to the left of the data will need to be copied down.

Another advantage to using the Import External Data function is that the resulting External Data Range is defined as a named range. This allows you to use the name of your External Data Range in place of the cell reference for formulas like VLOOKUP. In addition, Excel also gives you the option of using the Column Label in your functions. For example, if you name a column Amount, and you want to create a summary on the same worksheet that sums that column, you can write the formula as =Sum(Amount).

However, by default, Excel does not allow you to use the names of your columns this way. To fix this, go to the Options dialog under the Tools menu and turn on the Manual option on the Calculation tab (Figure 2-3). To apply this feature to data that will refresh, type in the field names manually and bring in the data without them. In the External Range Properties Dialog (Figure 2-2), uncheck the "Include field names" checkbox in the "Data formatting and layout" section. By not linking the field names in Excel to the data, you can continue to use the column names in your formulas.

Figure 2-3. The "Accept labels in formulas" checkbox, which enables the use of column labels from Excel Lists and External Data Ranges in formulas

When you turn on this function, you also access another very powerful feature. If you are used to writing VLOOKUP statements, this will change the way that you write many of those in which the formulas reside on the same sheet as the data range. Let's assume that you have a list of Social Security numbers, employee names, and salaries, and assume that the salaries are in a column named Salary. If this was your External Data Range named EmployeeInfo, you could write a VLOOKUP function using the Social Security number (SSN) as the value, EmployeeInfo as the Table Array, column 3 (Salary) as the column index value, and False in the Range Lookup box to ensure that you only get a result for exact matches. Using this function would yield the salary of the person with the specified Social Security number. If Excel did not automatically create this named range for you, you would need to constantly update your VLOOKUP formula as the range expanded or contracted.

This works great when the first column in your data range is the range that you are looking up. When this is not the case, you either need to change your query so that the column you want to use is first, or you need to use the following function. Turn on the "Accept labels in formulas" option on the Calculation tab. Using the same example, if you wanted to look up an employee's salary but only knew his name (or did not want to use his Social Security number), you can write this formula: =Joe Smith Salary. This goes to the row where Excel finds Joe Smith and pulls his salary. You can also put Joe Smith in single quotes to be certain that Excel knows which row you want to pull. In this case, Excel is using the intersection of the row and column that you chose. I want to caution you again that you can only use labels in formulas on the same sheet. To do this on another sheet, use the named range with VLOOKUP and put the Employee Name in the first column.

The primary reason to use named ranges and column labels in your functions and lists is that it enables you to refer to the data without having to change the cell references when the data set gets larger or smaller. Some people get around this by making their formula ranges large enough to not have to update them. While this is possible, it can create performance issues, and it also means that you cannot write formulas directly below your data. Also note that when you create a named range, you cannot put spaces in the name. If you try to, Excel gives you an error message that the name is not valid. The default named range for an external data query places underscores for the spaces. To break up the names to make them more readable, use underscores or capital letters at the beginning of each word, as shown previously in the EmployeeInfo named range.

In addition to Excel creating a Named Range for your External Data Range, the result set is also a QueryTable object that can be referred to by VBA, whether it is a simple import of external data or a database query. Within VBA, you can perform many tasks on the QueryTable object, including changing the table or query, setting a refresh timer, and refreshing the data. In addition, you also gain access to the properties exposed by the QueryTable object, including connection string, source file name, command text, etc. Another way to use the QueryTable object in VBA will be discussed later in this chapter, and you will use QueryTables at other times throughout the book.

When you right-click anywhere in a data range, a menu of options appears. You can use the Edit Query option to change the table that you are accessing. You can also change the SQL there if you are not using a table.

While the Import Data function is relatively simple to execute, it is very useful when you need to use entire data sets that are already defined in the database or file.