Edit

"Refresh All" doesn't work after exporting app data to a dynamic worksheet

  • Article

Symptoms

You use the Export to Excel command to export app data to a dynamic worksheet. Then, you open the downloaded file and refresh the data by selecting Data > Refresh All. In this situation, you find that the data disappears and the workbook appears blank. You might receive the following error message:

This Web query returned no data. To change the query, click OK, click the arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query.

Screenshot that shows the Web query returned no data error that occurs after you refresh the exported app data.

Cause

This issue occurs when the data that you access is password-protected and the Excel file can't submit passwords to external data sources.

Resolution

To resolve this issue, you must edit and save the web query.

  1. In the Excel file, select Data > Queries & Connections.

    Screenshot that shows the Queries and Connections option in the Data tab.

  2. The Queries & Connections pane opens on the right of the window. On the Connections tab, right-click the query and then select Properties.

    Screenshot that shows the Properties settings of a query on the Queries and Connections pane.

  3. The Connection Properties window opens. On the Definition tab, select Edit Query.

    Screenshot that shows the Edit Query button in the Connection Properties window.

  4. If prompted, enter the username and password. Enter the same user and password that you use to sign in to your app.

  5. On the Edit Web Query window, select Go. An error message occurs:

    Can't complete this action

    Screenshot that shows the Go button in the Edit Web Query window.

  6. Close the Edit Web Query window.

  7. This should fix the issue. Refresh the data in the worksheet again by selecting Data > Refresh All.

    Screenshot that shows how to refresh your app data in Excel.

If the above steps don't resolve the issue, follow these additional steps:

  1. Enter the following link in the address bar of the Edit Web Query window to access the Advanced Settings page in Microsoft Dynamics 365 Customer Engagement. Remember to replace OrgURL with your organization URL.

    https://OrgURL/main.aspx?settingsonly=true

  2. Sign out using the top-right profile option link and then sign back in using the right identity.

  3. Once you're signed in, close the Edit Web Query window, and then in the Excel file, select Data > Refresh All. The data will be refreshed as expected.

See also

Export data to an Excel dynamic worksheet