For simplicity and ease, you just can't beat the Drag and Drop method. Try this: Simultaneously open an empty Excel workbook and an Access database from which you want to import. In this case, you can use the ZalexCorp sample database you downloaded with this book. Now resize each application's window such that they are both fully visible on your screen.

Hover on the Access table or query you wish to transfer into Excel. Now press and hold the left mouse button and move the mouse cursor over to the blank worksheet in Excel as demonstrated in Figure 14-1. Release the mouse to see the data move to Excel.

Figure 14.1. Illustrating Drag and Drop

The Drag and Drop method comes in handy when you are doing a quick one-time analysis where you need a specific set of data in Excel. It is not so useful if:

Under the preceding scenarios, it is much better to use another technique.

You may remember from Chapter 2 that Access has a built-in Import wizard for importing data from various sources. Well, Access also has an Export wizard. It's relatively simple to use.

In Access, the last page in the Export wizard (Figure 14-3) asks if you want to save your export steps. Saving your export steps can be useful if you expect to frequently send that particular query or table to Excel.

The benefit of this method is that, unlike dragging and dropping, the ability to save export steps allows you to automate your exports by using macros.

Figure 14.3. Be sure to utilize the Save Export Steps option if you are going to Export your data frequently.

The limitation of this export method is that it is done within Access. If you are making an Excel report where data refresh must be under the Excel user's control, this method is not viable. In this circumstance, importing data from the Excel menu and/or using MS Query in Excel is the more viable option.

The option to pull data from Access has been available in Excel for many versions; it was just buried several layers deep in somewhat cryptic menu titles. This made getting Access data into Excel seem like a mysterious and tenuous proposition for many Excel analysts. With the introduction of the Ribbon in Excel 2007, Microsoft made importing Access data from Excel a little less nebulous, including the option right on the Ribbon under the Data tab.

Using the Get External Data method in Excel allows you to establishing a refreshable data connection between Excel and Access. To see the power of this method, walk through these steps:

Figure 14.6. Choosing how and where to view your Access data

Your reward for all the work will be a table similar to that shown in Figure 14-7, which contains the imported data from your Access database.

Figure 14.7. Your imported Access data!

The incredibly powerful thing about importing data this way is that it's refreshable! That's right. If you import data from Access using this technique, Excel creates a table that you can refresh by right-clicking and selecting Refresh, as demonstrated in Figure 14-8. When you Refresh your imported data, Excel reconnects to your Access database and imports the data again. As long as a connection to your database is available, you can refresh it with a mere click of the mouse.

Figure 14.8. As long as a connection to your database is available, you can refresh your table with the latest data.

Again, a major advantage to using the Get External Data method is that it allows you to establish a refreshable data connection between Excel and Access. This means, in most cases, you can set up the connection one time and then just refresh the data connection when needed. You can even record an Excel macro to refresh the data on some trigger or event. This is ideal for automating the transfer of data from Access.

The disadvantage to this method is that you have to take the data as it is in Access. That is to say, you give up the ability to utilize sorts, filters, and table joins to customize the data you bring into Excel.

Microsoft Query (affectionately known as MS Query) is a stand-alone program, installed with the Office suite, which can connect to external data sources from Excel. MS Query has one distinct advantage over the other methods for importing Access data into Excel: flexibility.

When transferring data using any of the previously mentioned methods, you can only import an existing table or query as is. That is to say, there is no opportunity to parse, filter or sort the data on the fly before importing it.

Not so with MS Query! With MS Query, you don't have to rely on the original tables or queries to be filtered or configured in a particular way. You can apply your own filters and sorts to your data pulls (through MS Query), essentially creating custom views that don't necessarily exist in the source database.

Starting MS Query

Begin by going to the Get External Data menu under the Data tab of the Excel ribbon. To start Microsoft Query, you choose the From Other Sources option and then select From Microsoft Query from the dropdown menu. Figure 14-9 shows what the menu will look like.

Figure 14.9. Start the Microsoft Query wizard.

After Microsoft Query opens, you will see the Choose Data Source dialog box illustrated in Figure 14-10. This is where you start building your MS Query import.

Figure 14.10. The Choose Data Source dialog box is your starting point.

Setting up Your Data Source

For this exercise, you will source data from the ZalexCorp database you downloaded with the sample files for this book. To set this database as an available data source, follow these steps:

1. From the Choose Data Source dialog, choose <New data source> from the Databases tab and click OK. This opens the Create New Data Source dialog box.

2. Type a name for your data source at the top of the dialog box, for example, ZalexCorp (see Figure 14-11).

   

   Figure 14.11. Name your new data source.

3. In the dropdown list box below, choose a type of driver for the database to which you want to connect. From this dropdown menu, make sure you select Microsoft Access Driver (*.mdb, *.accdb), as shown in Figure 14-12.

   

   Figure 14.12. Choose the Access driver designed for .mdb and .accdb files.

4. Click Connect. This opens the ODBC Microsoft Access Setup dialog box illustrated in Figure 14-13.

   

   Figure 14.13. The ODBC Microsoft Access Setup dialog box

5. Click the Select button in the Database section to browse for your database in the Select Database dialog box (see Figure 14-14). In this example, you will select the ZalexCorp database found in the C:OffTheGrid directory.

   

   Figure 14.14. Select your target database.

6. After you have selected your database, continue to click OK until you come back to the Choose Data Source dialog box. ZalexCorp now shows up in the list of databases (see Figure 14-15).

Now that your ZalexCorp data source is set up, MS Query remembers its location, allowing you to use it repeatedly without the need to point to it each time you need to use it.

Figure 14.15. ZalexCorp is now in the list of available data sources.

Tip

Microsoft Query works equally well for non-local databases (those that reside on a networked drive or even on the Web). In the Select Database dialog box, you'll find a dropdown menu with a list of computer drives available to you.

If your target database is on a network drive that is already mapped to your computer, choosing it is as simple as choosing that drive from the dropdown menu, and browsing the file hierarchy until you find the database you are looking for.

If your database is on a network that isn't mapped to your computer, simply click the Network button (shown in the lower right of the dialog box in Figure 14-14). This fires up a wizard to help you connect to a network drive or folder to which you are not currently mapped.

Building Your Custom Data Pull

Now that you have your ZalexCorp database set as an available data source, you can start building your own custom data pull. If you've closed the MS Query wizard, start it back up by going to the Data tab of the Excel Ribbon, choosing the From Other Sources option and then selecting From Microsoft Query.

1. Select your ZalexCorp datasource, as demonstrated in Figure 14-16, and click OK.

2. As you can see in Figure 14-17, you are presented with a dialog box that shows tables and queries within the ZalexCorp database. Select the Revenue by Period object and click the button with the right-pointing arrow.

3. In the next step, you can change the order of the data fields by clicking the up and down arrows to the right of the Columns in your query list box. Arrange the columns so that Region and Market come before Period as demonstrated in Figure 14-18. Click the Next button.

   

   Figure 14.16. Select your ZalexCorp data source.

   

   Figure 14.17. Select the Revenue by Period object.

   

   Figure 14.18. Move the Period column after Region and Market.

4. The next pane of the query wizard gives you the option of applying your own criteria to filter your data before importing (see Figure 14-19). Select the Period field to enable the filter options on the right. Once filtering is enabled, select "is greater than" from the condition dropdown. Then select 200812 in the criteria input box. Click the Next button.

   

   Figure 14.19. Set a filter telling MS Query to return only those records where the Period is greater than 200812.

5. In the next step, you're offered the opportunity to sort your query results. In this scenario, you want to sort by Period in ascending order, then by SumofSalesAmount in descending order. Figure 14-20 illustrates what this step looks like after the needed sorts have been applied. Click the Next button.

   

   Figure 14.20. You can apply your own sorting to your query results.

6. The last screen of the wizard asks you whether you want to return your data to Excel or further modify the query in Microsoft Query. Choose to view our data in Excel and click the Finish button.

7. At this point, you should see the Import dialog box shown in Figure 14-21. Here, elect to return the data to a Table in cell A1, and then click the OK button.

Figure 14.21. Return your results to a Table on your spreadsheet.

If all went well, you should have a table similar to that shown in Figure 14-22. Note that as designed, your query results contain only records where the Period is greater than 200812. Also, the columns have the correct order and sorting.

Figure 14.22. You've successfully created your first MS Query!

You can refresh the data by right-clicking anywhere inside your query table and selecting the Refresh button. You can also click the Refresh button found in the Design tab which activates when you cursor is inside the query table.

Again, while setting up an MS Query seems like a lot of work, the ability to parse, filter and sort data on the fly gives MS Query a distinct advantage over the previously mentioned methods for transferring data.

TRICKS OF THE TRADE: MANAGING EXTERNAL DATA PROPERTIES

Your query tables have a few adjustable properties exposed via the Properties dialog box. You can get to the properties of a particular External data table by clicking on the target table and selecting the Properties icon under the Data Tab (see Figure 14-23).

Figure 14.23. Getting to the properties of an external data table dialog box.

Activating the properties of a query table calls up the dialog box shown in Figure 14-24. Adjusting these properties allows you to further customize your query tables to suit your needs. Take a moment to familiarize yourself with some of the useful options on this dialog box.

Figure 14.24. The External Data Properties dialog box.

• Include row numbers: This property is unchecked by default. Checking this property will create a dummy column that contains row numbers. The first column of your dataset will be this row number column upon refresh.

• Adjust column width: This property is checked by default, telling Excel to adjust the column widths each time the data is refreshed. Removing this check will cause the column widths to remain the same.

• Preserve column/sort/filter/layout: If this is checked, the order of the columns and rows of the Excel range remains unchanged. This way, you can rearrange and sort the columns and rows of the external data in your spreadsheet without worrying about blowing away your formatting each time you refresh. Unchecking this property makes the Excel range look like the query.

• Preserve cell formatting: This is checked by default, telling Excel to keep the applied cell formatting when you refresh.

• Insert cells for new data, delete unused cells: This is the default setting for data range changes. When data rows decrease, you may have errors in adjacent cells that reference your external range. The cells these formulas referenced are deleted, so you will get a #VALUE error in your formula cells.

• Insert rows for new data, clear unused cells: When the unused cells are cleared instead of deleted, the formula may no longer return an error. Instead, it continues to reference cells from the original range—even though some of them are blank now. This could still give you erroneous results.

• Overwrite cells for new data, clear unused cells: The third option should be the same as option two when rows decrease as unused cells are cleared.