| "Sorry. If you do that, you'll void the product warranty." That's what I was told by a software vendor when I asked him about pointing a query at the overpriced application he'd sold one of my clients. No software publisher can possibly anticipate all the reasons that a customer might want his product. And that means that publishers can't possibly provide for all the ways that customers might want to view data. So, you'd think that a well-behaved publisher would offer an efficient means for users to get data out of one application and into another. Some do. The same company that warned me about voiding the warranty later came out with a new, improved product that makes it easy to point queries at its tables. But some don't, and then you need to work around the obstacles that the publishers put in your path. Fortunately, you can usually find a way. This chapter will help clarify for you that the search is worth the trouble. One way to get data from some other application into Excel is to display the data in that other application, copy it, switch to Excel, and paste in the data. Although that method works, there are plenty of reasons to find another way for example You're in a networked environment and not all users have access to an interface to the application that stores the data. In that case, not all users can display the data for a copy-and-paste operation. Again in a networked environment, you don't want to expose all the available data to every possible user. Maybe some of the data is confidential or sensitive. The way that the other application displays data is inconvenient. The application might display some fields that you don't care about. It might display subsets of records when you want to grab all of them. It might force you to display several different screens in order to get at all the data you're interested in. It's a pain.
Excel offers other ways to get data from other applications into a worksheet. Some involve Visual Basic for Applications (VBA) and some you can run from Excel's menus. Later chapters of this book, beginning in Part IV, "Managing Databases from Inside Excel," detail how to use VBA to get at external data. This chapter shows you how to get external data into a worksheet without writing a line of code. It also shows you how to update, or refresh, the external data to keep your worksheet current, both by setting options and by using VBA code. Many applications, particularly database management systems, store data in tables. Tables usually arrange data in the same form as Excel's lists: different records occupy different rows, and different fields occupy different columns. Figure 4.1 shows an example from Microsoft Access. Figure 4.1. By default, Access tables show Boolean (also termed Yes/No) fields as check boxes checked means TRUE. 
After you get this data into Excel, you can summarize it with pivot tables, graph it with charts, analyze it with statistical tools, and so on. If you set it up right, you have to arrange the data acquisition only once. If more data is added to the original table later, your workbook can pull in all the new or changed data automatically each time someone opens it, or even every few minutes. You begin the process by choosing Data, Get (or Import) External Data. Then Excel guides you through providing two basic sets of information: The first set of information, the data's location and format, can be used repeatedly. This is useful if you want to import different sets of data into different worksheets or workbooks for example, you might want to import data from January 2004 into one worksheet, from February 2004 into another, and so on. | 