Connect OLE DB data to your workbook
Databases are great for storing information, but sometimes you want to use or analyze its data in Excel. Some databases or other sources of data need an OLE DB connection for Excel to get the data you want. Running the Data Connection Wizard lets you make that connection. You get to the Data Connection Wizard through the Data tab.
Important: Connections to external data might be disabled on your computer. To connect to data when you open a workbook, enable data connections by using the Trust Center bar, or save the workbook in a trusted location.
-
On the Data tab, in the Get External Data group, click From Other Sources.
-
Click From Data Connection Wizard.
-
In the wizard, choose Other/Advanced and click Next.
-
Choose the type of OLE DB provider you want to connect to, and click Next.
-
Select the tables and queries you want by following the directions in the rest of the wizard, entering server names, user names, passwords, and any other information as needed.
-
You can click Finish, or click Next to change details for the connection.
-
In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.
-
Click the Properties button to set advanced properties for the connection, such as OLAP settings or options for refreshing the connected data.
-
Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.
-
Click OK to finish.
Keep the data in your workbook up to date
Now that you're connected to your OLE DB data, you'll always want the latest data in your workbook. Go to Data > Refresh All to get the latest data. For more information on refreshing, go to Refresh an OLE DB data connection.
Connect to other types of data sources
Need to connect to other sources of data, such as SQL Server, Microsoft Azure (cloud), Access, another Excel workbook, a text file, or one of many other types? See the following articles for more information.
Connect a SQL Server database to your workbook
Connect an Access database to your workbook
No comments:
Post a Comment