Monday, October 26, 2020

Refresh an external data connection in excel

You can connect your Excel workbook to an external data source, such as a SQL Server database, an OLAP cube, or even another Excel workbook. This connection can appear in your workbook as a table, PivotTable report, or PivotChart.

You can keep the data in your workbook up to date by "refreshing" the link to its source. Each time you refresh the connection, you see the most recent data, including anything that's new or has been deleted.

Security Note: Connections to external data may be currently disabled on your computer. To refresh the data when you open a workbook, you must enable the data connections by using the Trust Center bar, or you must place the workbook in a trusted location. For more information, see Add, remove, or modify a trusted location for your files, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.

Newer versions 2016 2010, 2013

For most connections, click any cell in the range or table that uses the connection, and then do one of the following:

  • Update only the selected data   Press ALT+F5, or on the Data tab, in the Queries & Connections group, click the arrow under Refresh All, and then click Refresh.

    Mouse pointing to the Refresh command on the ribbon

  • Update all data in the workbook   Press CTRL+ALT+F5, or on the Data tab, in the Queries & Connections group, click Refresh All.

    Mouse pointer over the Refresh All button

  • Control how or when the connection is refreshed   On the Data tab, click the arrow under Refresh All, and then click Connection Properties.

    Mouse pointing to the Connection properties command on the ribbon

    In the dialog box that opens, choose the options you want on the Usage tab, under Refresh Control.

Some refresh operations require more steps, as detailed in the following sections. If you see your data source listed, review the corresponding section.

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data to shrink the size of the file.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Queries & Connections group, click the arrow under Refresh All, and then click Connection Properties.

    Mouse pointing to the Connection properties command on the ribbon

  3. In the Connection Properties dialog box, on the Usage tab, under Refresh control, select the Refresh data when opening the file check box.

  4. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Queries & Connections group, click the arrow under Refresh All, and then click Connection Properties.

    Mouse pointing to the Connection properties command on the ribbon

  3. Click the Usage tab.

  4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query   A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh   To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query   To stop a query from running when background refresh is turned off, press the Esc key.

Refreshing an offline cube file, which recreates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you don't need immediate access to other files in Excel, and make sure you have adequate disk space to save the file again.

  1. Click the PivotTable connected to the offline cube file.

  2. On the Data tab, in the Queries & Connections group, click the arrow under Refresh All, and then click Refresh.

    Mouse pointer over the Refresh All button

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.

  2. If the Developer tab isn't available, do the following to display it:

    1. Click File, click Options, and then click Customize Ribbon.

    2. Under Main Tabs, select the Developer check box, and then click OK.

  3. On the Developer tab, in the XML group, click Refresh Data.

    XML Refresh Data

When you use the Power Pivot add-in to refresh previously imported data, you can see whether refresh succeeds, fails, or is cancelled.

In Power Pivot, click Home > Get External Data > Refresh or Refresh All to re-import the current table or all of the tables in the data model.

Status of the refresh is indicated for each connection used in the data model. There are three possible outcomes.

Data refresh status messages in Power Pivot

"Success" reports on the number of rows imported into each table.

"Error" can occur if the database is offline, you no longer have permissions, or a table or column is deleted or renamed in the source. Verify the database is available, perhaps by creating a new connection in a different workbook.

"Cancelled" means that Excel did not issue the refresh request, probably because refresh is disabled on the connection.

Tip:  Are you sharing this workbook with other people who might want to refresh data? Help your colleagues avoid data refresh errors by reminding them to request permissions on the data sources providing the data.

Table properties show queries used in data refresh

Data refresh is simply rerunning the same query that was used to get the data in the first place. You can view, and sometimes modify, the query by viewing table properties in the Power Pivot window.

  1. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window.

  2. Click Design > Table Properties.

  3. Switch to Query Editor to view the underlying query.

Queries are not visible for every type of data source. For example, queries are not shown for data feed imports.

Connection properties that cancel data refresh

In Excel, you can set connection properties that determine the frequency of data refresh. If refresh is not allowed on a particular connection, you'll get a cancellation notice when you run Refresh All or attempt to refresh a specific table that uses the connection.

  1. To view connection properties, in Excel, click Data > Queries & Connections to view a list of all connections used in the workbook.

  2. Right-click a connection and then click Properties.

  3. In Usage, under Refresh control, if the checkbox is cleared for Refresh this connection on Refresh All, you will get a cancellation when you try Refresh All in the Power Pivot window.

Refresh data on SharePoint Server 2013

On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

When the data you use for your map changes, you can refresh it manually in 3D Maps. The changes will then be reflected on your map. Here's how:

  • In 3D Maps, click Home > Refresh Data.

Refresh Data on the Home tab

Add data to Power Map

To add new data to your 3D MapsPower Map:

  1. In 3D Maps, go to the map you want to add data to.

  2. Leaving the 3D Maps window open, click in Excel and select the worksheet data you want to add.

  3. On the Excel ribbon, click Insert > Map arrow > Add Selected Data to Power Map.

Add Selected Data to Power Map command

Your 3D Maps will automatically update to show the additional data.

Data in a Microsoft Office Excel workbook that has been displayed in Excel Services can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as in a database or in an Online Analytical Processing (OLAP) cube.

If the data comes from an external data source, the workbook author or administrator has defined the connection information, often in an Office data connection (.odc) file, which describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source in Excel Services, you can perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Learn about refreshing data

The following illustration explains the basic process of what happens when you refresh data connected to an external data source.

The basic process of refreshing external data

  1. A refresh operation gets up-to-date data.

  2. A connection, often saved to a connection file, defines all the information needed to access and retrieve data from an external data source.

  3. There are a variety of data sources you can access, such as OLAP, SQL Server, OLEDB providers, and ODBC drivers.

  4. Up-to-date data is added to the current workbook.

Learn about security concerns

When you are connected to an external data source and you attempt to refresh the data, it's important to be aware of potential security issues, and to know what you can do about any security issues.

A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it's important to ensure that:

  • The connection file was authored by a reliable individual.

  • The connection file is secure and comes from a trusted Data Connection Library (DCL).

Security Note: Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner, and that you do not inadvertently reveal these credentials to others.

Control how data is refreshed

You can control how to refresh data from an external data source by doing one or more of the following:

  • Refresh on open   In Microsoft Office Excel 2007, a workbook author can create a workbook that automatically refreshes external data when the workbook is opened by selecting the Refresh data when opening the file check box in the Connection Properties dialog box. (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Usage tab, and then click Refresh data when opening the file.) In this case, Excel Services always refreshes the data before it displays a workbook and creates a new session. If the workbook author clears the Refresh data when opening the file check box, the data that is cached with the workbook is displayed, which means that when a user manually refreshes the data, the user sees refreshed, up-to-date data during the current session, but the data is not saved to the workbook. If you want to ensure that up-to-date data is always displayed when you display the workbook in Excel Services, select the Refresh data when opening the file check box by using Office Excel.

    If you are using an Office Data Connection file (.odc), make sure you also set the Always use connection file check box in the Connection Properties dialog box (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Definition tab, and then click Always use connection file).

    The trusted file location site settings, Short Session Timeout and External Data Cache Lifetime, can also have an impact on refresh operations. For more information, see your administrator or the Microsoft Office SharePoint Server Central Administration Help system.

  • Manual refresh   

    Note: You can manually refresh only a PivotTable report.

    1. Select a cell in a PivotTable report.

    2. On the Excel Web Access toolbar, under the Update menu, click Refresh Selected Connection.

      Notes: 

      • Any interactive operation that causes a requery of an OLAP data source initiates a manual refresh operation.

      • If this Refresh command is not visible, then the Web Part author has cleared the Refresh Selected Connection, Refresh All Connections property. For more information, see Excel Web Access Web Part custom properties.

  • Refresh all connections   On the Excel Web Access toolbar, under the Update menu, click Refresh All Connections.

  • Periodic refresh   By using Office Excel 2007, the workbook author can specify that data automatically refreshes at a specified interval after the workbook is opened for each connection in the workbook. For example, an inventory database may be updated every hour, and so the workbook author has defined the workbook to automatically refresh every 60 minutes.

    A Web Part author can select or clear the Allow Excel Web Access Periodic Data Refresh property to enable or disable this periodic refresh operation, if the property has been defined by the workbook author. When the time interval elapses, by default, the following refresh alert prompt is displayed at the bottom of the Excel Web Access Web Part:

    Refresh data for <List of Connections>? (Now) (Always) (Don't ask me again)

    An Excel Web Access Web Part author can also set the Display Periodic Data Refresh Prompt property to control the behavior of the message that is displayed when Excel Services performs a periodic data refresh during a session:

    • Always      means that the message is displayed with a prompt at each interval.

    • Optionally      means that a user can choose to continue periodic refresh without displaying a message.

    • Never      means that Excel Web Access performs a periodic refresh without displaying a message or prompt.

    For more information, see Excel Web Access Web Part custom properties.

  • Cancel a refresh   Because a refresh operation may take longer than you expect, you can cancel it. While the refresh operation occurs, Excel Services displays a message with a prompt. Click Cancel to interrupt the operation, so that you can complete the refresh operation at a more convenient time. Data returned by queries before you cancelled the refresh operation will display.

Stored passwords aren't encrypted, so they're not recommended. If your data source needs a password to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure doesn't apply to data retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. For example, Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

It's critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Queries & Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  3. Click the Definition tab, and then clear the Save password check box.

Note: Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you'll be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Queries & Connections group, click Refresh All, and then click Connection Properties.

    Mouse pointing to the Connection properties command on the ribbon

    Note: You can't run an OLAP query in the background.

    Note: You can't run a query for any connection type that retrieves data for the Data Model.

  3. Click the Usage tab.

  4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

    While you record a macro that includes a query, Excel doesn't run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor. Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True.

For most connections, click any cell in the range or table that uses the connection, and then do one of the following:

  • Update only the selected data   Press ALT+F5, or on the Data tab, in the Connections group, click the arrow under Refresh All, and then click Refresh.

    Mouse pointing to the Refresh command on the ribbon

  • Update all data in the workbook   Press CTRL+ALT+F5, or on the Data tab, in the Connections group, click Refresh All.

    Mouse pointer over the Refresh All button

  • Control how or when the connection is refreshed   On the Data tab, in the Connections group, click the arrow under Refresh All, and then click Connection Properties.

    Mouse pointing to the Connection properties command on the ribbon

    In the dialog box that opens, choose the options you want on the Usage tab, under Refresh Control.

Some refresh operations require more steps, as detailed in the following sections. If you see your data source listed, review the corresponding section.

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data to shrink the size of the file.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  1. Click the Usage tab.

  2. Under Refresh control, select the Refresh data when opening the file check box.

  3. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  3. Click the Usage tab.

  4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

Stored passwords aren't encrypted, so they're not recommended. If your data source needs a password to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure doesn't apply to data retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. For example, Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

It's critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  3. Click the Definition tab, and then clear the Save password check box.

Note: Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you'll be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

    Note: You can't run an OLAP query in the background.

    Note: You can't run a query for any connection type that retrieves data for the Data Model.

  3. Click the Usage tab.

  4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

    While you record a macro that includes a query, Excel doesn't run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor. Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True.

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

If you used Get & Transform to query a text file, no further steps are needed. If you imported the text file, the steps to refresh are as follows:

  1. Click any cell in the range or table that contains the link to the imported text file.

  2. On the Data tab, in the Connections group, click Refresh All.

    Mouse pointing to the Refresh command on the ribbon

    To update only the selected data, click Refresh.

    You can also right-click a cell in the range or table, and then click Refresh.

  3. In the Import Text File dialog box, browse to your text file, and then click Import.

Refreshing an offline cube file, which recreates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you don't need immediate access to other files in Excel, and make sure you have adequate disk space to save the file again.

  1. Click the PivotTable connected to the offline cube file.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Refresh.

    Mouse pointing to the Refresh command on the ribbon

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.

  2. If the Developer tab isn't available, do the following to display it:

    1. Click File, click Options, and then click Customize Ribbon.

    2. Under Main Tabs, select the Developer check box, and then click OK.

  3. On the Developer tab, in the XML group, click Refresh Data.

    XML Refresh Data

  • On the Data tab, in the Connections group, click Refresh All.

If you have multiple connections, and you only want to update a certain one, select any cell within that data range, click the arrow next to Refresh All, and click Refresh.

Mouse pointing to the Refresh command on the ribbon

Control how or when the connection is refreshed

  1. Click any cell that contains your connected data.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh All, and click Connection Properties.

    Connection Properties

  3. On the Usage tab, set any options you want to change.

    For very large data sets, consider checking the Enable background refresh option. This returns control of Excel to you as soon as the refresh begins, instead of making you wait for the refresh to finish.

Cancel or check the status of a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

When you use the Power Pivot add-in to refresh previously imported data, you can see whether refresh succeeds, fails, or is cancelled.

In Power Pivot, click Home > Get External Data > Refresh or Refresh All to re-import the current table or all of the tables in the data model.

Status of the refresh is indicated for each connection used in the data model. There are three possible outcomes.

Data refresh status messages in Power Pivot

"Success" reports on the number of rows imported into each table.

"Error" can occur if the database is offline, you no longer have permissions, or a table or column is deleted or renamed in the source. Verify the database is available, perhaps by creating a new connection in a different workbook.

"Cancelled" means that Excel did not issue the refresh request, probably because refresh is disabled on the connection.

Tip:  Are you sharing this workbook with other people who might want to refresh data? Help your colleagues avoid data refresh errors by reminding them to request permissions on the data sources providing the data.

Table properties show queries used in data refresh

Data refresh is simply rerunning the same query that was used to get the data in the first place. You can view, and sometimes modify, the query by viewing table properties in the Power Pivot window.

  1. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window.

  2. Click Design > Table Properties.

  3. Switch to Query Editor to view the underlying query.

Queries are not visible for every type of data source. For example, queries are not shown for data feed imports.

Connection properties that cancel data refresh

In Excel, you can set connection properties that determine the frequency of data refresh. If refresh is not allowed on a particular connection, you'll get a cancellation notice when you run Refresh All or attempt to refresh a specific table that uses the connection.

  1. To view connection properties, in Excel, click Data > Connections to view a list of all connections used in the workbook.

  2. Select a connection and click Properties.

  3. In Usage, under Refresh control, if the checkbox is cleared for Refresh this connection on Refresh All, you will get a cancellation when you try Refresh All in the Power Pivot window.

Refresh data on SharePoint Server 2013

On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

Note: The Power Query Editor is part of Get & Transform. To learn more, see Get & Transform in Excel 2016.

With the Power Query Editor, you can refresh a query to import the latest data into a table without having to recreate the query.

To refresh a query:

  1. Click Get & Transform > Show Queries.

  2. In the Workbook Queries pane, select the query you want to refresh.

  3. In the Power Query Editor ribbon, click Home > Query > Refresh preview.

Query Editor ribbon

When the data you use for your map changes, you can refresh it manually in 3D Maps. The changes will then be reflected on your map. Here's how:

  • In 3D Maps, click Home > Refresh Data.

Refresh Data on the Home tab

Add data to Power Map

To add new data to your 3D MapsPower Map:

  1. In 3D Maps, go to the map you want to add data to.

  2. Leaving the 3D Maps window open, click in Excel and select the worksheet data you want to add.

  3. On the Excel ribbon, click Insert > Map arrow > Add Selected Data to Power Map.

Add Selected Data to Power Map command

Your 3D Maps will automatically update to show the additional data.

Data in a Microsoft Office Excel workbook that has been displayed in Excel Services can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as in a database or in an Online Analytical Processing (OLAP) cube.

If the data comes from an external data source, the workbook author or administrator has defined the connection information, often in an Office data connection (.odc) file, which describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source, you can perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Learn about refreshing data

The following illustration explains the basic process of what happens when you refresh data connected to an external data source.

The basic process of refreshing external data

  1. A refresh operation gets up-to-date data.

  2. A connection, often saved to a connection file, defines all the information needed to access and retrieve data from an external data source.

  3. There are a variety of data sources you can access, such as OLAP, SQL Server, OLEDB providers, and ODBC drivers.

  4. Up-to-date data is added to the current workbook.

Learn about security concerns

When you are connected to an external data source and you attempt to refresh the data, it's important to be aware of potential security issues, and to know what you can do about any security issues.

A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it's important to ensure that:

  • The connection file was authored by a reliable individual.

  • The connection file is secure and comes from a trusted Data Connection Library (DCL).

For more information on how an administrator can author connection files, create trusted locations, and secure connection files, see Microsoft Office Excel 2007 Help and Office SharePoint Server Central Administration Help.

Security Note: Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner, and that you do not inadvertently reveal these credentials to others.

Control how data is refreshed

You can control how to refresh data from an external data source by doing one or more of the following:

  • Refresh on open   In Microsoft Office Excel 2007, a workbook author can create a workbook that automatically refreshes external data when the workbook is opened by selecting the Refresh data when opening the file check box in the Connection Properties dialog box. (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Usage tab, and then click Refresh data when opening the file.) In this case, Excel Services always refreshes the data before it displays a workbook and creates a new session. If the workbook author clears the Refresh data when opening the file check box, the data that is cached with the workbook is displayed, which means that when a user manually refreshes the data, the user sees refreshed, up-to-date data during the current session, but the data is not saved to the workbook. If you want to ensure that up-to-date data is always displayed when you display the workbook in Excel Services, select the Refresh data when opening the file check box by using Office Excel.

    If you are using an Office Data Connection file (.odc), make sure you also set the Always use connection file check box in the Connection Properties dialog box (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Definition tab, and then click Always use connection file).

    The trusted file location site settings, Short Session Timeout and External Data Cache Lifetime, can also have an impact on refresh operations. For more information, see your administrator or the Microsoft Office SharePoint Server Central Administration Help system.

  • Manual refresh   

    Note: You can manually refresh only a PivotTable report.

    1. Select a cell in a PivotTable report.

    2. On the Excel Web Access toolbar, under the Update menu, click Refresh Selected Connection.

      Notes: 

      • Any interactive operation that causes a requery of an OLAP data source initiates a manual refresh operation.

      • If this Refresh command is not visible, then the Web Part author has cleared the Refresh Selected Connection, Refresh All Connections property. For more information, see Excel Web Access Web Part custom properties.

  • Refresh all connections   On the Excel Web Access toolbar, under the Update menu, click Refresh All Connections.

  • Periodic refresh   By using Office Excel 2007, the workbook author can specify that data automatically refreshes at a specified interval after the workbook is opened for each connection in the workbook. For example, an inventory database may be updated every hour, and so the workbook author has defined the workbook to automatically refresh every 60 minutes.

    A Web Part author can select or clear the Allow Excel Web Access Periodic Data Refresh property to enable or disable this periodic refresh operation, if the property has been defined by the workbook author. When the time interval elapses, by default, the following refresh alert prompt is displayed at the bottom of the Excel Web Access Web Part:

    Refresh data for <List of Connections>? (Now) (Always) (Don't ask me again)

    An Excel Web Access Web Part author can also set the Display Periodic Data Refresh Prompt property to control the behavior of the message that is displayed when Excel Services performs a periodic data refresh during a session:

    • Always      means that the message is displayed with a prompt at each interval.

    • Optionally      means that a user can choose to continue periodic refresh without displaying a message.

    • Never      means that Excel Web Access performs a periodic refresh without displaying a message or prompt.

    For more information, see Excel Web Access Web Part custom properties.

  • Cancel a refresh   Because a refresh operation may take longer than you expect, you can cancel it. While the refresh operation occurs, Excel Services displays a message with a prompt. Click Cancel to interrupt the operation, so that you can complete the refresh operation at a more convenient time. Data returned by queries before you cancelled the refresh operation will display.

  1. Click any cell in the range or table that contains the link to the external data.

  2. On the Data tab, in the Connections group, click Refresh All.

    Refresh Data Connection

    To update only the selected data, click Refresh.

    You can also right-click a cell in the range or table, and then click Refresh.

    Note: If you have more than one workbook open, you'll need to repeat the operation in each workbook.

  1. Click any cell in the range or table that contains the link to the imported text file.

  2. On the Data tab, in the Connections group, click Refresh All.

    Refresh Data Connection

    To update only the selected data, click Refresh.

    You can also right-click a cell in the range or table, and then click Refresh.

  3. In the Import Text File dialog box, browse to your text file, and then click Import.

Refreshing an offline cube file, which recreates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you don't need immediate access to other files in Excel, and make sure you have adequate disk space to save the file again.

  1. Click the PivotTable connected to the offline cube file.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Refresh.

    Refresh Data Connection

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.

  2. If the Developer tab isn't available, do the following to display it:

    1. Click File, click Options, and then click Customize Ribbon.

    2. Under Main Tabs, select the Developer check box, and then click OK.

  3. On the Developer tab, in the XML group, click Refresh Data.

    XML Refresh Data

  • On the Data tab, click Refresh All.

If you have multiple connections, and you only want to update a certain one, select any cell within that data range, click the arrow next to Refresh All, and click Refresh.

Refresh Data Connection

Control how or when the connection is refreshed

  1. Click any cell that contains your connected data.

  2. On the Data tab, click the arrow next to Refresh All, and click Connection Properties.

    Connection Properties

  3. On the Usage tab, set any options you want to change.

    For very large data sets, consider checking the Enable background refresh option. This returns control of Excel to you, instead of making you wait for the refresh to finish.

Cancel or check the status of a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

When you use the Power Pivot add-in to refresh previously imported data, you can see whether refresh succeeds, fails, or is cancelled.

In Power Pivot, click Home > Get External Data > Refresh or Refresh All to re-import the current table or all of the tables in the data model.

Status of the refresh is indicated for each connection used in the data model. There are three possible outcomes.

Data refresh status messages in Power Pivot

"Success" reports on the number of rows imported into each table.

"Error" can occur if the database is offline, you no longer have permissions, or a table or column is deleted or renamed in the source. Verify the database is available, perhaps by creating a new connection in a different workbook.

"Cancelled" means that Excel did not issue the refresh request, probably because refresh is disabled on the connection.

Tip:  Are you sharing this workbook with other people who might want to refresh data? Help your colleagues avoid data refresh errors by reminding them to request permissions on the data sources providing the data.

Table properties show queries used in data refresh

Data refresh is simply rerunning the same query that was used to get the data in the first place. You can view, and sometimes modify, the query by viewing table properties in the Power Pivot window.

  1. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window.

  2. Click Design > Table Properties.

  3. Switch to Query Editor to view the underlying query.

Queries are not visible for every type of data source. For example, queries are not shown for data feed imports.

Connection properties that cancel data refresh

In Excel, you can set connection properties that determine the frequency of data refresh. If refresh is not allowed on a particular connection, you'll get a cancellation notice when you run Refresh All or attempt to refresh a specific table that uses the connection.

  1. To view connection properties, in Excel, click Data > Connections to view a list of all connections used in the workbook.

  2. Select a connection and click Properties.

  3. In Usage, under Refresh control, if the checkbox is cleared for Refresh this connection on Refresh All, you will get a cancellation when you try Refresh All in the Power Pivot window.

Refresh data on SharePoint Server 2013

On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

With Power Query, you can refresh a query to import the latest data into a table without having to recreate the query.

Important: 

  • Excel 2010   Power Query is not included in Excel 2010, but you can download, install, and activate the Power Query Add-In.

  • To activate the Power Query add-in, click File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel.

To refresh a query:

  1. In the Query Editor ribbon, click Refresh preview.

Query Editor ribbon

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

If your Excel workbook has a connection to a table or query in an Access database, you can make sure it shows the most up to date information. Use the Refresh command (on the Data tab) to do this.

  1. On your worksheet, click any cell that contains your Access data.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.

Refresh Data Connection

To control how or how often the connection is refreshed:

  1. Click any cell that contains your Access data.

  2. In the Connections group, click the arrow next to Refresh All, and click Connection Properties.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

Connection Properties

  1. On the Usage tab, set any options you want to change.

Check the status of or cancel a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one or more of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

If your Excel workbook has a connection to a table or query in a SQL Server database, you can make sure it shows the most up to date information. Use the Refresh command (on the Data tab) to do this.

  1. On your worksheet, click any cell that contains your SQL Server data.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.

Refresh Data Connection

To control how or how often the connection is refreshed:

  1. Click any cell that contains your SQL Server data.

  2. In the Connections group, click the arrow next to Refresh All, and click Connection Properties.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

Connection Properties

  1. On the Usage tab, set any options you want to change, such as enabling a background refresh.

Cancel or check the status of a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

If your Excel workbook has a connection to a table or query in an OLE DB data source, you can make sure it shows the most up to date information. Use the Refresh command (on the Data tab) to do this.

  1. On your worksheet, click any cell that contains your OLE DB data.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.

Refresh Data Connection

To control how or how often the connection is refreshed:

  1. Click any cell that contains your OLE DB data.

  2. In the Connections group, click the arrow next to Refresh All, and click Connection Properties.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

Connection Properties

  1. On the Usage tab, set any options you want to change.

Check or cancel a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on or cancel a refresh, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

When the data you use for your map changes, you can refresh it manually in 3D Maps. The changes will then be reflected on your map. Here's how:

  • In 3D Maps, click Home > Refresh Data.

Refresh Data on the Home tab

Add data to Power Map

To add new data to your 3D MapsPower Map:

  1. In 3D Maps, go to the map you want to add data to.

  2. Leaving the 3D Maps window open, click in Excel and select the worksheet data you want to add.

  3. On the Excel ribbon, click Insert > Map arrow > Add Selected Data to Power Map.

Add Selected Data to Power Map command

Your 3D Maps will automatically update to show the additional data.

Data in a Microsoft Office Excel workbook that has been displayed in Excel Services can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as in a database or in an Online Analytical Processing (OLAP) cube.

If the data comes from an external data source, the workbook author or administrator has defined the connection information, often in an Office data connection (.odc) file, which describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source, you can perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Learn about refreshing data

The following illustration explains the basic process of what happens when you refresh data connected to an external data source.

The basic process of refreshing external data

  1. A refresh operation gets up-to-date data.

  2. A connection, often saved to a connection file, defines all the information needed to access and retrieve data from an external data source.

  3. There are a variety of data sources you can access, such as OLAP, SQL Server, OLEDB providers, and ODBC drivers.

  4. Up-to-date data is added to the current workbook.

Learn about security concerns

When you are connected to an external data source and you attempt to refresh the data, it's important to be aware of potential security issues, and to know what you can do about any security issues.

A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it's important to ensure that:

  • The connection file was authored by a reliable individual.

  • The connection file is secure and comes from a trusted Data Connection Library (DCL).

For more information on how an administrator can author connection files, create trusted locations, and secure connection files, see Microsoft Office Excel 2007 Help and Office SharePoint Server Central Administration Help.

Security Note: Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner, and that you do not inadvertently reveal these credentials to others.

Control how data is refreshed

You can control how to refresh data from an external data source by doing one or more of the following:

  • Refresh on open   In Microsoft Office Excel 2007, a workbook author can create a workbook that automatically refreshes external data when the workbook is opened by selecting the Refresh data when opening the file check box in the Connection Properties dialog box. (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Usage tab, and then click Refresh data when opening the file.) In this case, Excel Services always refreshes the data before it displays a workbook and creates a new session. If the workbook author clears the Refresh data when opening the file check box, the data that is cached with the workbook is displayed, which means that when a user manually refreshes the data, the user sees refreshed, up-to-date data during the current session, but the data is not saved to the workbook. If you want to ensure that up-to-date data is always displayed when you display the workbook in Excel Services, select the Refresh data when opening the file check box by using Office Excel.

    If you are using an Office Data Connection file (.odc), make sure you also set the Always use connection file check box in the Connection Properties dialog box (On the Data tab, in the Connections group, click Connections, select the connection, click Properties, click the Definition tab, and then click Always use connection file).

    The trusted file location site settings, Short Session Timeout and External Data Cache Lifetime, can also have an impact on refresh operations. For more information, see your administrator or the Microsoft Office SharePoint Server Central Administration Help system.

  • Manual refresh   

    Note: You can manually refresh only a PivotTable report.

    1. Select a cell in a PivotTable report.

    2. On the Excel Web Access toolbar, under the Update menu, click Refresh Selected Connection.

      Notes: 

      • Any interactive operation that causes a requery of an OLAP data source initiates a manual refresh operation.

      • If this Refresh command is not visible, then the Web Part author has cleared the Refresh Selected Connection, Refresh All Connections property. For more information, see Excel Web Access Web Part custom properties.

  • Refresh all connections   On the Excel Web Access toolbar, under the Update menu, click Refresh All Connections.

  • Periodic refresh   By using Office Excel 2007, the workbook author can specify that data automatically refreshes at a specified interval after the workbook is opened for each connection in the workbook. For example, an inventory database may be updated every hour, and so the workbook author has defined the workbook to automatically refresh every 60 minutes.

    A Web Part author can select or clear the Allow Excel Web Access Periodic Data Refresh property to enable or disable this periodic refresh operation, if the property has been defined by the workbook author. When the time interval elapses, by default, the following refresh alert prompt is displayed at the bottom of the Excel Web Access Web Part:

    Refresh data for <List of Connections>? (Now) (Always) (Don't ask me again)

    An Excel Web Access Web Part author can also set the Display Periodic Data Refresh Prompt property to control the behavior of the message that is displayed when Excel Services performs a periodic data refresh during a session:

    • Always      means that the message is displayed with a prompt at each interval.

    • Optionally      means that a user can choose to continue periodic refresh without displaying a message.

    • Never      means that Excel Web Access performs a periodic refresh without displaying a message or prompt.

    For more information, see Excel Web Access Web Part custom properties.

  • Cancel a refresh   Because a refresh operation may take longer than you expect, you can cancel it. While the refresh operation occurs, Excel Services displays a message with a prompt. Click Cancel to interrupt the operation, so that you can complete the refresh operation at a more convenient time. Data returned by queries before you cancelled the refresh operation will display.

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data to shrink the size of the file.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  1. Click the Usage tab.

  2. Under Refresh control, select the Refresh data when opening the file check box.

  3. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  3. Click the Usage tab.

  4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

Stored passwords aren't encrypted, so they're not recommended. If your data source needs a password to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure doesn't apply to data retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. For example, Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

It's critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

  3. Click the Definition tab, and then clear the Save password check box.

Note: Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you'll be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties.

    Connection Properties

    Note: You can't run an OLAP query in the background.

    Note: You can't run a query for any connection type that retrieves data for the Data Model.

  3. Click the Usage tab.

  4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

    While you record a macro that includes a query, Excel doesn't run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor. Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True.

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

  • Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

  • Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

Related content

at
Labels: ,

1 comment:

  1. AnonymousApril 17, 2022 at 6:08 PM

    Microsoft Office Tutorials: Refresh An External Data Connection In Excel >>>>> Download Now

    >>>>> Download Full

    Microsoft Office Tutorials: Refresh An External Data Connection In Excel >>>>> Download LINK

    >>>>> Download Now

    Microsoft Office Tutorials: Refresh An External Data Connection In Excel >>>>> Download Full

    >>>>> Download LINK rK

    ReplyDelete

Subscribe to: Post Comments (Atom)