By importing data, you don't have to retype the data you want to analyze in Microsoft Excel. You can also update your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.
Importing data from databases and files
You can import data to Excel from most data sources by pointing to Import External Data on the Data menu, clicking Import Data, and then choosing the data you want to import in the Select Data Source dialog box.
The Data Connection Wizard, available when you click New Source in the Select Data Source dialog box, makes it possible to import data from external data connections not available from the Select Data Source dialog box. These sources may include OLE DB data sources (including OLAP cubes and exchange servers) and any data sources a system administrator supplies. You cannot filter or join data in the Data Connection Wizard.
The default connection method when you import data using the Data Connection Wizard is through OLE DB providers. The resulting .odc (office data connection) files can be opened for viewing in Internet Explorer and edited in Excel, Notepad, and other Microsoft Office applications if the file doesn't point to an OLAP data source.
The Data Connection Wizard also provides access to a data source called a data retrieval service. A data retrieval service is a Web service installed on Windows SharePoint Services for connecting to and retrieving data. To use a data retrieval service, a client application, such as Excel, sends a query request over HTTP (HTTP: Internet protocol that delivers information on the World Wide Web. Makes it possible for a user with a client program to enter a URL (or click a hyperlink) and retrieve text, graphics, sound, and other digital information from a Web server.) to the data retrieval service on Windows SharePoint Services. The data retrieval service sends that request to the data source, and then passes the data that is returned to it back to the client application as XML. Importing data from a data retrieval service in Excel automatically creates a databound XML list in your worksheet. After adding a databound XML list to your worksheet, you can use the commands on the XML submenu of the Data menu or the List tool bar to refresh data, edit the query, or set the properties of the XML map associated with the XML list.
A default installation of Windows SharePoint Services provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available on the Downloads on Microsoft Office Online.
Note To bring external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) into Microsoft Excel, you must have access to the data. If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.
Importing data with Microsoft Query
In most cases, you can import data by using the Import Data command as described in the section above. Use Query or another program only if you need to perform specialized query tasks such as the following:
Microsoft Query provides a simple front end, easily accessible from within Excel, to perform these specialized query tasks.
You can use Query to set up ODBC data sources to retrieve data. In Query, you can use the Query Wizard to create a simple query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.), or you can use advanced criteria in Query to create a more complex query. You can access Query from Excel, or you can create a query from within the PivotTable and PivotChart Wizard.
You can also use Dynamic Data Exchange (DDE) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) with Query. For more information about DDE, see Query Help.
To import data using Query, you must first:
- Install Query Query, including the Query Wizard, is an optional feature for Excel. Under most circumstances, you are prompted to install Query when you point to Import External Data on the Data menu and click New Database Query.
- Install ODBC drivers An ODBC driver (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) is required to retrieve data in relational databases, text files, or Excel using Query. When you install Query, you automatically install a set of ODBC drivers. If you use a driver other than one installed with Query, you must install the driver separately.
- Install data source drivers A data source driver (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.) is required to retrieve OLAP source data. Query supports connecting to databases that are created by using SQL Server OLAP Services; when you installed Query, you automatically installed support for this type of OLAP database. To connect to other OLAP databases, you must install a data source driver and client software.
For more information, see Query Help.
Importing data from the Web
Importing data with Visual Basic for Applications (VBA)
You can use a Visual Basic for Applications (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) to gain access to an external data source.
Depending on the data source, you will use either ActiveX Data Objects (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.) or Data Access Objects (Data Access Objects (DAO): A data access interface that communicates with Microsoft Jet and ODBC-compliant data sources to connect to, retrieve, manipulate, and update data and the database structure.) to retrieve data using VBA.
If you want to use a macro that you created in Excel version 5.0 or earlier, click Add-Ins on the Tools menu, and then make sure the ODBC Add-In check box is selected.
For information about creating Visual Basic for Applications macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).
Note While you are recording a macro that includes a query, Excel can't run the query in the background, even if you chose to run it that way. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True".
Refreshing data and naming ranges
Excel provides many options for refreshing imported data, including refreshing the data whenever you open the workbook and automatically refreshing data at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status of the refresh while it's being refreshed.
If your external data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) requires a password (password: A word, phrase, or string of characters used to prevent unauthorized access to a database. To gain access to the database, you must enter the password correctly.) to gain access to the data, you can require that the password is entered each time the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) is refreshed.
When an external data range expands and additional records are returned, Excel can fill formulas in adjacent columns or within the data range so that they remain next to the appropriate data.
You can also choose how to add new data to your worksheet.
Naming external data ranges
Excel automatically names an external data range as follows:
If your worksheet has more than one external data range from the same source, the ranges are numbered. For example, MyText, MyText_1, MyText_2, and so on.
You can also change the name of an external data range in the Data Range Properties dialog box.